Realtime data integration using replication server and DS

dhiraj · Post by **dhiraj** » Sun Jan 03, 2010 3:32 pm

Hi,
we are in need to build a real time data warehouse, there are reporting requirments that need real time data (near real time would not do). The source system is on DB2 on mainframe. The existing environment captures changes occuring to the source database in real time using replication server for z/OS and applies them to other downstream systems.

The data integration platform used at this location is Datastage EE 7.5.2 on UNIX.

I am new to Real time data integration and the Replication server product. was wondering what would my options be to integrate the data changes occuring in the source (DB2 on mainframe) in realtime to the data warehouse (DB2 on UNIX). We would want to leverage the existing Replication server infrastructure to the extent possible.

1) Is it possible to interface replication server on z/OS with Datastage on UNIX, such that replication server can detect changes and push the changes to the data stage jobs to perform transformation before loading it to the data warehouse in real time? If so how? what are our options/workarounds?

2) I have been reading that infoshphere change data capture can help capture real time data changes and can interface with data stage. Does change data capture work with Datastage EE 7.5.2? and can it be used to capture changes occuring in DB2 on z/OS? Are there any work arounds/options?

3) Do we have any other alternatives to perform this realtime data integration using datastage?

Thanks
Dhiraj

ray.wurlod · Post by **ray.wurlod** » Sun Jan 03, 2010 6:56 pm

You can have "always running" DataStage jobs that receive the pushed data. Get onto the latest, "wave aware", version.

dhiraj · Post by **dhiraj** » Mon Jan 04, 2010 10:21 am

Hi,
Could you please eloborate? Are you saying datastage can integrate with replication server?

Thanks
Dhiraj
P.S. we are using datastage 7.5.2 on UNIX and replication server for z/OS

JRodriguez · Post by **JRodriguez** » Mon Jan 04, 2010 11:31 am

Dhiraj,

Replication server uses WebSphere MQ message queues to transmit transactions between source and target databases ... so in my humble opinion ... MQ message Broker (a development tool) will be a better fit for this task

In case that you should go with datastage, you still can design a solution using MQ stages. You can define jobs as "always on" using MQ or you can use a feature from MQ called trigger, every time a wave of messages show up in a queue - your delta records from replication server - you can trigger a sequence or a job in datastage

eostic · Post by **eostic** » Mon Jan 04, 2010 8:49 pm

The newer InfoSphere Changed Data Capture offerings pull a lot of this together nicely nowadays....prior there are many CDC options....Rep Server...Queue Rep....etc, and many of these are integrated directly to DataStage thru a custom Stage type, or at least easily by way of .csv or alternatively, xml definition for the messaging payload..... I can't confirm, but always thought that prior releases of Rep Server went direct without using MQ or other messaging protocol.... that would mean [one possible approach] having a DS job that reads the CCD (if my memory serves me correctly) table and then keeps its own record of which row it last read..... when complete, the job just starts up another instance of itself and the process runs continually.....

Ernie