The Get Diagnostics statement provides diagnostic information about the last SQL statement (other than a Get Diagnostics statement) that was executed.

Example:

Exec Sql Update Cusmas set City = ‘CHICAGO’ where CusNbr = 1;

Exec Sql Get Diagnostics Condition 1

:MsgText = MESSAGE_TEXT;

 

If the update statement was successful then MsgText will have something like this

“1 rows updated in CUSMAS in TESTLIB”

If the update statement could not find a record to be updated, you may get

“Row not found for update”

Condition 1 defines that we want the information from the most recent SQL statement that was executed.

 

To get the number of rows updated by the update statement

Sql Get Diagnostics Condition 1

:RowsCount = ROW_COUNT;

 

We can get other info from the SQL diagnostics like below:

Sql Get Diagnostics Condition 1

:RtnSqlCode = DB2_RETURNED_SQLCODE,

:RtnSqlState = RETURNED_SQLSTATE,

:MsgLen        = MESSAGE_LENGTH,

:MsgId           = DB2_MESSAGE_ID;

 

More info at

https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/sqlref/src/tpc/db2z_sql_getdiagnostics.html#db2z_sql_getdiagnostics