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:

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:

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:

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.