How to Import Data into SQL Server 2005 From DB2 (AS400/iSeries)
Ever since I read how much IBM's DB2 SMOKED Oracle and SQL Server in the tpc-c (OLTP) benchmarks, I've wanted to work with it. The benchmarks go a little something like this:
|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.