Synchronizing/Replication between MS SQL & DB2

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tjeez - 23 Nov 2005 10:13 GMT

Hi folks,

I'm totally not familiar with DB2 databases, but for a project at wor
I need to synchronize a MS SQL database with a DB2 database. The MS SQ
server is (offcourse) on a Windows 2003 server, while the DB2 databas
is on a non-windows environment.

Now I wonder what is the best way to accomplis
synchronisation/replication? Is there a built-in tool to do this, wil
I need third party software, or will I need to write my ow
synchronization application (there is a log table which keeps track o
all changes made to any record)?

I know there is the db2 load or import command. Is it possible t
invoke that command from a C# application with the DB2 .Net Dat
Provider or any other data provider?

Thanks in advance

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

Reply to this Message

Brian Tkatch - 23 Nov 2005 14:39 GMT

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

B.

Reply to this Message

Tjeez - 23 Nov 2005 15:23 GMT

Brian Tkatch Wrote:

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

Thanks for your reply. But I'm not sure how to do this with a DTS. I
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 i
a delimited file and then use the db2import command tool (wit
'INSERT_UPDATE' as a parameter) for each file to 'synchronize' the DB
database. The only thing I don't know is how to specify records tha
must be deleted (other than using a regular SQL statement).

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

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

Reply to this Message

Brian Tkatch - 23 Nov 2005 16:41 GMT

> 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.

>Is it not impossible to connect to DB2 (non-Windows) from MS SQL Server?

Isn't that what ODBC is for?

>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).

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

B.

Reply to this Message

juliane26 - 23 Nov 2005 16:58 GMT

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.

Reply to this Message

Tjeez - 24 Nov 2005 07:50 GMT

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

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

Reply to this Message

db2team@hotmail.com - 24 Nov 2005 17:35 GMT

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

> 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.

Reply to this Message