Solved

How to convert  a DB2 database to MS SQL server database ?

Posted on 2007-12-06
13
2,423 Views
Last Modified: 2011-09-20
Hello,
I have some DB2 tables and I want to migrate the table to MS SQL server? Is there an easy to do this?

Thanks so much for your suggestion,
Regards,
lilyyan
0
Comment
Question by:lilyyan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 
LVL 45

Accepted Solution

by:
Kent Olsen earned 80 total points
ID: 20420328
Hi lilyyan,

Well, for my money, such a migration is a mistake.  :)  But that's not the question, is it?    ;)

I don't know of any tools specifically designed to migrate DB2 to SQL Server.  IBM certainly doesn't have tools to help people LEAVE their platforms, just as Oracle and Microsoft don't make it easy to leave their systems.

That said, there are a lot of tools out there to generate the base SQL to generate the tables, indexes, views, and related objects.  The DB2LOOK utility can do this for you.  The resulting SQL will have to be edited slightly where proprietary extensions are used.  Things like IDENTITY columns.  The SQL Server syntax is different.

Move the data is a different issue.  You should be able to dump the data from DB2 as a CSV or tab delimited file, then load it into SQL Server.  If your data volume is modest, you may be able to use ACCESS to link to both databases, generate the tables in SQL Server, and move the data.



Good Luck,
Kent
0
 

Author Comment

by:lilyyan
ID: 20420647
HI Kent,

Thanks for your reply. so the basic steps for the data migration is to: use DB2LOOK to generate SQL statment, then generate a CSV file and trnsfer to MS SQL server?
lilyyan
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 20420662
what version of sql server do you run ?
you can try to do it using DTS (in sql 2000) or SSIS (in sql 2005)
these tools should make the movement of data rather simple,
i believe you will also be able to create the destination tables in sql server using these tools and you won't have to spend time on converting db2's syntax to sql server's
0
Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

 

Author Comment

by:lilyyan
ID: 20420755
HI Momi,

Thanks for your reply. I'm using sql 2000. could you explain a little more about DTS ? How to use it?
lilyyan


0
 
LVL 17

Assisted Solution

by:pssandhu
pssandhu earned 80 total points
ID: 20421205
Hey:
Check this link out. http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/dtssql2k.mspx

It will give you an idea on how it works. You will have to do some reading since it is not easy to write the whole thing out.

P.
0
 
LVL 13

Assisted Solution

by:ghp7000
ghp7000 earned 80 total points
ID: 20430725
headache job, all kinds of got yas can be involved, depending upon the complexity of the data and the tables. Can u explain whatyou hope to accomplish by moving the db to sql server?
0
 

Author Comment

by:lilyyan
ID: 20442879
Hi, thanks for the replies.  I want the table and the data to be moved to MS sql server. I do find the DTS tool in ms sql Entreprise Managar. not sure how to ue it yet. any suggestion? Thanks very much!
0
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 80 total points
ID: 20442961
you need to map the source db2 tables to sources using odbc driver
then you need to define the target tables in ms sql using either type of connectivity  you want (odbc / native etc)
and then map the column to one another
you can try to use the wizards there, they are very simple and self explanatory
0
 

Author Comment

by:lilyyan
ID: 20445094
Hi, thanks much for your reply.

how to map the source db2 tables to sources using odbc driver? did mean register a db2 server.?
(my pc has db2 management and development tools installed)

0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 20450569
inside your sql server client, you should create a new dts package
and follow the wizards
you don't use db2 tools here
0
 

Assisted Solution

by:MarkGho
MarkGho earned 80 total points
ID: 21265072
IBM purchased a company called Datamirror.  It allows real time conversion of DB2 to SQL.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
update db2 V 8.01 table but get time out error 68 33 237
Connect SQL Developer to zO/S DB2 database 6 381
iSeries DB2 Query 2 101
Configuration Assistant in DB2 10.1 3 125
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question