473,320 Members | 1,921 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

Nov 12 '05 #1
6 7625
"Dave" <ad***@comcast.net> wrote in message
news:9_Swb.306834$Tr4.966018@attbi_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.
Nov 12 '05 #2

"Mark A" <ma@switchboard.net> wrote in message
news:z9******************@news.uswest.net...
"Dave" <ad***@comcast.net> wrote in message
news:9_Swb.306834$Tr4.966018@attbi_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


Nov 12 '05 #3
"Dave" <ad***@comcast.net> wrote in message
news:hwTwb.306983$Tr4.967143@attbi_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.
Nov 12 '05 #4
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

Nov 12 '05 #5
Thanks Serge & Mark....Let me try the options that you have suggested.

"Serge Rielau" <sr*****@ca.eye-bee-m.com> wrote in message
news:bq**********@hanover.torolab.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

Nov 12 '05 #6
Dave,

Have you looked at the identityoverride option for load?

Mark A wrote:
"Dave" <ad***@comcast.net> wrote in message
news:9_Swb.306834$Tr4.966018@attbi_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.


Nov 12 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Tom Loach | last post by:
I have user that we just migrated his Access database to SQL Server. All went well with the migration, but then he came up with another requirement to be able to replicate the database to a local...
4
by: serge | last post by:
I am doing a test on migrating an Oracle 8i database to SQL 2000. I've never done this before and I would like to find out if there are any complications or side effects if I try doing the...
7
by: Dave | last post by:
We are trying to migrate a MS SQL server app to DB2 8.1 Linux platform. Our database has got about 300+tables with total size - 150 GB We are using MS SQL's BCP utility to extract data from...
1
by: rob | last post by:
Dear All, I have a very small test project to convert asp to asp.net using the Microsoft/Artisan ASP to ASP.NET Migration Assistant...
1
by: pankaj_wolfhunter | last post by:
Greeting, Can anyone tell me the points to keep in mind while migrating data from Sybase to DB2 especially in case of LOB data. I know the books available on the IBM site, but if still someone can...
2
by: contact1981 | last post by:
Hello, I am trying to migrate data from a DB2 database to SQL Server 2005 database. Does anyone know about any migration tool that does that? I have heard about DB2 Migration Tool kit, but I...
11
by: Chad | last post by:
Hi Is it possible to substitute an alternative data source (eg MySQL or SQL Server) into an existing MS-Access application?
2
by: TSrinivasan | last post by:
Hi All, I am doing data migration from SQL Server to Oracle using TOAD for the first time. I export data from SQL Server to a notepad file. I import data using TOAD from that notepad...
1
by: giff | last post by:
Hi We had a recent migration to a new server early this week (Tuesday). My database (and all the other files on the old server) were copied onto the new server. The old server is still up and...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.