data migration from SQL server to DB2

[Dave]

Hi

We need to migratedata for about 300 tables from MS SQL server database to
Linux DB2 V8.1.
Total size of all 300 tables is @ 3 GB.
We have converted tables DDL using IBM migration tool kit and created them
in DB2
but having tough time in transferring the data.
There are tons of identity colums (GENERATE ALWAYS AS IDENTITY) in these
tables.
Tried using DTS functionality of SQL server but we are getting too many
errors.
Any suggestions ?

Thanks in advance

[Mark A]

"Dave" <ad***@comcast. net> wrote in message
news:9_Swb.3068 34$Tr4.966018@a ttbi_s03...

Hi

We need to migratedata for about 300 tables from MS SQL server database to Linux DB2 V8.1.
Total size of all 300 tables is @ 3 GB.
We have converted tables DDL using IBM migration tool kit and created them in DB2
but having tough time in transferring the data.
There are tons of identity colums (GENERATE ALWAYS AS IDENTITY) in these
tables.
Tried using DTS functionality of SQL server but we are getting too many
errors.
Any suggestions ?

Thanks in advance

We probably need more specific information on the errors. However, in
general, you will have better luck with the import utility (rather than
load) when loading data with generated columns and the columns already have
data. Check the Command Reference for details.

[Dave]

"Mark A" <ma@switchboard .net> wrote in message
news:z9******** **********@news .uswest.net...

"Dave" <ad***@comcast. net> wrote in message
news:9_Swb.3068 34$Tr4.966018@a ttbi_s03...

Hi

We need to migratedata for about 300 tables from MS SQL server database to

Linux DB2 V8.1.
Total size of all 300 tables is @ 3 GB.
We have converted tables DDL using IBM migration tool kit and created

them

in DB2
but having tough time in transferring the data.
There are tons of identity colums (GENERATE ALWAYS AS IDENTITY) in these
tables.
Tried using DTS functionality of SQL server but we are getting too many
errors.
Any suggestions ?

Thanks in advance

We probably need more specific information on the errors. However, in
general, you will have better luck with the import utility (rather than
load) when loading data with generated columns and the columns already

have data. Check the Command Reference for details.

Thanks for quick response Mark.
I am trying to copy from MS SQL server database to DB2 database (and not
DB2 to DB2)
I though import & Load utilities of DB2 are meant for copying from one DB2
db to another DB2 db.
Here is one of the errors that we are getting while running DTS tool of
MS SQL server...
SQL0206N "<Column Name>" is not valid in the context where it is used.
SQLSTATE=42703
I guess my question is are there any good IBM tools or third party
tools that help us migrate the
data from SQL server to DB2 database ?

Thanks

[Mark A]

"Dave" <ad***@comcast. net> wrote in message
news:hwTwb.3069 83$Tr4.967143@a ttbi_s03...

Thanks for quick response Mark.
I am trying to copy from MS SQL server database to DB2 database (and not DB2 to DB2)
I though import & Load utilities of DB2 are meant for copying from one DB2 db to another DB2 db.
Here is one of the errors that we are getting while running DTS tool of
MS SQL server...
SQL0206N "<Column Name>" is not valid in the context where it is used.
SQLSTATE=42703
I guess my question is are there any good IBM tools or third party
tools that help us migrate the
data from SQL server to DB2 database ?

Thanks

If you can export the data from MS SQL in fixed length (positional) or
delimited format ("|" works well as a delimiter, but any delimiter can be
defined) into regular sequential files, then you can use the import utility.
The data does not have to come from DB2. Make sure that dates are in the
format of YYYY-MM-DD, YYYY/MM/DD, MM-DD-YYYY, etc.

For medium to large tables, make sure you set the commit interval to about
500-1000 so you will not fill up the logs. Check the Command Reference for
details.

The load utility can also be used, but may be a problem with generated
always columns.

I have found that tools such as DTS are very good for lightening your
wallet, but not much else.

[Serge Rielau]

Dave,

Have you downloaded the Migration Toolkit (free).
Is has function to move the data.
There is a migration link of the DB2 homepage.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Dave]

Thanks Serge & Mark....Let me try the options that you have suggested.

"Serge Rielau" <sr*****@ca.e ye-bee-m.com> wrote in message
news:bq******** **@hanover.toro lab.ibm.com...

Dave,

Have you downloaded the Migration Toolkit (free).
Is has function to move the data.
There is a migration link of the DB2 homepage.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Sean McKeough]

Dave,

Have you looked at the identityoverrid e option for load?

Mark A wrote:

"Dave" <ad***@comcast. net> wrote in message
news:9_Swb.3068 34$Tr4.966018@a ttbi_s03...

Hi

We need to migratedata for about 300 tables from MS SQL server database

to

Linux DB2 V8.1.
Total size of all 300 tables is @ 3 GB.
We have converted tables DDL using IBM migration tool kit and created

them

in DB2
but having tough time in transferring the data.
There are tons of identity colums (GENERATE ALWAYS AS IDENTITY) in these
tables.
Tried using DTS functionality of SQL server but we are getting too many
errors.
Any suggestions ?

Thanks in advance

We probably need more specific information on the errors. However, in
general, you will have better luck with the import utility (rather than
load) when loading data with generated columns and the columns already have
data. Check the Command Reference for details.