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.
►─ 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 ───────────────────┘
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 ───────────┘
A routine-parameter, host-variable, or local-variable must be specified for each statement-info or condition-info item.
Identifies the type of statement information to be extracted and returned. Statement-info names that begin with 'IDMS_' are extensions to the SQL standard.
Requests diagnostic information for a condition.
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.
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.
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
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Returns a value with data type DEC(31). The value depends on the type of the previously executed statement:
Parameters for Expansion of condition-info
Returns a value with data type varchar(4000) containing the comments in the message dictionary for the message associated with the condition.
Returns a value with data type varchar(4000) containing the definition in the message dictionary of the message associated with the condition.
Returns a value with data type char(8) containing the message ID in the message dictionary of the message associated with the condition.
Returns a value with data type integer containing the number of the module that detected the condition.
Returns a value with data type integer containing the reason code of the condition.
Returns a value with data type integer containing the SQLCODE value associated with the condition.
Returns a value with data type integer containing the IDMS task ID of the task that encountered the condition.
Returns a value with data type integer indicating the length of the message associated with the specified condition.
Returns a value with data type varchar(256) containing the message text associated with the specified condition.
Returns a value with data type char(5) indicating the SQLSTATE associated with the specified condition.
The procedure TGETDIAG1 executes a SELECT statement that causes a number of string truncations. 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
|
Copyright © 2014 CA.
All rights reserved.
|
|