Real Time Load

arjun_004 · Post by **arjun_004** » Wed Feb 01, 2012 6:08 am

Hi All,

We have the the requirement to load the Real time data from Sql Server to DB2 using Datastage 8.1

Any record inserted/deleted/updated in the sql server table needs to inserted in the DB2 table.

Kindly suggest any design/stages or approach.

ray.wurlod · Post by **ray.wurlod** » Wed Feb 01, 2012 3:01 pm

Can you capture the real time data from the same source as SQL Server is capturing it from? If you wait till it's in SQL Server it's not real time any more.

The next best thing is some kind of CDC approach, for example scraping the SQL Server transaction logs. Investigate IBM's CDC product and IBM's CDC stage in DataStage if you'd like to explore this route.

qt_ky · Post by **qt_ky** » Wed Feb 01, 2012 6:26 pm

Another possible approach is to enable (license) the Federation Server feature within DB2. It can give you DB2 nicknames (pointers) that when queried, updated, inserted, deleted, etc., via DB2, the execution passes through to SQL Server, Oracle, other databases, etc. To physically replicate the SQL Server into DB2 would require other replication products as Ray mentioned, or a development effort. For that purpose, I would recommend evaluating existing commercial products that have already been tested and proven to do the work for you.

arjun_004 · Post by **arjun_004** » Thu Feb 02, 2012 12:40 am

Please suggest the specific Product/version which can help in pulling the Real time Data from SQL server to DB2.

2 to 3 sec delay in getting the data is acceptable in my case.

Will this approach work if i try to execute the DS job multiple times in a loop. using ksh or loop stage is Datastage.We are expecting less then 100 records in the source.

qt_ky · Post by **qt_ky** » Thu Feb 02, 2012 9:09 pm

The companion product, InfoSphere Replication Server, is described in the Information Server Introduction PDF file, which is installed on your client or available online. It supports SQL Server. That's a product you could evaluate. However, if you're need is to replicate only one small table, then developing your own method is going to cost much less.

SURA · Post by **SURA** » Thu Feb 02, 2012 11:06 pm

Try it with trail version which will support upto 1000 rows. If you are happy then go with it. But it will be expensive.

DS User

cppwiz · Post by **cppwiz** » Tue Feb 07, 2012 4:47 pm

InfoSphere Change Data Capture can scrape the SQL Server transaction logs and replicate the data to DB2.

http://www-01.ibm.com/software/data/inf ... a-capture/

kwwilliams · Post by **kwwilliams** » Tue Feb 07, 2012 5:07 pm

Then publish to a queue where you can use the DTS stage to ensure that you are not losing any of the data in the process. If you are looking for 2-3 second latency you really don't have any other options than a always on job that reads either queue messages or services that supply the information. It will take longer than 2-3 seconds to start a DataStage job.