Capturing rejected rows from DB2/UDB API Stage

[cnguyen]

We are having problem capturing rows that are not loaded to the DB2 mainframe using the DB2/UDB API stage in a PX job. Here is the basic job design.

Seq ---> Transform ---> DB2/UDB API
|
V
Seq2

In the transform, we added an "OTHERWISE" contraint and chhecked the "Otherwise/Log" box. The data did not get loaded into the DB2 database because of some date format. However, the rejected rows are not written to the Seq2 file.

We have done the same design in a server job and the rejected rows are captured. We would like to achieve the same thing with a PX job using the same DB2/UDB API stage but have not been successful.

We cannot use the DB2/UDB Enterprise stage as our DB2 database is on a mainframe. Has anyone been successful with this using the DB2/UDB API stage on PX jobs? Any input would be greatly appreciated. Opened a case with Technical Support but have not received any feedback yet. Also done extensive searching in this forum but to no lead so far.

Thanks

[ray.wurlod]

The problem here is that (as you have drawn the diagram) the Seq2 file is populated from the Seq1 stage (rows that do not match the metadata). I feel that that's not what you've really designed - try wrapping your ASCII art in Code tags to preserve its formatting.

You believe that parallel and server jobs operate the same way in handling database rejects. This is not the case - rows rejected by the database are not directed onto the Otherwise/Log link. This link handles rows that were not output onto any of the prior-executed output links. Examine the source code generated by the server and parallel jobs and identify the differences - the server Transformer stage actually reacts to the return value of the generic Put() function (whereas the parallel one does not).

Assuming you are using the Upsert write mode, you need to define an output link from the DB2 Enterprise stage (to Seq3 file?) and set the Output Rejects property to True. This link will be marked automatically as a Rejects link.

For more information inspect the Help topic DB2/UDB Enterprise Stage - Inputs Page Properties Tab

I am guessing that a similar mechanism is required for the DB2 API stage, but will make no promises, as I have not tried it. And it doesn't have its own help pages or manual entry.