More detailed error messages?

[KeithM]

I have a simple job that reads from one table and writes to another table. Both tables are SQL Server tables and I am using an ODBC stage to make the connection. Some of the records are failing to insert but when I view the log the only error messages are SQLExecute failed ... 0 rows affected. I run the same insert through Query Analyzer and I get a more detailed message telling me what is actually wrong.

Is there a setting or another log that I can view using DataStage that I can see more detailed messages that tell me what is actually wrong when a statement fails?

[ray.wurlod]

You can capture this information in the following manner. Create a job design that looks like this.

Code: Select all

                               out
  ODBC   ---->  Transformer  ----->  ODBC
                       |
                       | [Reject]
                       |
                   SeqFile

The link called out tries to affect the target table. If this fails, the row is deemed not to have been processed on the link called out, and so is eligible to be processed as a rejected link.
On the rejects link, you can get access to link variables from the link called out. These include:

• out.SQLSTATE (generic ODBC error code)
  out.DBMSCODE (error code from SQL Server)
  out.LASTERR (error message from SQL Server)

Target of the rejects link is typically a text file, since there are fewer conditions that would prevent writing to a text file than to a database table.