OLEDB provider for DB2


You got DB2?  Got SQL?   Now you can connect them together, better.  Microsoft is making the OLEDB provider for DB2 available for download.



The Microsoft OLE DB Provider for DB2 is a COM library for connecting to IBM DB2 databases.  It is licensed for use with solutions based on Microsoft SQL Server 2005 Enterprise Edition and Developer Edition. SQL Server developers and administrators can use the provider with SQL Server Integration Services, SQL Server Analysis Services, Replication, and Distributed Query Processor.  The single setup program will install the provider and tools on both x86 and x64 computers. Read the installation guide and Readme for more information.


http://download.microsoft.com/download/4/4/D/44DBDE61-B385-4FC2-A67D-48053B8F9FAD/DB2OLEDB.exe


IBM also has an OLEDB provider for DB2, but it is licensed separately, I think at extra charge.  You have to buy the DB2 Client Access license or something like that.  This one is licensed with SQL2005 EE.  Nice!


This enables you to, for example, define linked servers to external DB2 servers, running on any platform, and perform queries on them. With those linked servers, you’ll be able to see DB2 data in the SQL Management Studio, in Visual Studio designers, and so on.  And of course in your applications.   Let’s say you have a Java app running on an AS/400, connecting to DB2 there.  With this OLEDB provider, your .NET apps running on Windows Server 2003 can connect to the same DB2, the same tables.  Java-and-.NET interop via a shared database.


Best,


-Dino

Comments (16)

  1. Pontus says:

    Even better if Standard edition would be supported. Enterprise edition is fairly costly.

    Has anybody tried on SQL 2005 standard edition?

    / Pontus

  2. cheeso says:

    Pontus, I don’t believe the OLEDB provider is disabled from working on other editions, but it is *licensed* for use with Enterprise Edition.  -Dino

  3. Bob_Chauvin says:

    I’m having trouble using this driver… Any sites that are a good reference?  

    Generally, I can connect via odbc, but the data source wizard is asking for Initial Catalog and Package collection…

    What are these?

  4. cheeso says:

    Initial Catalog is the default database. The package Collection is a DB2-ism. For more details, see

    http://msdn.microsoft.com/library/en-us/his/htm/_sna_all_db2oledb.asp

  5. Bob C says:

    Thanks.  Yeah.  I’m finding the oledb driver to be very slow .vs. the ODBC via the IBM Client Connect software…

  6. cheeso says:

    That is curious. In our internal testing, we generally get better perf on the OLEDB driver than on the ODBC stuff from IBM.  Likewise IBM has an OLEDB driver, and again we think we have better perf.  However, there is a matrix of features, and not all are available on each driver.  In some cases perf is not the deciding factor, it is feature support (clobs, cursors, etc).  

  7. Bob_Chauvin says:

    There may be other issues that I’m running in to.  I’m connecting from a W2003 server, for SQL2005 creating a linked server to DB2 to consolidate access to on platform (views to DB2).

    Do you know of any good links other than the prior you provided regarding using MS’s DB2 driver?  

  8. cheeso says:

    So are you saying, compairng the linked server setup to the potion of connecting directly to db2, you get better perf connecting directly?

    or are you saying, when configuring db2 as a linked server, you get better perf thru ODBC than thru OLEDB.  

    The former I can believe, the latter sounds pretty surprising.  

    If you are not happy with the linked server approach, you can define an integration  package and transfer the DB2 data to SQL on a schedule – in which case the data access is local to SQL .  But you will have a synchronization issue – in some cases db2 will have newer data than SQL.

  9. Bob C says:

    Unfortunately I see the ODBC as better perf compared to OLEDB when I create a linked server, and then issue sql to the DB2 from SQL Server.

    I guess I’m suprised too, but thought that perhaps it was due to the fact that the ODBC driver is from IBM, and the OLEDb from MS.

    An example of performance issue is this:  There is a long delay when issuing a select command via the oledb linked server which seems to grow based on the number of rows in the DB2 table.  One side affect of this is that If I try to create a view against a table with a thousands of rows and many cols, the create view will time out in the view designer (will succeed in if created via a query pane, but takes 20+ seconds to process…)  Small tables work in the view painter, but still take 20+ seconds to parse.

    ODBC on the other hand performs as expected.

    Maybe there is a problem on the DB2 server, or some setting on the W2003 server?

  10. DeeptiChand Parvataneni says:

    Hi,

    Is there any documentation on performance of this OleDB provider compared with ODBC driver for DB2. Eventhough we know OleDB performs better than ODBC , i was wondering if we have some documentation somewhere .

    Thanks,

  11. cheeso says:

    I don’t know of any perf comparisons.  I think it would depend on the scenario, and the best approach would be to benchmark it yourself.

  12. This example shows how to add a remote DB2 as a linked server, using the MS OLEDB provider for DB2 (I

  13. Wynn says:

    This driver was installed on SQL 2005 server, but I can’t seemed to install this driver on client (has sql 2005 client installed on machine) so I can connect to as400 thru bids.

  14. cheeso says:

    Wynn, May I suggest that you contact support regarding this issue?   My understanding was that the driver was supported for SQL Enterprise, to allow SQL Server to connect outward (as with a linked server).  I may be wrong about that.  If I am correct, though, it seems to follow that a client computer connecting to AS400 (through BIDS or otherwise) would not be a scenario supported by that license.  

  15. George says:

    I have used the DB2 Sql driver to connect to the AS400 with SQL 2005 Standard edition but I cannot get it to work using replication with the AS400 iSeries as the subscriber.

  16. Arvin says:

    I have downloaded the OLEDB and installed successfully. From Visual Studio 2012 SERVER EXPLORER on expanding the TABLES list it turn out to be empty.

    Please help