Configuring Error Handling in Write to DB

The Write to DB stage has an error port which allows you to filter out records that cause database errors when writing the record to a database, such as a primary key constraint violation or a unique constraint violation. These records can then be routed along another path in the dataflow while other records are successfully committed. For example, if you are processing 100 records and records 4, 23, and 56 cause a database error, these three records would be routed through the error port while the other 97 records would be committed to the database.

Note: Using the error port is optional. If you do not use the error port, the job will fail if any record causes an error.

From the palette, choose the type stage you want to handle error records (for example, Write to File) and drag it onto the canvas. You have a couple options for selecting a stage:
- To write failed records to a file, drag one of the following onto the canvas: Write to File, Write to XML, or Write to Variable Format File,.
- To simply discard failed records, drag Write to Null onto the canvas.
Connect the error port on Write to DB to the stage you want to handle failed records.
The following example shows the error port on Write to DB connected to a Write to File stage. In this example, records that cause an error when written to the database are instead written to the file specified in the Write to File stage.

When you run the dataflow, records that cause an error are routed through the error port. The records from the error port contain the fields specified in Write to DB plus the following fields:

Error.code: This field contains the numeric error code returned from the database. For example, given the error ORA-00001: unique constraint ANKUSH.SYS_C0010018) violated, the value in the Error.code field would be 1. See your database software's documentation for a listing of error codes.
Error.Message: This field contains the error message returned from the database. For example: ORA-01034 ORACLE not available. In this case, ORACLE not available would be the value in the Error.Message field. See your database software's documentation for a listing of error messages.
Error.SQLState: This field contains the SQLSTATE code which provides detailed information about the cause of the error. For a listing of SQLSTATE codes, see your database software's documentation.
Timestamp: The date and time on the Spectrum Technology Platform server when the error occurred.
Username: The name of the Spectrum Technology Platform user that ran the dataflow.