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:

RDBMS tpc-c
IBM DB2 6,085,166
Oracle 4,092,799
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
Schema/Owner Database/Library
Table/View File
Row Record
Column Field

The accounting department gave me a PDF which contained the following information.

DB Info
File CARPDIEM
Library CDCLIENT
Fields M00000, M00001

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:

General
Linked Server AS400
Provider IBM AS400 OLE DB Provider
Product Name <anything>
Data Source <IP of server>
Security Options
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.

Chrissy is a PowerShell MVP who has worked in IT for nearly 20 years, and currently serves as a Sr. Database Engineer in Belgium. Always an avid scripter, she attended the Monad session at Microsoft’s Professional Developers Conference in Los Angeles back in 2005 and has worked and played with PowerShell ever since. Chrissy is currently pursuing an MS in Systems Engineering at Regis University and helps maintain RealCajunRecipes.com in her spare time. She holds a number of certifications, including those relating to SQL Server, SuSE Linux, SharePoint and network security. She recently became co-lead of the SQL PASS PowerShell Virtual Chapter. You can follow her on Twitter at @cl.

Posted in DB2, SQL Server
11 comments on “How to Import Data into SQL Server 2005 From DB2 (AS400/iSeries)
  1. Gnanavel B says:

    If you need any clarification pls mail me. I will help you..

    I have 4 yrs expr in AS400 application.

    Thanks,
    Gnanavel B(Malaysia),
    [email protected].

  2. GooGui says:

    i need some help, and you two seems to know lots more than me about this subject…
    i cannot get a linked server to work for some tables. it keeps giving me binary fields instead of char fields even if i try the force translate parameter…
    my last cx string :
    __
    EXEC master.dbo.sp_addlinkedserver @server = N'DBTST', @srvproduct=N'IBMDA400', @provider=N'IBMDA400',
    @datasrc=N'CML400.EU', @provstr=N'Provider=IBMDA400;Data Source=CML400.EU;User ID=EXTPGM;Password=########;Force Translate=0;Process Binary as Character=True'

    EXEC master.dbo.sp_serveroption @server=N'DBTST', @optname=N'Force Translate', @optvalue=N'0'
    GO
    __

    any clues?

    Best regards,

    GooGui (France)

  3. Chrissy says:

    Hey GooGui,
    I'm really not sure. What I do is import it first into "harvest" tables in SQL, then modify the data and datatypes after the fact.

    Using DTS/SSIS may also give you better results.

    Good luck!
    Chrissy

  4. mygr8r says:

    Hi GooGui,

    I used to import the data into staging tables and modify the data types of the fields like Chrissy did, until I found some 3rd party tools that eliminated those extra steps. Check out the tools, called DataTools, on pervasivedatatools.com. I've used several of their tools with much success.

    Hope this helps!
    mygr8r

  5. as400 says:

    on the jdbc connections there is a parameter 'translate binary=true', check if there is something similar for ODBC, miht even be the same parameter

  6. C. Lambert says:

    Looking into doing this myself…on which version of SQL server were you linking these tables?

  7. Agent says:

    That 'allow inprocess' bit just saved me quite a bit of time. Thanks :)

    It's working for me in MSSQL '08.

  8. Justin Parkinson says:

    I have a table in a SQL Database, that has a foreign key in one of the AS400 tables. Is there any way that I can import that AS400 table so that it forms part of the SQL Database diagram? I don't need to write to it, just read from it.

  9. rick wenger says:

    Do you know where I can get the as/400 odbc client? I've looked everywhere. We are timesharing currently on an as/400 and can't get the as/400 odbc client.
    I've tried looking on the ibm website and its not very informative. I may try to call their support number, but, I thought I would ask first.

  10. Cathy says:

    Hi,
    Can you please let me know how to get to the "Allow InProcess" property? Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

*