Unanswered: Moving SQL Server data to DB2 on Linux.
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.
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.