Synchronizing/Replication between MS SQL & DB2

I do not know which is bestm but IIRC, SQL Server comes with DTS
Packages that should make this pretty easy.

B.

Brian Tkatch Wrote:[color=blue]
> I do not know which is bestm but IIRC, SQL Server comes with DTS
> Packages that should make this pretty easy.
>
> B.[/color]
Thanks for your reply. But I'm not sure how to do this with a DTS. Is
it not impossible to connect to DB2 (non-Windows) from MS SQL Server?

I was thinking to retrieve all changed records (per table), put them in
a delimited file and then use the db2import command tool (with
'INSERT_UPDATE' as a parameter) for each file to 'synchronize' the DB2
database. The only thing I don't know is how to specify records that
must be deleted (other than using a regular SQL statement).

I also must consider buying a third software application to do this. I
must check which is better to maintain, which is cheaper, which is
best, ...


--
Tjeez
Message posted via http://www.exforsys.com for all your training needs.

> But I'm not sure how to do this with a DTS.

Create an ODBC connection to DB2, then in DTS add the object for each
database and use an action run a SQL statement. Or something like that,
i haven't done it in a while.
[color=blue]
>Is it not impossible to connect to DB2 (non-Windows) from MS SQL Server?[/color]

Isn't that what ODBC is for?
[color=blue]
>I was thinking to retrieve all changed records (per table), put them in
>a delimited file and then use the db2import command tool (with
>'INSERT_UPDATE' as a parameter) for each file to 'synchronize' the DB2
>database. The only thing I don't know is how to specify records that
>must be deleted (other than using a regular SQL statement).[/color]

Hmm.. good question. I don't know the best way.

B.

You can use DB2 built-in replication software, but that would need an
additional license for Information Integrator.

Then DB2 would take care of the changes and apply and purge them. It
would accomplish that using so called 'change data'-tables. Those would
be filled asynchronously reading the log files in DB2 and using
triggers in SQL server.

I dunno MS solutions - I am sure there is something on this side as
well - to accomplish that or any other third-party product.

If you write it on your own, you have to take care of all that
yourself. Whether that is useful depends very much on the complexity of
the data you want to replicate; and whether you need too keep
informations about transactions (e.g. OLTP) or just want to copy some
data. For loading some data into a (small) DWH an own solution using
triggers or just coying data might be cheaper.

Thanks for your replies! I will take a look at both DTS and Information
Integrator.


--
Tjeez
Message posted via http://www.exforsys.com for all your training needs.

You might also want to take a look at StarQuest Data Replicator,
another excellent replication tool for DB2 & MS SQL!

Details here: http://www.starquest.com/Productfolder/infoSQDR.html

Bob

Tjeez wrote:[color=blue]
> Thanks for your replies! I will take a look at both DTS and Information
> Integrator.
>
>
> --
> Tjeez
> Message posted via http://www.exforsys.com for all your training needs.[/color]