DataStage Parallel job: Retrieve sql codes on a failed upsert

When an enterprise database stage such as DB2 or Oracle is set to upsert it is possible to create a reject link to trap rows that fail any update or insert statements. By default this reject link holds just the columns written to the stage, they do not show any columns indicating why the row was rejected and often no warnings or error messages appear in the job log.

Steps

There is an undocumented feature in the DB2 and Oracle enterprise stage where a reject link out of the stage will carry two new fields, sqlstate and sqlcode. These hold the return codes from the RDBMS engine for failed upsert transactions. The fields are called sqlstate and sqlcode.
To see these values add a peek to your reject link, the sqlstate and sqlcode should turn up for each rejected row in the job log. To trap these values add a copy stage to your reject link, add sqlstate and sqlcode to the list of output columns, on the output columns tab check the "Runtime column propagation" check box, this will turn your two new columns from invalid red columns to black and let your job compile. If you do not see this check box use the Administrator tool to turn on column propagation for your project.
When the job runs and a RDBMS reject occurs the record is sent down the reject link, two new columns are propagated down that link and are defined by the copy stage and can then be written out to an error handling table or file.
If you do not want to turn on column propagation for your project you can still define the two new columns with a Modify stage by creating them in two specifications. sqlcode=sqlcode and sqlstate=sqlstate. Despite column propagation being turned off the Modify stage will still find the two columns on the input link and use the specification to add them to the output schema.

Examples

Oracle By default, oraupsert produces no output data set. By using the -reject option, you can specify an optional output data set containing the records that fail to be inserted or updated. It’s syntax is: -reject filename For a failed insert record, these sqlcodes cause the record to be transferred to your reject dataset: -1400: cannot insert NULL -1401: inserted value too large for column -1438: value larger than specified precision allows for this column -1480: trailing null missing from string bind value
For a failed update record, these sqlcodes cause the record to be transferred to your reject dataset: -1: unique constraint violation -1401: inserted value too large for column -1403: update record not found -1407: cannot update to null -1438: value larger than specified precision allows for this column -1480: trailing null missing from string bind value An insert record that fails because of a unique constraint violation (sqlcode of -1) is used for updating.
DB2 When you specify the -reject option, any update record that receives a status of SQL_PARAM_ERROR is written to your reject data set. It’s syntax is: -reject filename

Conclusion

Always place a reject link on a Database stage that performs an upsert. There is no other way to trap rows that fail that upsert statement.
For other database actions such as load or import a different method of trapping rejects and messages is required.