Datacom

- our job has been running successfully for a few weeks now but today we received an abend. 

the ideal program has not been changed

the trigger has not been changed

the datacom table has not been changed

Has someone else had this problem using triggers?

What does the triggered procedure do? Is it inserting rows into another table? If so, perhaps that insert is failing with a return code 07 or 08? Or maybe that other table is inaccessible? There can be many reasons. As per the documentation...

If a navigational application command (that is, a record-at-a-time command, not an SQL statement) triggers an SQL [procedure], and that [procedure] generates an error, a return code 94(100) is received. This return code is generated to make it clear that a trigger failure caused the current record-at-a-time request to fail. For example, a 94(100) could prevent confusion in a situation where, if the triggered procedure performs an INSERT, a DELET command could cause a return code 07 DATA AREA FULL error. The actual return codes produced by the trigger itself may be found in the Request Area, starting at DBRQDIAG (located 26 bytes (decimal) into the user request area), as follows: the 4-byte SQLCODE is followed by the external 2-byte return code, followed by the internal 1-byte return code, followed by the 5-byte SQLSTATE. Note: The Statistics and Diagnostics Area (PXX) contains more detailed information about the SQL error (search for the SQLCODE or SQLSTATE).

How do I look at the Request Area, starting at DBRQDIAG ?

Since it is a CA Ideal application, your error procedure possibly does not have access to that part of the request area. (access to that information would perhaps be a good suggestion for an "Idea" submission).

Is your MUF startup coded with the SYSOUT parameter (e.g. SYSOUT     0,5M,1M,3M,1M,1M)? If so then the PXXSQLnn SYSOUT should have a "-- DATA BASE SQL ERROR SUMMARY" that corresponds to the date/time when your application failed. As stated previously, the SQLCODE or SQLSTATE should provide the details of why the triggered procedure failed.

If there is no corresponding "-- DATA BASE SQL ERROR SUMMARY" in PXXSQL01, then there should be a Master List dump in PXXMLnn SYSOUT. Search for the DBRW---n (where n is the task number that appears in the dump header for the RC 94(100)) and then go to offset 26 within that request area to view the DBRQDIAG values.

If your MUF is not coded with the SYSOUT parameter (or for z/VSE sites) both the SQL Error summary and the dump will be in the PXX.

If the dump has been suppressed, you can use the RCERROR YES,NO,94100 console command to MUF in order to unsuppress it, and then rerun your application to produce the dump.

Alternatively you could just eyeball the triggered SQL procedure code and check the state of the tables it accesses. Often that shows up obvious conditions (such as table full, Index full, or database disabled) without the need for dump analysis.

TEC1857557 created from this question.