DB2 to MS SQL 2

[Dimandja]

I need to copy tables from DB2 to MSQL. (There also are stored procedures in DB2.) The tables are big size-wise.

What is the best course of action? For example, a DTS package, CSV files, a procedure involving ODBC.NET?, or anything else entirely?

 

[yksvaan]

Well, DTS is as good or bad as any of the other tools. It more depends what kind of database you have on db2 and how much data there actually is. Maybe dbadmin of db2 won't even let you take a oledb connection to db2(let's say it is 24-7 online, probably not). In that case a batch on db2 would make a textfile(csv or fixed) and you would still use DTS to load it (first copy it to sql server machine's disk).

I'd say DTS is quite easy to start work with interactively. When you start trying to automate it like a programmer programs, challenges arise.

But what did you mean with there are stored procs in db2 ?

Cheers

 

[Dimandja]

I was told there are stored procedures in DB2. But, as business requirements most likely will change, I am not too worried about them at this moment. But, I may have to bring them accross as well. Is there an automated way to do this?

 

[rasETL]

Dimandja

First, there are differences between MSSQL and DB2, both in data and Stored procedures.

Given that, I do not believe there is a way to port Stored procedures directly to MSSQL - at least I have never tried it.

As far as data - you can directly move DB2 Table data to SQL Server vis DTS, but keep in mind the following...

- Transformations will need to be set up for some field types, such as Datetime fields. DB2 and SQL store some field types differently.
- Transferring data from large DB2 Tables can put a strain on your network. I work at a large Company, and my attempt to move data from a 2+ million row table directly via DTS got me a call from a network admin - I was using 40% of the network for the time the process ran. This takes even more resources when you go MS SQL to DB2.

You may be better off using a DB2 utility to dump the DB2 Table to a flat file, maybe comma-delimited, then create a package to load MS SQL, or , use BCP or Bulk Insert.

Hope that helps.

 

[yksvaan]

The first thing I always ask is why. Why would you try copying sp ? Is there a proof of concept about this ? Or are you just playing with an idea ? rasanders had good comments.

BTW, if you in loading a textfile use either DTS Transform Data Task (with Fast Load option checked) or BULK INSERT or bcp, I think they kind of do the same thing anyway. Probably there won't be big differences in performance. But I don't have any experience with large files.

rasanders, I agree that a flat-file approach might be best here. But I absolutely do NOT recommend comma-delimited nor semicolon-delimited nor space-delimited as a general advice, big problems with names, addresses, product names etc. I recommend tab-delimited or fixed-length file.

Cheers