Testing DB2 OLEDB Provider using Microsoft Excel

Testing DB2 OLEDB Provider using Microsoft Excel

 It is well known that from DB2 control center, one can test connectivity using configuration assistant for various providers like OLEDB , CLI , JDBC , ADO .

But configuration assistant  can’t fetch data using oledb provider from DB2 table unless one has some custom application to fetch data using specified providers.


Microsoft excel provides another way and such readymade application to test and fetch data from DB2 table using OLEDB provider.

For windows DB2 32 bit installation, the dll name for DB2 oledb provider is ibmdadb2.dll.  For windows 64 bit DB2 installation, oledb provider comes with both 64 bit ibmdadb264.dll and 32 bit ibmdadb2.dll  found in sqllib/bin directory.

Though example is provided in excel 2003, it can be used for other excel version like excel 2007 and excel 2010.

and any database like sample can be used for testing. In this example we used our custom database created for testing.


Following steps gives detailed overview of testing DB2 oledb provider  using excel

1a - Launch MS Excel 2003.

1b - Go to Data->Import External Data-> Import Data

1c-  Click and open  “Connect to new data source.odc

1d - This will launch Data Connection Wizard. Click and select Other/advanced and click next


2 . It will open datalink properties dialog box. Select and double click the IBM OLEDB provider which corresponds to OLEDB provider Driver copy.


3 - This will display “All” tab properties corresponding to OLEDB provider



4.  There are two options to test connectivity to database. One is with DSN defined in

db2dsdriver.cfg and another one is without DSN .


4A - Option A- With DSN defined in db2dsdriver.cfg

Select the data source and give the DSN name  as defined in db2dsdriver.cfg

For example data source “TDB2” is used in this example.



It will open “Connect to DB2 database” dialog box.

Provide the User ID and Password for the Database to be connected.


4B- Option B- if there is no DSN defined in Db2dsdriver.cfg .

Go to “All” tab and click on extended properties, provide the information like

Database, Hostname, Protocol and port for the database server.


 It will open “Connect to DB2 database” dialog box

Provide the UserID and password for the database and click OK.


5. Select the table from which data need to be imported.

   Click Next button to continue.



6. Save the Data connection file so that it can be used next time when importing data from same Data source and table. Click Finish button.



7. Select the start column of the worksheet to import the table data and click OK.

   This will put the data from the database table in the selected worksheet column.


8. After clicking OK button ,Microsoft  excel displays the data from the table EMP which is selected in step 5.


In this way not only one can test OLEDB provider but can also verify the data coming from DB2 database table.



1) Prashant N Kulkarni

2) Arun Kumar purnaiah



Views: 4843


You need to be a member of ChannelDB2 to add comments!

Join ChannelDB2

Featured Downloads

Try BLU Acceleration on Cloud

© 2020   Created by channeldb2.   Powered by

Badges  |  Report an Issue  |  Terms of Service