My initial position:
I've got two databases (DB2 8.1 and SQL Server 2000 SP3a) on two
different win2k systems.
The OLTP system is based on DB2 8.1 and the DW on SQL Server 2000.

My job is now, to realize a high performance periodic data migration
from the OLTP to the DW which could be started manual or automatic.
It twangs simple, but for a newbie like me, it is quite difficult.
The migration related to 12 tables, whereof 8 tables should be deleted
on (relational) DW site and completely inserted without any
aggregation from the OLTP site.
The only challenge of these 8 tables is, the data conversion from
different data types which is solved by the "convert(...).." -
statement (from SQL Server).

My current solution for these tables is:

1. Step
=> Deleting all data from DW table
2. Step (OnSuccess)
=> "Transform Data Task" to copy the data from OLTP to a temporary
table on SQL - Server site (temp - table has equivalent data types
like OLTP)
3. Step (OnSuccess)
=> "Execute SQL Task" to copy and convert the data from temp table to
the DW
4. Step (OnSuccess)
=> Deleting all data from temporary table

The problem is, that i don't wanna use the "Transform Data Task" and
would like to solve this situation within 2 steps (without a temporary
table). I also don't wanna use Active X - Skripts because, they should
be "slow" and i think, it have to exist an simpler solution for this
case. (Because, it is a common problem regarding to migration of
heterogenous data and data sources)

The optimized concept i would like to realize is:

1. Step
=> Deleting all data from DW table
2. Step (OnSuccess)
=> "Execute SQL Task" to copy and convert the data from OLTP to the DW
(like: insert into DW.TAB1 select * from OLTP.TAB1 => problem, that i
can only refer directly to one OLE DB Provider and the OLE DB2 -
Provider don't know the convert - command)

To use both, the source (on DB2) and the destination(on SQL Server) in
one "Execute SQL Task", i have limited 2 different options.
1. Work with OPENROWSET
2. Work with a linked server ( -> OPENQUERY)

I have tried it with these solutions, but beside of error messages, i
haven't see any positive result. I've got especially problems with the
@provstr - Parameter of the sp_addlinkedserver - Command, because i
don't know, what i should write in it.

My questions are now:
How should i procede?
Are there other possibilities, which are more simple?

It would be fine, if someone could help me.

CU

Alex
(Student of business informatics)

Alex,

I recommend a third-party solution designed to replicate tables
between DB2 & SQL Server, for example StarQuest Data Replicator
(http://www.starquest.com/Productfolder/infoSQDR.html). Probably much
easier to maintain (especially with new version of each database).

Good luck,

Bob


alexander_sperber (AT) gmx (DOT) de (Alex Sperber) wrote in message news:<aab277b1.0405030509.83e8d71 (AT) posting (DOT) google.com>...

db2team (AT) hotmail (DOT) com (DB2) wrote in message news:<a78ec628.0405050948.72d82daa (AT) posting (DOT) google.com>...
Hy Bob,
thanks for your answer, but meanwhile I solved the problem with help
of the Microsoft OLE ODBC - Driver. He runs without problems and the
performance is also right.