DSXchange

Hi,
we are using DS MQ Connector stage to read xml messages from Websphere MQ. Then using XML i/p stage, the message is split into individual xml elements and then inserted into Target DB2 table.
'Message Read Mode' read mode property is set to 'Delete Under Transacton'.
In case of Incorrect message structure(which xml i/p stage fails to interprete) or in case Job aborting due to some reason, the message read from the Queue is deleted from the Queue even though it is not written into Target table.
In this case, the message is lost forever and tracing it becomes impossible.
Is there any way where the messages are not deleted untill the job runs/ transaction completes successfully.?
Or is there some mechanism where the unsuccessful messages are caught in Reject file.?

Please provide your suggestions/ alternative solutions to this issue.

You are attempting a two-phase commit. Read from MQ and write to DB2. The ways that can not work but report success to the transaction are many. There is no way I would trust DataStage or my programming prowess to catch them all. Rather I would keep it simple. Browse from MQ write to DB2 and then go back and delete the message from MQ only if the DB2 write was a success. To me this means capturing the MQMsgId from the message and manually deleting the message after the DB2 write, either as a separate job or passing the MQMsgId down a link that contains a constraint that the DB2 link was successful. This way if anything happens the message will remain on the queue.

throbinson makes a good point. The technique outlined is the safest way to go, no matter what because you retain ultimate control. We're waiting for a new Distributed Transaction Stage, but it has only been made available in a limited set of sites and is for a unique set of circumstances where MQ can be configured as the Transaction coordinator. With Server's MQStage you can get "closer" to what you are looking for (http://dsrealtime.wordpress.com/2008/05 ... to-target/ ) but even then, the manual method will never go awry.

Ernie