The GET DIAGNOSTICS statement is designed to be used in static SQL sessions; this is not indicated in the documentation. This document elaborates on when and how the GET DIAGNOSTICS statement can be used.
The GET DIAGNOSTICS is a compiled SQL statement that has currently been developed and implemented as a static SQL CALL to SQL procedure SYSCA.GET_DIAGNOSTICS. Because of that, it is doing some database access as opposed to (only) accessing SQL control blocks for diagnostic information about the status of the preceding SQL statement. This means it should be used in static SQL programs only, because it needs an Access Module (AM) at run-time. The AM, as well as the catalog which contains it, must be available to the program at execution time. If, for whatever reason, you cannot execute this call to SYSCA.GET_DIAGNOSTICS at run-time (no access module, catalog offline, etc), then you should not expect any meaningful results from this GET DIAGNOSTICS statement. The program will not abort; the results from the statement will simply be meaningless.
There are several reasons why the GET DIAGNOSTICS statement has been implemented in this "static" way: portability, i.e. not being dependent of the SQLCA structure; transfer of data; being more certain we are diagnosing the last executed SQL statement, i.e. doing it in a "dynamic" way includes several other SQL statements, etc. Implementing the statement in this way was a conscious choice, not an oversight. Therefore, if a user program has to be purely dynamic, the GET DIAGNOSTICS statement currently cannot be used and one should diagnose existing problems with the SQL communication fields that are available, e.g. SQLSTATE, SQLCODE, SQLCERC, etc.
Also, it cannot be used to diagnose on a CONNECT statement; it can only be successfully used after a CONNECT has already initiated and established an SQL session.
The GET DIAGNOSTICS can return a number of optional data items which describe the statement being reported on, including the command which was issued; the function code for that command; the dynamic function and command code (for a dynamic SQL statement); the number of result sets (if the command invoked an SQL procedure); and a row count of the number of rows returned. If the statement resulted in conditions (also called exceptions) then a parameter called MORE will be set to Y and another parameter called NUMBER will be set to the number of conditions being returned. THE GET DIAGNOSTICS CONDITIONS form of the statement can then be used to return the various exceptions that were encountered.
Within a static SQL program, after a successful CONNECT has established the SQL session, then you can anticipate that the GET DIAGNOSTICS statement will return information such as in the following examples. This type of diagnosis and display may help debug any logical problem in the SQL code, even when no errors are reported. For a statement which executes successfully, such as a PREPARE in this example, you might see:
Function: PREPARE Function Code: 00000020 Dynamic Function: SELECT Dynamic Function Code: 000000025 Row Count: 000000000 More: N Number Diag Messages: 000000000
For a statement which causes an exception at run-time, you might see similar results, except that the number of diagnostic messages is set to 000000001. By testing the returned value of that parameter, the program might then choose to use the GET DIAGNOSTICS CONDITIONS statement to retrieve and display the error message that was encountered, or other parameters. For example, when a program uses an embedded SQL statement without a WHERE clause, which could return more than one row to the program, a simple GET DIAGNOSTICS would result in the return of the following:
Function: SELECT Function Code: 00000025 Dynamic Function: SELECT Dynamic Function Code: 000000025 More: Y Row Count: 000000001 Number Diag Messages: 000000001
Since the number of diagnostic messages is greater than 0, the program could then use the GET DIAGNOSTIC CONDITION form of the statement to retrieve the error message which was issued, which the program could display or query internally, which in this case was:
DB001051 T229 C-4M321: Cardinality violation
This can be verified by displaying the more common error reporting fields for this statement, which in this situation show:
SQLSTATE: "21000" SQLCODE: 000000004- SQLCERC: 000001051