transferring data from SQL server 2008 to Db2/400 database

Question

i have  table in sql server 2008 database which i need to transfer the data to DB2/400 database table.

The table will have approximately  2 million records. and needs to be moved to Db2 darabase on a daily basis.

i have created a linked server, from the SQL server to db2 and try to insert the data. 

to insert 150 K records its taking 1 hr. 

is there any way to transfer the data from sql server to DB2 database in a much faster way.

The table definition is similar in two databases. all the columns are string types.

Is there a way to export a table as a file and import to Db2 database?

any help is appreciated.

Answer 1

Might want to look into

Datadirect Connect64

http://www.datadirect.com/products/other/ssis/index.html

---

Chuck

Answer 2

Typically, the data move is on a one by one record basis (sing the standard driver), this is why it is so slow, the SSIS OLEDB destination is a no exception, so no method will be any faster.

I suggest you use commercial components instead, e.g. CozyRoc's DB2 Destination http://www.cozyroc.com/ssis/db2-destination.

Here is how you connect to DB2 from SSIS: http://geekswithblogs.net/13DaysaWeek/archive/2010/05/16/connecting-to-db2-from-ssis.aspx in case you do not go the better route.

and then you need to wire the source to destination up in a Data Flow Task.

---

Arthur My Blog