Re: Optimization of data migration

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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C1AAE534-769F-4841-AF31-93898DA5CAD4@xxxxxxxxxxxxxxxx
> 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:
>
>> 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/dataware/migrate/index_4.htm
>>
>>
>>
>>
>>
>>
>> "fleo" <fleo@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:80BB64EE-83F3-4D66-B7AF-614FF4EE80D3@xxxxxxxxxxxxxxxx
>> > 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:
>> >
>> >> 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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> >> news:62DC12EE-AC4C-4543-905A-F16257FB6C6D@xxxxxxxxxxxxxxxx
>> >> > "Allan Mitchell" wrote:
>> >> >
>> >> >> 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)
>> >> > Thanks Allan.
>> >> >
>> >> > I found papers on migrating from Oracle, MySQL, ... but nothing on
>> >> > DB2??
>> >> > (http://www.microsoft.com/technet/interopmigration/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?
>> >>
>> >>
>> >>
>>
>>
>>


.

Relevant Pages

  • Re: attaching DAT files
    ... use DUMP and LOAD, go for that. ... DUMP DATABASE ... > After REFIT stop and start SQL server service. ...
    (microsoft.public.sqlserver.connect)
  • Re: attaching DAT files
    ... use DUMP and LOAD, go for that. ... DUMP DATABASE ... > After REFIT stop and start SQL server service. ...
    (microsoft.public.sqlserver.server)
  • Re: attaching DAT files
    ... use DUMP and LOAD, go for that. ... DUMP DATABASE ... > After REFIT stop and start SQL server service. ...
    (microsoft.public.sqlserver)
  • Re: Import CSV and update existing conflicting existing record(s)
    ... "staging" table first. ... I compare and load it to the destination table. ... In DTS (SQL Server 2000) this would be done using a data pump task to ... and potentially fix data that fails the validation steps. ...
    (microsoft.public.sqlserver.dts)
  • Re: any tool which can tell the load of each database running on a SQL
    ... Did you mean Load in terms of User connections for each databases, ... This will give you the amount of users in each database. ... SQL Server MVP ...
    (microsoft.public.sqlserver.tools)
Loading