In our project we have a requirement where we need to move part of data from db2 tables to sqlserver tables.we are using DSNUPROC and DSNTIAUL utility to unload data from db2 and using sql server export,import wizard to load data into the sql server 2005.
now the db2 has its native datatype like time stamp,etc which where not in sql server and also using DSNUPROC utility integer column is converted to Character(iam using external keyword or else data will be in compressed format which is not readable,and is not understandable by sql server ) .
now the issue is this character data i am loading as integer in sql server because of that record length is mismatching. and data loading is failing in sql server.any pointers regarding this will be highly helpful.
Joined: 23 Nov 2006 Posts: 19270 Location: Inside the Matrix
Please provide some detail information.
Show the data before the ftp/other download to the win-based system.
Show the unload control statements from db2.
You may need to insert a step between the unload and the data trqnsfer. You might also consider writing code to create the download file and then you can ensure all of the data is in windows-friendly formats.
From experience, if you create a simple text file with delimiters, the import into sqlserver becomes trivial.
Joined: 13 Jun 2007 Posts: 632 Location: Wisconsin
Just a different though, could you have a program (java?) that reads that data directly from DB2 on a Windows machine and writes it straight into SQL Server?
Not the ideal solution if you are moving lots of data often because you are doing row by row inserts after the reads, but since you would be manipulating the data in your code anyway to make it SQL server friendly, just throw the insert in also.