Hi, I might work on a DB2 to SQL Server data migration using DTS.
What should I keep in mind to optimize performance?
Thank you

A few things that run off the top of my head.

Make sure that you have the appropriate indexes on the Source to facilitate retrieval of the data to move,
When inserting to the destination removes indexes and triggers.
Move only the data you ctually need to move
Fast Disks
Fast CPUs + cache
As little distance as possible betwen Source And Destination (fast Network)



On Wed, 17 Aug 2005 06:29:04 -0700, "fleo" <fleo (AT) discussions (DOT) microsoft.com> wrote:

"Allan Mitchell" wrote:


I found papers on migrating from Oracle, MySQL, ... but nothing on DB2??
(http://www.microsoft.com/technet/int...ation/sql.mspx)

I am going to be migrating the data only (not the objects). I have seen
multiple options and I am not sure about the best way to go. Should I create
a staging database?

It could well be worthwhile having a staging area yes. This way your data
can go through a series of screens to clean things up and when it reaches
the end of the process is ready for insertion into the real destination.

do you need to do cleansing or is it a stright pick up and move?


Allan


"fleo" <fleo (AT) discussions (DOT) microsoft.com> wrote

Yes I need to perform cleaning/transformation.

All the info I found put the emphasis on migrating the database itself
rather than the data. Do you know where I could get information about
migrating the data? The pitfalls, the things I should check in the source
data, how to design the DTS to be able to go from developpement to test and
production phases, ...

Thank you

"Allan Mitchell" wrote:

To move the data you will need to understand the differences between DB2 and
SQL Server. The ranges stored in datatypes may be different, there may be
completely incompatable datatypes. The list that I gave you will help to
optimise the load. What it will not do is tell you the differences between
SQL Server and DB2. This article does it in reverse though

http://www.redbooks.ibm.com/tstudio/...te/index_4.htm






"fleo" <fleo (AT) discussions (DOT) microsoft.com> wrote

Many thanks for your help Alan!
I read the article and got puzzled by the "source physical file". Is there
something equivalent when migrating to SQL Server?

I am definitely not an expert on DTS so maybe I'm missing something and
being too simplistic. I had in mind to create an OLEDB connection to the DB2
database and copy the data "as is" in a staging database. Maybe I could
perform some sort of filtration at the same time??

Then I would transform/copy the data to the real database...

"Allan Mitchell" wrote:

Your idea is perfectly workable. You would load the data to a staging DB,
perform a number of screen upon the data and then present it to the outside
world by moving it into your real DB

Allan

"fleo" <fleo (AT) discussions (DOT) microsoft.com> wrote

There is also a nice too called StarQuest Data Replicator:
http://www.starquest.com/Productfolder/infoSQDR.html

Good luck,

Bob

Thank you for your help

"fleo" wrote: