SQL Server 2000, SP4

We have a DB2 table where we want to extract data for only
those fields that equal 'N'. What is the optimal way to
set up DTS to move the data from a DB2 table to an Oracle
table, where date fields get transformed from DB2 date
type to Oracle date type, null date fields also get copied
over, some columns get trimmed of leading and ending
spaces, and for each row/record that comes over, DTS flips
the flag back at the DB2 table from 'N' to 'Y' indicating
the row has been processed?

More specifically, we've been tasked to develop a means to
move data from the AS/400 to Oracle and back again. Right
now we are working on moving data from DB2 to Oracle.
The 'N' flag in the DB2 table is to tell DTS that the data
is ready to process. DTS establishes a connection to the
databases, processes each record where the flag is set
to 'N', and also flips the flag after each record is
successfully moved over to 'Y'; that way if there is a
glitch somewhere, say the server goes down in mid
transfer, DTS can pick back up and only process the the
remaining records that have a value of 'N'.

[Allen, I combed through the messages and never did find
your response to an earlier message I sent. Perhaps I've
been the stupid user, but I'd appreciate if you would
address this message. Thanks :-)]

OK


The SourceSQLStatement in the DataPump task would have a WHERE clause for
you field = 'N'
If the differences between an Oracle and DB2 datatype reuire manipulation
then you can use an Active X Transform to do that i.e. check the range,
check for NULL etc etc.

Ideally I would have a sperate trigger table that captured in the Source the
fact that you had "Flipped the bit". If all records move successfully then
you can issue an update on the Source to the Real table where the PK values
match between trigger and real table.

If you leave the batch sizes as default then either all records move or none
at all.

You need a method of saying "I am moving these records so anything that
comes in after I have started will be processed in the next batch". This is
so that you do not reset the flag back t o'Y' for records that come into the
trigger table during the move but do not get moved this time. For this I
would use a BatchLoadControl table which enters the date as soon as you
start the process. You then update only those records with a trigger table
insert date of < that date.

Let me know if you have any Qs

--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Mary Scheffler" <Mary_Scheffler (AT) urscorp (DOT) com> wrote