Excel to IBM DB2 Connection

4
votes

I've been attempting to establish a connection with an IBM DB2 database with which I can extract the data.

The details I have are:

  • Server Name
  • Data Library
  • Username/Password.

I've tried two different ways:

Using Excel:

Within Excel, I have gone onto data-> From other sources -> From data connection wizard -> other/advanced -> Microsoft OLE DB Provider for DB2.

I have entered the server name for the data source parameter and the username and password in the appropriate parameters. Whenever I click on the "test" button, the same error is returned: "Test connection failed because of an error in initializing the provider.- The parameter is incorrect"

Using SQL Server 2008:

I have also attempted to connect to this DB2 database through SQL Server 2008 creating a new linked server and entering the server name in the data source parameter and username and password in the security page.

The provider I've selected there is "IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider". The error I get here is: "The linked server has been created but failed a connection test"

Any advice on how to connect to the DB2 database from excel or SQL Server 2008 would be extremely helpful.

Thanks

1
excelodbcdb2oledbibm-midrange
We had to use the DB2 Connection Wizard to set up an ODBC connection to our "DB2 Connect" thing. After that, we were able to provide different users and passwords to the connection but there seemed to be some voodoo that had to occur in that wizard before we could just use it. – billinkc
I have a similar problem, where I don't have driver for DB2 under option 2 drop down list while creating a new data source. But I have a DB2 driver copied from my colleagues machine, all i need to know is, can I use this driver dump from my colleague's machine by placing this driver at the drivers folder of microsoft query through excel. If so, whats the path? Do let me know if something is not clear. – Raja Reddy

1 Answers

3
votes

Have you tried using the IBM DB2 for i IBMDA400 OLEDB Provider included with IBM i Access?

enter image description here

I tested it briefly with Excel 2010 and it worked perfectly. The driver can be installed directly from the IBM i system using the UNC path \\system\QIBM\ProdData\Access\Windows\cwblaunch.exe.

How to configure the Microsoft OLE DB Provider for DB2 to connect to an IBM i (AS/400)

PROVIDER

Provider

CONNECTION

Connection

DATA SOURCE

The optional Data source parameter names the Universal Data Link (UDL) file the settings are saved in.

NETWORK

The Network parameter must be TCP/IP Connection. You must click the ellipsis and configure the IP address of the IBM i.

TCP/IP Network Settings

SECURITY

The Security parameters are self-explanatory.

DATABASE

The Initial Catalog parameter refers to the relational database name configured on the AS/400 system. It can be found with the DSPRDBDIRE command from a terminal session. The default is the system name (serial number) or RCHASE12.

DSPRDBDIRE

The Package Collection is the name of a library where temporary objects required by the Microsoft OLE DB Provider for DB2 will be created.

The Default schema is the name of the default library for unqualified objects.

ADVANCED

Advanced

DBMS Platform

Select DB2/AS400.

TEST

Test