none
SSIS and Microsoft OLE DB Provider for DB2 RRS feed

  • Question

  • I am trying to get the best performance driver for transfer of data from AS400 to SQL Server 2005 using SSIS.  I am comparing it to DTS packages using Client Access ODBC Driver (32-bit) which I'll call std ODBC

    .Net Provider/ODBC works but is VERY slow. I understand that Microsoft has simply put the .Net layer on top of the std ODBC driver and this overhead is slowing it right down (to a crawl in my opinion)

    .Net Oledb/ IBM DB2 for iSeries IBMDA400 works and is about 2.5 times faster than .Net/ODBC, but still about 3x slower than std ODBC

    .Net Oledb/ IBM DB2 for iSeries IBMDASQL is similar.

    Microsoft OLE DB Provider for DB2 is supposed to be the fastest but it requires upgrading to Enterprise Edition, which is not a trivial cost.

    Can anyone give me feedback on the performance I can expect with Microsoft OLE DB Provider for DB2 compared to std ODBC.

    Thanks
    Alister

    Wednesday, April 22, 2009 4:28 AM

Answers

  • Hi Alister,

    I think no one knows exactly the question how much. Different environment gives different answer. If possible, could you try to install an evaluation edition and test these two drivers?

    Thanks.


    Yao Jie Tang -Microsoft Online Community
    • Marked as answer by Tony Tang_YJ Thursday, April 30, 2009 10:55 AM
    Tuesday, April 28, 2009 11:28 AM

All replies

  • i can't confirm this, but any DB2 ole db provider should be faster than an odbc one.  this is because odbc is a layer of abstraction on top of ole db.

    hth
    Duane Douglas, MCAD | http://www.ssisbi.com | Please mark the post(s) that answered your question.

    Wednesday, April 22, 2009 5:13 AM
    Moderator
  • Thanks Duane...

    OLEDB is definately much faster than ODBC, but my question is how much faster is the Microsoft OLEDB driver than the IMB OLEDB driver.

    Cheers
    Alister
    Wednesday, April 22, 2009 10:52 PM
  • Thanks Duane...

    OLEDB is definately much faster than ODBC, but my question is how much faster is the Microsoft OLEDB driver than the IMB OLEDB driver.

    Cheers
    Alister
    i don't know the answer to that.  however, i can tell you that some visitors to this forum have reported "show-stopping" issues with the ms driver that went away when they implemented the ibm driver instead.  you'll get a better sense of what i mean if you search the forum.

    hth

    Duane Douglas, MCAD | http://www.ssisbi.com | Please mark the post(s) that answered your question.

    Thursday, April 23, 2009 5:17 AM
    Moderator
  • Hi Alister,

    I think no one knows exactly the question how much. Different environment gives different answer. If possible, could you try to install an evaluation edition and test these two drivers?

    Thanks.


    Yao Jie Tang -Microsoft Online Community
    • Marked as answer by Tony Tang_YJ Thursday, April 30, 2009 10:55 AM
    Tuesday, April 28, 2009 11:28 AM
  • Thanks for the suggestion Yao-Jie Tang but unfortunately I haven't the time to follow this course.  I have spent too much time on this already so have decided to stick with the DTS packages and not use SSIS.

    Cheers

    Alister

    Monday, May 11, 2009 8:42 AM
  • Thanks for the suggestion Yao-Jie Tang but unfortunately I haven't the time to follow this course.  I have spent too much time on this already so have decided to stick with the DTS packages and not use SSIS.

    Cheers

    Alister


    are you saying that you've decided not to use ssis because you're not sure how well the ms ole db provider for db2 will perform, or is there some other reason for your decision?  if there's some other reason, i'm curious to know what that is.  if you don't mind, please let us know.  thanks.
    Duane Douglas, MCAD | http://www.ssisbi.com | Please mark the post(s) that answered your question.

    SSIS Business Intelligence

    Wednesday, May 13, 2009 5:25 AM
    Moderator
  • Hi Duane...

    I've decided to stick with DTS because:

    1. .Net ODBC is painfully slow
    2. IBM OLEDB is better but sill 2.5 times slower than DTS
    3. Microsoft OLEDB requires SQL Server 2005 Enterprise Edition and cost blows this option out of the water.

    So yes, I'm not sure that MS OLEDB will perform OK but even if I had a categoric assurance that it's performance was better than DTS, it is the cost that is the inhibiting factor.

    Cheers
    Alister
    Wednesday, May 13, 2009 9:39 AM
  • the ibm ole db provider for db2 (not .net) on ssis 2005 standard edition should be faster than dts (i'm not following why you considered the .net version).

    is that not suitable for you?  if so, why not?

    hth
    Duane Douglas, MCAD | http://www.ssisbi.com | Please mark the post(s) that answered your question.

     

    SSIS Business Intelligence

    Thursday, May 14, 2009 7:38 AM
    Moderator
  • Hi Duane...

    As I said, the IBM OLEDB (not .Net) driver is about 2.5 times slower than DTS.  The .Net driver I referred to is for ODBC. 

    Cheers
    Alister
    Thursday, May 14, 2009 12:35 PM
  • Thanks Steve, this looks very good. As I read your blog I was saying to myself - yeah yeah, but what about the performance? However, your performance figures at the end certainly are impressive - I hope I get the same in my environment. I'll post my results to this thread. I'm at a critical part of the project at the moment, and the show must go on, so it may be a week before I can give this my full attention. Cheers AlisterN
    Friday, May 22, 2009 9:18 PM
  • Just wanted to say, if you even think you have a performance issue with the Microsoft DB2 Provider, then let me know. I've yet to have seen where we can not do as well, if not better than other providers.

    Another thing also - if you are using standard edition of SQL, Host Integration Server provides the same providers (usually newer) and I'm sure it's cheaper than others :).

    Thanks,
    Charles Ezzell

    Microsoft Host Integration Server Escalation Engineer


    Charles Ezzell - MSFT
    Wednesday, August 4, 2010 5:00 PM
  • The MSFT OLDDB provider for DB2 doesn't work the same as the IBM driver (iSeries) so sometimes it won't even connect to query the same to say, an AS400. 
    Wednesday, August 4, 2010 5:46 PM
  • In what context, and query? Our provider has been around for quite a number of years (SNA 4.0). We released the 'standalone' providers for SQL with SQL 2005. Our testing is against DB2/400, DB2/MVS, DB2/LUW. So, what errors are you getting? And what is the version of DB2/400 you are using? Granted, the different provider options can be a bit elusive for some, but I work personally with many VERY heavy users of DB2 around the world.

    That said, depending on the version of the provider you have, if your AS/400 is newer (say v6r1), you may need a new one. We have released a different version of the provider with each major release of SQL Server. The current version (v3) is on the SQL Server 2008 R2 Feature Pack download page. I would highly suggest testing against it. I recently did a test against V6R1, with our (newer) HIS 2010 release version of the provider, and pulled 8.3 million rows of data into SQL 2008 R2 in ~ 4 1/2 minutes ona 1GB network. SSIS itself has made some improvements in how it 'caches' data sometime after 2008 SP1, which has greatly improved our download speed. Also the V3 provider will allow you to move data from SQL back to DB2/400 a bit faster than previous versions (using openrowset with fastload).

    Anyway, if you can be a bit more specific, I can help you.


    Charles Ezzell - MSFT
    Wednesday, August 4, 2010 10:00 PM
  • Hi all,

     

    just to be clear: "MSFT OLDDB provider for DB2" still requires at least Enterprise edition of SQL Server right?

    SteveNovoselac: Thanks for your tip/method!


    Friday, October 22, 2010 7:31 AM
  • The microsoft documentation doesn't say SQL Server Enterprise Edition UNTIL you try and install on a SQL Server 2008 R2 STANDARD EDITION .

    [12:34:14 PM Info] Checking SQL version and edition for: Software\Microsoft\Microsoft SQL Server\MSAS10_50.MSSQL2K8SE\Setup
    [12:34:14 PM Info] Got Version 10.50.1600.1
    [12:34:14 PM Info] Got edition Standard Edition
    [12:34:14 PM Info] Found SQL Server Standard Edition.
    [12:34:14 PM Info] Not the correct edition.
    [12:34:14 PM Info] Done enumerating Sql Server instances, original hr = 0X80070103

     

    Anyone know where to getthe DB2OLEDB driver for SQL Server 2008 R2 Standard Edition.

     

    Regards

    Paul

    Thursday, March 24, 2011 12:35 AM
  • Paul,

    The download location states "The Microsoft OLE DB Provider for DB2 Version 3.0 offers a set of technologies and tools for integrating vital data stored in IBM DB2 databases with new solutions based on Microsoft SQL Server 2008 R2 Enterprise Edition and Developer Edition." That said, if you have a BizTalk license, you have a license to the particular version of HIS that is associated with that version of BizTalk, and you can use that (server licensing applies). Also, you can open a case with Support Services in your region (Europe, America, etc) against Host Integration Server and request that a case be escalated to allow you to use the 'free' provider on Standard. We will need justification of several sorts. If you have an account manager, it is best to work with him on this. If you have any further questions regarding this, please post to the Host Integration Server forum.


    Charles Ezzell - MSFT
    Thursday, March 24, 2011 12:03 PM
  • Charles,

    I have been trying to test the Microsoft OLE DB Provider for DB2 v 3.0 with SSIS OleDb Dataflow Components (Source & Destination) to pull/push data between DB2 and Sql Server 2008 R2. (downloaded from the Sql Server 2008 R2 Feature Pack). The source seems to be pulling DB2 data at an acceptable rate (GB networks/64-bit, 16 core 64GB RAM - same  SAN), although based on the hardware i'm expecting much better throughput. I cannot seem to get the push to DB2 to work efficiently, mainly because I'm not able to get the FastLoad option to work on the OLEDB2 driver with the Oledb Destination Component.

    What am I doing wrong?

    Also, I found this link to a hotfix, but after I installed, it rendered the OLEDB2 3.0 driver corrupt, as it was throwing exceptions when loading BIDS or when trying to connect via Connection Managers/Pipeline components.

    http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=2428175&kbln=en-us

    Fix: 2428175_x86_ENU

    Help!

     

     

    Thursday, September 15, 2011 3:55 AM
  • JStone...

    You probably are not doing much wrong, we've actually done a lot of fine-tuning in the provider(s) (HIS 2010/DB2OLEDBv3) for this option based on customer feedback and added in error redirection (which still needs a bit of fine tuning against some platforms of DB2 as they each behave differently). The latest hotfix for the v3 provider you can download from this support topic - http://support.microsoft.com/kb/2559748. See this article for optimizing things a bit - http://support.microsoft.com/kb/2503205. IBM does not recommend doing commits more than 100 rows at a time when doing multi-row inserts, so personally I would set FastLoadMaxInsertCommitSize to 100 in the SSIS package. That is mentioned in IBM Redbook titled "DB2 UDB for z/OS: Application Design for High Performance and Availability", see "Choosing a reasonable number of rows" topic in "Chapter 10: Advanced programming techniques". While referring to z/OS, it also applies to DB2/LUW and DB2/400. Note: With DB2/400 and the default settings with journaling turned on, performance will be slow - I've been able to get more than 10x throughput by turning off journaling on the tables I'm uploading to on v6r1.

    For pulling data from DB2, you can adjust the "Rowset Cache Size" option on the provider string. This helps us in prefetching data from DB2. Not needed as much with SQL 2008 R2 as SSIS also made improvements <apparently> in prefetching data since SQL 2005, but it can help a bit. I have a blog on testing this at http://blogs.msdn.com/b/charliee/archive/2009/06/03/performance-testing-the-microsoft-ole-db-provider-for-db2-part-2.aspx. it won't help in moving data to DB2. (Reminds me, I need to blog more)

    If you have any issues, don't hesitate to open a support case (open it against either SSIS or Host Integration Server and ask for it to be escalated to me), and I will be glad to assist you directly.


    Charles Ezzell - MSFT

    Thursday, September 15, 2011 8:06 AM
  • This is outstanding information. I will certainly take time in the next day to gather this information regarding the DB2 environment. One question, on the OleDb Destination component, I noticed that the FastLoad option is not an available drop-down choice when using the aforementioned Microsoft OLEDB2 3.0 driver connection. (see screenshot below)

     

    Charles,

     

     

    Thursday, September 15, 2011 8:41 PM
  • I think the problem is you haven't selected a connection manager. Plus, it's not available on that screen, and has been corrected in the next version of SQL (Denali). You'll need to manually select it on the properties of the destination (see picture). Note I've sent the FastLoadMaxInsertCommitSize.... to 100, and in that particular source query I'm moving 800K rows from SQL to DB2<hardcoded>. My actual connection string is:

    User ID=<userID>;Initial Catalog=DSNFD037;Provider=DB2OLEDB;Persist Security Info=True;Client Application Name=SSISTEST;Client User ID=CHARLIEE;Client Accounting=Move800KRows;Client Workstation Name=CSD3197145;Derive Parameters=True;Network Address=SYS1;Network Port=448;Package Collection=<userID>;Default Schema=<userID>;Default Qualifier=<userID>;AutoCommit=False;FastLoad Optimize=True;

    If you did not care about 'error redirection', you can set the FastLoad Optimize to False (or leave it out).

     

    Charles


    Charles Ezzell - MSFT
    Thursday, September 15, 2011 9:07 PM
  • Charles,

    Actually I did, but I blanked it out (used Snagit editor) to protect the connection manager name (has the company and DB2 ip). I can't seem to get the 2559748 hotfix you referenced working. (see exception below)

    Thanks again for your help on this,

    -- Jordan



    • Edited by jstone923 Friday, September 16, 2011 2:27 PM
    Thursday, September 15, 2011 9:23 PM
  • Charles,

    I've also tried the following Microsoft OLEDB DB2 3.0 hotfixes to no avail. After any single hotfix is applied, i receive the same error. I must be doing something goofy.

    I have Sql Server 2008 Client tools and Integration Services installed, but NOT the R2 version. Would this matter? Also, do I need the full database engine installed on my machine for this hotfix to install correctly?

     

     

    • Edited by jstone923 Friday, September 16, 2011 6:34 PM
    Friday, September 16, 2011 6:31 PM
  • I've seen a similar error with another customer, and it appeared to be something in BIDS/VS causing the problem, as he put the provider on another box and the issue went away. Do you have another box you can try, or run 'repair' on your install? If not, I would say it's time to open a case.


    Charles Ezzell - MSFT
    Friday, September 16, 2011 6:45 PM
  • I know this post is ongoing so i felt hte need to comment OLE DB is being deprecated by MS

     

    http://social.technet.microsoft.com/Forums/en/sqldataaccess/thread/e696d0ac-f8e2-4b19-8a08-7a357d3d780f

    or

    http://tinyurl.com/3qzzs8z

    Thursday, October 13, 2011 2:23 PM
  • No, it's the SQL Server Native Client OLE DB provider that is being deprecated. It does  not affect other OLEDB providers at all.


    Charles Ezzell - MSFT
    Thursday, October 13, 2011 2:35 PM
  • Hi Charles-

    Do you know if there has been any traction on this issue?  I'm having similar problems with installing the hotfix for the Microsoft OLEDB DB2 3.0 provider.  Prior to the application of the hotfix, the provider will work through BIDS (with the exceptions to the FastLoad feature in the OLEDB Destination Component, as noted above), however, after installation of the hotfix, I see the same error (included here for search engine goodness):

    "The procedure entry point RegGetValueA could not be located in the dynamic link library ADVAPI32.dll."

    If I remove the hotfix, the provider will work in the same manner prior to the hotfix being applied.

    Please let me know if there is a way to address this issue.

    Friday, November 18, 2011 4:07 AM
  • Are you using Windows XP, especially 32bit? If so, this is a bug, and we hope to have a fix for it soon. Please open a case with Microsoft Support. Basically we are using an API that is not supported on Windows XP32bit.
    Charles Ezzell - MSFT
    Friday, November 18, 2011 1:14 PM
  • Hi Charles-

    With respect to the particular error noted above, yes, I am using Windows XP 32-bit.  However, I migrated my SSIS package over to a Windows Server 2008 R2 64-bit box, installed the base V3.0 driver, and was able to configure and test a UDL to my DB2/6000 data source via the Data Access Tool.  The SSIS package also executed, albeit very slowly (~200 rows inserted/sec). 

    However, when I installed the hotfix on the 64-bit box and then tested the UDL via the Data Access Tool, it failed to connect with some exceptions (see below).  The SSIS package also failed to connect (see below).  Uninstalling the hotfix and reinstalling the base driver corrects the connectivity issue.

    Are there 32-bit and 64-bit versions of the driver/hotfix?

    ======= SSIS EXCEPTION ===================

    ([OLE DB Destination 1 [339]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "XXXXXX" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.).

    ======= DATA ACCESS TOOL EXCEPTIONS ===================

    See the end of this message for details on invoking
    just-in-time (JIT) debugging instead of this dialog box.

    ************** Exception Text **************
    System.BadImageFormatException: An attempt was made to load a program with an incorrect format. (Exception from HRESULT: 0x8007000B)
       at Microsoft.HostIntegration.DataAccessLibrary.DRDAAR.DRDAAllocHandle(Int16 handleType, ConnectionStructure& hstmt, IntPtr& handle)
       at Microsoft.HostIntegration.DataAccessLibrary.DRDAAR.AllocHandle(DrdaARConstants handleType, IntPtr requesterHandle, IntPtr statement)
       at Microsoft.HostIntegration.DataAccessLibrary.ARConnection.Connect()
       at Microsoft.HostIntegration.DataAccessLibrary.DataAccessControl.DB2TestConnection(IConnectionString connStr, String& serverClass, String& serverVersion)
       at Microsoft.HostIntegration.DataAccessLibrary.DataAccessControl.TestConnectionUI(IConnectionString connStr, String& output)
       at Microsoft.HostIntegration.DataAccessTool.ActionBinding.TestConnection()
       at System.Windows.Forms.MenuItem.OnClick(EventArgs e)
       at System.Windows.Forms.Command.Invoke()
       at System.Windows.Forms.Control.WmCommand(Message& m)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)


    ************** Loaded Assemblies **************
    mscorlib
        Assembly Version: 2.0.0.0
        Win32 Version: 2.0.50727.4961 (win7RTMGDR.050727-4900)
        CodeBase: file:///C:/Windows/Microsoft.NET/Framework64/v2.0.50727/mscorlib.dll
    ----------------------------------------
    Microsoft.HostIntegration.DataAccessTool
        Assembly Version: 8.0.4100.0
        Win32 Version: 8.0.4294.0
        CodeBase: file:///C:/Program%20Files/Microsoft%20OLE%20DB%20Provider%20for%20DB2/system/Microsoft.HostIntegration.DataAccessTool.exe
    ----------------------------------------
    Microsoft.HostIntegration.DataAccessLibrary
        Assembly Version: 8.0.4100.0
        Win32 Version: 8.0.4294.0
        CodeBase: file:///C:/Windows/assembly/GAC_MSIL/Microsoft.HostIntegration.DataAccessLibrary/8.0.4100.0__31bf3856ad364e35/Microsoft.HostIntegration.DataAccessLibrary.dll
    ----------------------------------------
    System.Windows.Forms
        Assembly Version: 2.0.0.0
        Win32 Version: 2.0.50727.4961 (win7RTMGDR.050727-4900)
        CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Windows.Forms/2.0.0.0__b77a5c561934e089/System.Windows.Forms.dll
    ----------------------------------------
    System
        Assembly Version: 2.0.0.0
        Win32 Version: 2.0.50727.4962 (win7RTMGDR.050727-4900)
        CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System/2.0.0.0__b77a5c561934e089/System.dll
    ----------------------------------------
    System.Drawing
        Assembly Version: 2.0.0.0
        Win32 Version: 2.0.50727.4927 (NetFXspW7.050727-4900)
        CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Drawing/2.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll
    ----------------------------------------

    ************** JIT Debugging **************
    To enable just-in-time (JIT) debugging, the .config file for this
    application or computer (machine.config) must have the
    jitDebugging value set in the system.windows.forms section.
    The application must also be compiled with debugging
    enabled.

    For example:

    <configuration>
        <system.windows.forms jitDebugging="true" />
    </configuration>

    When JIT debugging is enabled, any unhandled exception
    will be sent to the JIT debugger registered on the computer
    rather than be handled by this dialog box.

     

    Friday, November 18, 2011 5:36 PM
  • There are both 32bit and 64bit versions of the hotfixes. If you download directly from the article, it will offer you the one based on the platform of your current operating system. Also, there can be multiple versions of the fix, for both Host Integration Server and for the stand-alone data provider. I've not tested trying a 32bit hotfix install on a 64bit installation, it should not allow it, but then again, it may, since the 64bit installation does contain both 32bit and 64bit binaries.
    Charles Ezzell - MSFT
    Monday, November 21, 2011 12:32 PM
  • Perfect, that fixed it.  I did have to uninstall everything and reinstall, but that was quick.

    The browser issue was my problem - I downloaded the hotfix from my 32-bit client and didn't bother to look at the (rather explicitly named) filename.

    Thanks,
    Todd

    Monday, November 21, 2011 6:26 PM
  • Cool. I'm sure the 32bit bit blew things apart on the 64bit system. I'll open a bug against this so that 32bit installs won't install on 64bit systems.
    Charles Ezzell - MSFT
    Monday, November 21, 2011 6:29 PM
  • Hi Charles-

    Is there any way to get the latest version of the DB2 OLE DB Provider, with all of the hotfixes included?  I have the 8.0.4309 version of the DB2OLEDB.DLL installed, but I see documents that identify other versions (e.g. 8.5.4367.2) that are included in the HIS 2010 Cumulative Update referenced here:  http://support.microsoft.com/kb/2533330.  I seem to be having the issue identified by KB2559748( http://support.microsoft.com/kb/2559748), but the fix seems to be included in the HIS 2010 CUM update.  I only have the DB2 OLE DB provider installed.

    Thanks,

    Todd

    Wednesday, November 23, 2011 10:20 PM
  • Todd - We do not have a Cumulative update available for DB2OLEDBv3. The article you mention has the fix for your particular provider available.

     

     Note we will be releasing with SQL 2012 an updated version of the provider. If that does not solve your issue, please let me know.


    Charles Ezzell - MSFT
    Thursday, November 24, 2011 12:09 AM