Coordinate Unit of Work

CA4T · Post by **CA4T** » Mon Jul 14, 2014 3:33 pm

Can DataStage coordinate a unit of work that exists on mainframe and open systems?

Here is what I am trying to ask... Let's say you have a job that is going to remove records from a mainframe DB2 table and insert them onto an open source Greenplum table. So you might have a job design that does something like the following:
1. Selects a record from a DB2 table
2. Deletes that record from the DB2 table
3. Insert that record into a Greenplum table

But, the insert to the open systems greenplum fails... and I want to rollback the delete to the mainframe DB2 -- would I have to code for this? Can I make these the same unit of work and have DataStage not commit until all parts are successful.

Any ideas?

chulett · Post by **chulett** » Mon Jul 14, 2014 3:41 pm

Have you checked out the Distributed Transaction stage? I believe it can handle that situation.

qt_ky · Post by **qt_ky** » Mon Jul 14, 2014 8:17 pm

It should handle that except that the version 11.3 connectivity guide for the Distributed Transaction stage indicates support equals Yes for DB2, Oracle, and MQ Connector stages and support equals No for ODBC and Teradata Connector stages. I haven't tested it myself. Sometimes the documentation is not current. Test it to see if it will work and/or check with IBM. It sounds like you need the Distributed Transaction stage to support the ODBC Connector stage.