Udostępnij za pośrednictwem

OLEDB provider for DB2

  • Artykuł

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.

https://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

  • Anonymous
    February 07, 2006
    Even better if Standard edition would be supported. Enterprise edition is fairly costly.
    Has anybody tried on SQL 2005 standard edition?

    / Pontus

  • Anonymous
    February 08, 2006
    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

  • Anonymous
    February 16, 2006
    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?

  • Anonymous
    February 21, 2006
    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

  • Anonymous
    February 22, 2006
    Thanks.  Yeah.  I'm finding the oledb driver to be very slow .vs. the ODBC via the IBM Client Connect software...

  • Anonymous
    February 22, 2006
    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).  

  • Anonymous
    February 22, 2006
    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?  

  • Anonymous
    February 23, 2006
    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.

  • Anonymous
    February 23, 2006
    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?

  • Anonymous
    March 17, 2006
    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,

  • Anonymous
    March 17, 2006
    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.

  • Anonymous
    August 24, 2007
    This example shows how to add a remote DB2 as a linked server, using the MS OLEDB provider for DB2 (I

  • Anonymous
    August 01, 2008
    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.

  • Anonymous
    August 04, 2008
    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.  

  • Anonymous
    November 07, 2008
    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.

  • Anonymous
    November 16, 2014
    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