Ways to check the execution of SQL statements
Db2 offers several ways to check the execution of SQL statements in an program.
A program that includes SQL statements can have an area that is set apart for communication with Db2—an SQL communication area (SQLCA). When Db2 processes an SQL statement in your program, it places return codes in the SQLSTATE and SQLCODE host variables or in corresponding fields of the SQLCA. The return codes indicate whether the statement executed successfully or failed.
Recommendation: Because the SQLCA is
a valuable problem-diagnosis tool, include the necessary instructions
to display some of the information that is in the SQLCA in your application
programs.
You can use a GET DIAGNOSTICS statement or a WHENEVER statement in your program to supplement checking SQLCA fields after each SQL statement runs.
- The GET DIAGNOSTICS statement returns diagnostic information about the last SQL statement that was executed. You can request specific types of diagnostic information or all available diagnostic information about a statement. For example, the GET DIAGNOSTICS statement returns the number of rows that are affected by a data insert, update, or delete.
- The WHENEVER statement allows you to specify what to do if a general condition is true. Db2 checks the SQLCA and continues processing your program. If an error, exception, or warning results when an SQL statement is executed, Db2 branches to another area in your program. The program can then examine the SQLSTATE or SQLCODE to react specifically to the error or exception.