Excel to IBM DB2 Connection
Have you tried using the IBM DB2 for i IBMDA400 OLEDB Provider included with IBM i Access?
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
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.
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
.
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
DBMS Platform
Select DB2/AS400
.
TEST
user1087943
Updated on June 04, 2022Comments
-
user1087943 almost 2 years
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
-
user1087943 over 12 yearsThanks for the reply James. I have that exact provider on SQL Server 2008, although when I enter the details to create a linked server I get the same error in my original post. I entered the connection string in your post and also tried one for "IBM OLEDB provider for db2" but both gave the same error. Would you know what to enter as the product name, data source and catalog? Thanks
-
James Allman over 12 years@user1087943 I don't have a product name option with the IBMDA400 OLEDB driver. The data source would be the system name or ip address of the AS/400. The catalog would be the default library you would like to use. I'll try to set up a VM with SQL'08, Office'10 and the MS DB2 driver to test when I get a chance.
-
James Allman over 12 years@user1087943 Updated with documentation on configuring the Microsoft OLEDB Driver for DB2 with the IBM i as originally requested.
-
user1087943 over 12 yearsThanks for the thorough guide James. Turns out it was an issue with the Microsoft driver for DB2.