|Microsoft SQL Server||1,231,433|
I mean, I don't work with servers that require nearly that horsepower but it's interesting to see.
So now I have a chance to work with DB2 on an iSeries server. Granted, I'm not doing much other than SELECT statements, but I'm having fun. The initial setup was a little confusing for me. According to IBM's documentation website, there's two ways to access the DB2 server: via SQL or via "traditional file access." The firm I work with uses traditional file access terminology so translation to SQL terms was necessary. Overall it was pretty easy; here's a cheat sheet:
|SQL Server||IBM file access|
The accounting department gave me a PDF which contained the following information.
To get the Linked Server going, I installed the iSeries Client Access on the SQL Server which supplied the IBM AS400 OLE DB Provider. From there, I created the Linked Server with the following information:
|Provider||IBM AS400 OLE DB Provider|
|Data Source||<IP of server>|
|I mapped a local account using the credentials supplied to me by the accounting department.|
Initally, when expanding the table structure, I received an error: The OLE DB Provider "IBMDA400" for linkedServer "AS400" reported an error. Acccess is denied. Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB Provider... Error: 7399. but after researching, found that I simply needed to select the box "Allow Inprocess" under the IBMDA400 Provider options and this error went away. I then experimented with a few different SELECT statements to see which one was easiest. Ultimately, I ended up using the following queries to import the data from the DB2 server into SQL Server 2005:
SELECT * INTO importClients from AS400.ServerName.CARPDIEM.CDCLIENT
If you don't know the variable for ServerName in the above query, you can refer to this tutorial. Also, I was able to use Microsoft's DB2 Provider, as described here. The Initial Catalog is the ServerName and the Package Collection and Default Schema are both the schema/username. Not sure what the speed differences between the two providers are, if I find the time to benchmark, I'll be sure to post the results.
I admit, I don't have too much experience with DB2 yet. But it's on my to-do list and I'm working on it. I recently had to import some data from DB2 hosted on an iSeries server. I had the hardest time figuring out the hostname and LINKEDSERVER..SCHEMA.TABLE didn't work. Once DTS helped me figure out the hostname, I suddenly saw it everywhere. Here are five ways to discover an iSeries hostname:
- GET ADMIN CONFIGURATION
From a command prompt, run db2 GET ADMIN CONFIGURATION | grep DB2SYSTEM
Telnet to the system's IP address. The hostname will appear in the lower right hand corner above IBM's copyright information.
- Schema Documentation.
The accounting department printed out a PDF file for me that outlined the schema of the views that were generated for my account. The hostname was listed immediately to the right of the title "IBM Query for iSeries."
- Microsoft's DTS Wizard
This step requires a valid DB2 Provider: Use DTS Wizard to connect to the iSeries server. Once a successful connection is made, chose to import entire tables. In the next screen, you will be presented with the transformation screen. This screen contains 3-part table names that include the hostname.
- Perform a malformed SQL Server-based query on a DB2 linked server.
This step requires a DB2 linked server, which requires a valid DB2 provider: While playing around with the syntax of OPENQUERY, I executed the following statement SELECT * FROM OPENQUERY(AS400, 'SELECT * FROM LinkedServer.Schema.Table') and the following error was returned:
OLE DB provider "IBMDA400" for linked server "AS400" returned message "SQL0114: Relational database AS400 not the same as current server SERVERNAME.
Cause . . . . . : Relational database AS400 was specified in a 3 part name in the statement. However, either the name is not the same as the current server SERVERNAME, or the name is not the same as a relational database name specified previously in the statement.
Now my 4-part queries on a DB2 Linked server in SQL Server totally work. In this case, SELECT * from LinkedServer.SERVERNAME.Schema.Table. Ohhh, shiny!
In order to emulate a DB2/iSeries<->SQL Server setup that I deal with at work, I installed DB2 Express-C on SuSE Linux Enterprise Server 10. The DB2/Linux Server details, as well as a sample database and table name, are as follows:
|IBM DB2 Database|
After the initial install and sample database/table creation, I set out to create a Linked Server in SQL Server 2008. Initially, I installed IBM Data Server Client client tools, which includes a few JDBC drivers, on the SQL Server. One of these providers is "IBM OLE DB Provider for DB2," showed up in the list of Linked Server Provider in SQL Server after the software was successfully installed.
Setting up the Linked Server was pretty straight-forward and initially seemed to work, using the following details:
|Provider||IBM OLE DB Provder for DB2|
|Login Info||Your call. I mapped a local user to db2user.|
|* IBMADDB2.DB2COPY is the name of the IBM DB2 provider in the Linked Server Provider list.|
The server was created easily enough, but SQL Server's automatically generated SELECT statements kept erroring out. The syntax was as follows: SELECT fName, lName FROM IBMDB2..DB2USER.EMPLOYEES. Upon execution, the following error was returned:
Msg 7318, Level 16, State 1, Line 1
The OLE DB provider "IBMDADB2.DB2COPY1" for linked server "IBMDB2" returned an invalid column definition for table ""DB2USER"."EMPLOYEES"".
So I added in the database name (IBMDB2.SAMPLE.DB2.EMPLOYEES) to the SELECT statement, executed the query and received the error:
Msg 7313, Level 16, State 1, Line 1
An invalid schema or catalog was specified for the provider "IBMDADB2.DB2COPY1" for linked server "IBMDB2".
Past experience taught me that it was now probably time to look for a new driver. After some research, I found an IBM DB2 Driver in the SQL Server 2008 Feature Pack which can be found: OLEDB Provider for DB2 (DB2OLEDB.exe). According to Microsoft..
The Microsoft OLE DB Provider for DB2 version 2.0 is a COM component for integrating vital data stored in IBM DB2 databases with new solutions based on Microsoft SQL Server 2008 Enterprise Edition and Developer Edition. SQL Server developers and administrators can use the provider with Integration Services, Analysis Services, Replication, Reporting Services, and Distributed Query Processor.
After extracting the contents and clicking setup.exe as instructed, the full-on GUI-based installer failed. Ultimately, I had to run the install from the command line: setup.exe /L c:\HISInstall.log /S /INSTALLDIR C:\HIS and it worked flawlessly after a reboot that was required anyway by Windows Update.
Once the promising new driver was installed, it an additional 2 hours to figure out exactly what Linked Server parameters were required. I kept running into an error that said "The parameter is incorrect." Ultimately, I figured out how to set it up by running the Data Access Tool included with Microsoft's DB2 Provider. After I setup a successful connection to the DB2 Server, the Data Access Tool gave me the Provider String that I needed to create a successful connection.
|Provider||Microsoft OLE DB Provider for DB2|
Provider=DB2OLEDB;Initial Catalog=SAMPLE;Network Transport Library=TCP;Host CCSID=37;
PC Code Page=1252;Network Address=lucas;Network Port=50000;Package Collection=DB2USER;
Default Schema=DB2USER;Process Binary as Character=False;Units of Work=RUW;
DBMS Platform=DB2/MVS;Defer Prepare=False;Rowset Cache Size=0;Persist Security Info=True;
Connection Pooling=False;Derive Parameters=False;
|Login Info||Your call. I mapped a local user to db2user.|
The Data Access Tool gives the username and password in clear text in the Provider String but I just removed that and chose to setup the username and password under the Security Options. Ultimately, this was the setup that worked for me. I am now able to query the data using a simple 4 part name: SELECT fName, lName FROM DB2.SAMPLE.DB2.EMPLOYEES. Success! If you'd like to create the Linked Server using only T-SQL, run the following command, suited to your environment, of course.
EXEC master.dbo.sp_addlinkedserver @server = N'DB2', @srvproduct=N'DB2OLEDB', @provider=N'DB2OLEDB',
@datasrc=N'LUCAS', @provstr=N'Provider=DB2OLEDB;Initial Catalog=SAMPLE;Network Transport Library=TCP;
Host CCSID=37;PC Code Page=1252;Network Address=lucas;Network Port=50000;Package Collection=DB2USER;
Default Schema=DB2USER;Process Binary as Character=False;Units of Work=RUW;DBMS Platform=DB2/MVS;
Defer Prepare=False;Rowset Cache Size=0;Persist Security Info=True;Connection Pooling=False;
Derive Parameters=False;', @catalog=N'SAMPLE'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DB2',@useself=N'False',
In my next post, I'll cover a more likely DB2<->SQL Server scenario: creating a linked server in SQL Server 2005 to IBM DB2 running on an AS400/iSeries server.
IBM DB2's free version "DB2 Express-C" is available for download at db2express.com. So far, I've successfully installed and uninstalled DB2 Express on SLES 10 SP2. While there was a command line installer included (db2_install), the server wouldn't start after a supposedly successful install. I figured I would have to use the GUI install, but my SLES instance doesn't run X-windows so I had to download X-Win32 and export my display (export DISPLAY=cracklin:0.0) to my laptop and go from there.
Administration of DB2 Express-C is performed using IBM Data Server Client (registration required), which also includes the IBM DB2 OLE DB drivers needed to create a DB2 linked server in SQL Server.
After working with the Server Client (which contains the Control Center, DB2's equivalent to SQL Server's Enterprise Manager), I wanted to reinstall DB2 with different parameters, specifically the administrator account and the TCP port. DB2's default port is 50000 but the Linux install sets it to 50001 for some reason. I can probably change this using the built in command-line tools but I didn't want to invest to time to track it down, so I uninstalled DB2 and reinstalled the server by executing the following:
chmod +x /opt/ibm/db2/V9.5/instance/*
chmod +x /opt/ibm/db2/V9.5/bin/*
chmod +x /opt/ibm/db2/V9.5/adm/*
chmod +x /opt/ibm/db2/V9.5/das/bin/*
rm -rf /opt/ibm/db2
Then I reinstalled with a whole new perspective on the way things work. If you're interested in what the IBM Control Center looks like, here's a sample from my own environment (Windows client, Linux server).
Click to enlarge.
So far, so fun