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.ThanksAlister

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.hthDuane Douglas, MCAD | http://www.ssisbi.com | Please mark the post(s) that answered your question.

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.CheersAlister

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.CheersAlister 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.hthDuane Douglas, MCAD | http://www.ssisbi.com | Please mark the post(s) that answered your question.

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

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

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.

Hi Duane...I've decided to stick with DTS because:1. .Net ODBC is painfully slow2. IBM OLEDB is better but sill 2.5 times slower than DTS3. 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.CheersAlister

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.

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.CheersAlister

AlisterN - try this method..http://blog.stevienova.com/2009/05/20/etl-method-fastest-way-to-get-data-from-db2-to-microsoft-sql-server/

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

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 EngineerCharles Ezzell - MSFT

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.

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

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!

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

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