GET DIAGNOSTICS statement details

Products

• IDMS
• IDMS - Database

Issue/Introduction

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.

Environment

Release: All supported releases. 
Component: SQL Option.

Resolution

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, this call to SYSCA.GET_DIAGNOSTICS cannot be executed at run-time (no access module, catalog offline, etc), then no meaningful results can be expected 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, similar results may be seen, 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