data migration from SQL server to DB2

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

re: data migration from SQL server to DB2

"Dave" <adavi@comcast.net> wrote in message
news:9_Swb.306834$Tr4.966018@attbi_s03...[color=blue]
> Hi
>
> We need to migratedata for about 300 tables from MS SQL server database[/color]
to[color=blue]
> 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[/color]
them[color=blue]
> 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
>[/color]
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.

re: data migration from SQL server to DB2

"Mark A" <ma@switchboard.net> wrote in message
news:z9Twb.1402$sW1.53490@news.uswest.net...[color=blue]
> "Dave" <adavi@comcast.net> wrote in message
> news:9_Swb.306834$Tr4.966018@attbi_s03...[color=green]
> > Hi
> >
> > We need to migratedata for about 300 tables from MS SQL server database[/color]
> to[color=green]
> > 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[/color]
> them[color=green]
> > 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
> >[/color]
> 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[/color]
have[color=blue]
> data. Check the Command Reference for details.
>
>[/color]

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

re: data migration from SQL server to DB2

"Dave" <adavi@comcast.net> wrote in message
news:hwTwb.306983$Tr4.967143@attbi_s03...[color=blue]
>
> Thanks for quick response Mark.
> I am trying to copy from MS SQL server database to DB2 database (and[/color]
not[color=blue]
> DB2 to DB2)
> I though import & Load utilities of DB2 are meant for copying from one[/color]
DB2[color=blue]
> 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
>[/color]
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.

re: data migration from SQL server to DB2

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

re: data migration from SQL server to DB2

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

"Serge Rielau" <srielau@ca.eye-bee-m.com> wrote in message
news:bq18c7$7cv$2@hanover.torolab.ibm.com...[color=blue]
> 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
>[/color]

re: data migration from SQL server to DB2

Dave,

Have you looked at the identityoverride option for load?

Mark A wrote:
[color=blue]
> "Dave" <adavi@comcast.net> wrote in message
> news:9_Swb.306834$Tr4.966018@attbi_s03...
>[color=green]
>>Hi
>>
>>We need to migratedata for about 300 tables from MS SQL server database[/color]
>
> to
>[color=green]
>>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[/color]
>
> them
>[color=green]
>>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
>>[/color]
>
> 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.
>
>[/color]