GET DIAGNOSTICS

The GET DIAGNOSTICS statement extracts information on exception or completion conditions of the last executed SQL statement from the diagnostics area and returns it to the issuer. Use this statement in SQL that is embedded in a program.
idmscu19
The GET DIAGNOSTICS statement extracts information on exception or completion conditions of the last executed SQL statement from the diagnostics area and returns it to the issuer. Use this statement in SQL that is embedded in a program.
This article describes the following information:
2
2
Syntax
  ►─ GET DIAGNOSTICS ─┬─ 
statement-info
 ────────────────────────────┬───────────►◄                     │─ CONDITION ─┬─ 
condition-nr
 
condition-info
 ─┘                     └─ EXCEPTION ─┘  
Expansion of statement-info
     ┌────────────────────────────────── , ───────────────────────────────────┐ ►─ ▼ ─┬─ 
routine-parameter
 ───┬──── = ────┬─ COMMAND_FUNCTION ────────────┬─┴─►◄       ├─ 
host-variable
 ───────┤           ├─ COMMAND_FUNCTION_CODE ───────┤       └─ 
local-variable
 ──────┘           ├─ DYNAMIC_FUNCTION ────────────┤                                           ├─ DYNAMIC_FUNCTION_CODE ───────┤                                           ├─ IDMS_RETURNED_RESULT_SETS ───┤                                           ├─ MORE ────────────────────────┤                                           ├─ NUMBER ──────────────────────┤                                           ├─ ROW_COUNT ───────────────────┤                                           └─ IDMS_ROWID_INSERT ───────────┘  
Expansion of condition-info
     ┌────────────────────────────────── , ───────────────────────────────────┐ ►─ ▼ ─┬─ 
routine-parameter
 ───┬──── = ────┬─ IDMS_MESSAGE_COMMENTS ───────┬─┴─►◄       ├─ 
host-variable
 ───────┤           ├─ IDMS-MESSAGE_DEFINITION ─────┤       └─ 
local-variable
 ──────┘           ├─ IDMS_MESSAGE_ID ─────────────┤                                           ├─ IDMS_MODULE_NUMBER ──────────┤                                           ├─ IDMS_REASON_CODE ────────────┤                                           ├─ IDMS_SQLCODE ────────────────┤                                           ├─ IDMS_TASK_ID ────────────────┤                                           ├─ MESSAGE_LENGTH ──────────────┤                                           ├─ MESSAGE_TEXT ────────────────┤                                           └─ RETURNED_SQLSTATE ───────────┘  
Parameters
A
routine-parameter
,
host-variable
, or
local-variable
must be specified for each statement-info or condition-info item.
  • statement-info
    Identifies the type of statement information to be extracted and returned.
    Statement-info
    names that begin with 'IDMS_' are extensions to the SQL standard.
  • CONDITION
    Requests diagnostic information for a condition.
  • condition-nr
    Specifies the number of the completion or exception condition for which diagnostics information is being requested. An exception is raised if
    condition-nr
    does not refer to a valid condition number.
  • condition-info
    Identifies the type of condition-related information to be extracted and returned.
    Condition-info
    names that begin with 'IDMS_' are extensions to the SQL standard.
  • EXCEPTION
    Specifies a synonym for CONDITION. While it is part of the current SQL standard, its use is discouraged because it will not be in future SQL standards.
Parameters for Expansion of statement-info
  • routine-parameter
    Identifies an SQL routine parameter that is to receive the value of the specified diagnostics item.
    Routine-parameter
    must be a parameter of the current SQL routine and must be compatible for assignment with the specified diagnostic item.
    See Expansion of Routine-parameter for information about expanded syntax.
  • host-variable
    Identifies a host variable that is to receive the value of the specified diagnostics item.
    Host-variable
    must be a host variable previously declared in the application program and must be compatible for assignment with the specified diagnostic item.
    See Expansion of Host-variable for information about expanded syntax.
  • local-variable
    Identifies a local variable of an SQL routine that is to receive the value of the specified diagnostics item.
    Local-variable
    must be a local variable declared in the current SQL routine and must be compatible for assignment with the specified diagnostic item.
    See Expansion of Local-variable for information about expanded syntax.
  • COMMAND_FUNCTION
    Returns a value with data type varchar (64) indicating the type of SQL command that was last executed. The values that may be returned are listed under the Statement Type column in Table Procedure Requests in Writing a Table Procedure.
  • COMMAND_FUNCTION_CODE
    Returns a value with data type integer indicating the type of SQL command that was last executed. The values that may be returned are listed under the Command Number column in Table Procedure Requests in Writing a Table Procedure.
  • DYNAMIC_FUNCTION
    Returns a value with data type varchar (64) indicating the type of SQL command that was prepared or dynamically executed by the last command. The values that may be returned are listed under the Statement Type column in Table Procedure Requests in Writing a Table Procedure.
  • DYNAMIC_FUNCTION_CODE
    Returns a value with data type integer indicating the type of SQL command that was prepared or dynamically executed by the last command. The values that may be returned are listed under the Command Number column in Table Procedure Requests in Writing a Table Procedure.
  • IDMS_RETURNED_RESULT_SETS
    Returns a value with data type integer indicating the number of result sets returned by a procedure invoked by the last command. This value is only valid if the diagnosed statement is a call or select of an SQL invoked procedure.
  • MORE
    Returns a value with data type char(1). A value of 'Y' indicates that the execution of the previous SQL statement caused more conditions than have been set in the diagnostics area. A value of 'N' means that the diagnostics area contains information on all the completion and exception conditions.
  • NUMBER
    Returns a value with data type integer indicating the number of the exceptions or completion conditions set by the execution of the previous SQL statement for which information is available in the diagnostics area.
  • ROW_COUNT
    Returns a value with data type DEC(31). The value depends on the type of the previously executed statement:
    • INSERT - Number of rows inserted
    • DELETE - Number of rows deleted
    • UPDATE - Number of rows updated
    • BULK FETCH - Number of rows fetched
    • FETCH - 1 or 0
  • IDMS_ROWID_INSERT
    Returns a value with data type BINARY(8) containing the ROWID of the last record successfully inserted into the database. This value persists even after other types of queries are performed, for example SELECT, UPDATE, or DELETE.
Parameters for Expansion of condition-info
  • IDMS_MESSAGE_COMMENTS
    Returns a value with data type varchar(4000) containing the comments in the message dictionary for the message associated with the condition.
  • IDMS_MESSAGE_DEFINITION
    Returns a value with data type varchar(4000) containing the definition in the message dictionary of the message associated with the condition.
  • IDMS_MESSAGE_ID
    Returns a value with data type char(8) containing the message ID in the message dictionary of the message associated with the condition.
  • IDMS_MODULE_NUMBER
    Returns a value with data type integer containing the number of the module that detected the condition.
  • IDMS_REASON_CODE
    Returns a value with data type integer containing the reason code of the condition.
  • IDMS_SQLCODE
    Returns a value with data type integer containing the SQLCODE value associated with the condition.
  • IDMS_TASK_ID
    Returns a value with data type integer containing the IDMS task ID of the task that encountered the condition.
  • MESSAGE_LENGTH
    Returns a value with data type integer indicating the length of the message associated with the specified condition.
  • MESSAGE_TEXT
    Returns a value with data type varchar(256) containing the message text associated with the specified condition.
  • RETURNED_SQLSTATE
    Returns a value with data type char(5) indicating the SQLSTATE associated with the specified condition.
Example
The procedure TGETDIAG1 executes a SELECT statement that causes a number of string truncation. The first GET DIAGNOSTICS returns the number of conditions that the SELECT statement raised. A WHILE LOOP containing the second GET DIAGNOSTICS concatenates the message texts of all the raised conditions to the RESULT parameter of the procedure.
set options command delimiter '++'; create procedure SQLROUT.TGETDIAG1   ( TITLE    varchar(10) with default   , P_NAME   char(18)   , P_NUMBER integer   , RESULT   varchar(512)   )     EXTERNAL NAME TGETDIAG LANGUAGE SQL begin not atomic   declare L_NUMBER  integer      default 1;   declare L_MESSAGE varchar(256) default ' ';   select NAME into P_NAME from system.schema    where cast(NAME as char(12)) = P_NAME;   /* retrieve the number of conditions raised */   get diagnostics P_NUMBER = NUMBER;   while (L_NUMBER < = P_NUMBER)     do       /* retrieve the message text of the raised condition */       get diagnostics condition L_NUMBER         L_MESSAGE = MESSAGE_TEXT       set RESULT = RESULT || ' ' || L_MESSAGE;       set L_NUMBER = L_NUMBER + 1;     end while; end ++ commit++ set options command delimiter default++   call SQLROUT.TGETDIAG1('TGETDIAG1', 'SYSTEM'); *+ *+ TITLE       P_NAME                 P_NUMBER *+ -----       ------                 -------- *+ TGETDIAG1   SYSTEM                        4 *+ *+ RESULT *+ ------ *+ DB001043 T171 C1M322: String truncation DB001043 T171 C1M322: *+ String truncation DB001043 T171 C1M322: String truncation *+ DB001043 T171 C1M322: String truncation