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.
About
-
We are IT consultants
who grew up living and loving life
in Cajun Country.
Authors
Categories
What's Your Job Title?
Loading ...
information
wants to be free
February 23rd, 2009 - 01:38
If you need any clarification pls mail me. I will help you..
I have 4 yrs expr in AS400 application.
Thanks,
Gnanavel B(Malaysia),
gnanavel_b@yahoo.com.
March 10th, 2009 - 04:01
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)
March 13th, 2009 - 20:22
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
March 16th, 2009 - 15:05
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
May 18th, 2009 - 10:26
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
November 19th, 2009 - 11:05
Looking into doing this myself…on which version of SQL server were you linking these tables?
November 20th, 2009 - 10:32
That ‘allow inprocess’ bit just saved me quite a bit of time. Thanks
It’s working for me in MSSQL ‘08.