I am working on a project in which I need to set up a daily process to move data from a SQL Server 2005 database at a remote site to a DB2 V9.1 on Linux database at our home office. The data that will be moved from SQL Server resides in several tables. I do not want to move all of the columns that are defined on each table. The destination tables that will be defined in DB2 will be denormalized and used for reporting. The data in DB2 will be totally refreshed each day.

I realize that there are several ways in which to do this including the use of SSIS and export/import. What I am trying to determine is the most efficient way because the data will be crossing our WAN.

If anyone has gone through this and figured out the best way to do it, please share that process with me.

Thank you for what ever help you can provide.

I would use bcp queryout to export the data and use db2 load utility to load the data into DB2 Database. This way you are only transfering the data, you want to load to DB2. Further you have the option to compress the file before the transfer.

Thank you for your suggestion.

I'll certainly give it a try to see how it works for us.

One last question concerning this issue.

Has anyone used the DB2 Migration Toolkit for this type of a situation or is that tool more suited to a one time migration of data from SQL Server to DB2?

Thanks.

Yes,

you could also use it for your purpose. It will generate scripts to export and load the data.
You can use the scripts for your replication tasks.

Cheers
nvk

Thanks for the information!! Keep up the good work guys!

Thanks for your input!