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.
1) Prashant N Kulkarni
2) Arun Kumar purnaiah