
The DESCRIBE PROCEDURE statement gets information about the result sets returned by a procedure. The information, such as the number of result sets, is put into a descriptor.
This statement can only be embedded in an application program, SQL function, SQL procedure, or trigger. It is an executable statement that cannot be dynamically prepared. It must not be specified in Java™ or REXX.
None required.
>>-DESCRIBE -+-+-+-PROCEDURE-+--procedure-name--+------------------------------+-+-+--> | | '-ROUTINE---' '-(--+--------------------+--)-' | | | | | .-,--------------. | | | | | | V | | | | | | '---parameter-type-+-' | | | '-SPECIFIC--+-PROCEDURE-+--specific-name--------------------------' | | '-ROUTINE---' | '-PROCEDURE--variable-------------------------------------------------' .-SQL-. .-LOCAL--. >--+-USING--+-----+--DESCRIPTOR--+--------+--SQL-descriptor-name-+->< | '-GLOBAL-' | '-INTO----descriptor-name-------------------------------------' parameter-type |----data-type--+------------+----------------------------------| '-AS LOCATOR-' data-type |--+-built-in-type------+---------------------------------------| '-distinct-type-name-'
built-in-type |--+-+---SMALLINT---+----------------------------------------------------------------------+--| | +-+-INTEGER-+--+ | | | '-INT-----' | | | '---BIGINT-----' | | .-(5,0)------------------------. | +-+-+-DECIMAL-+-+--+------------------------------+-------------------------------------+ | | '-DEC-----' | | .-,0--------. | | | '-+-NUMERIC-+-' '-(--integer--+-----------+--)-' | | '-NUM-----' '-, integer-' | | .-(--52--)------. | +-+-FLOAT--+---------------+-+----------------------------------------------------------+ | | '-(--integer--)-' | | | +-REAL---------------------+ | | | .-PRECISION-. | | | '-DOUBLE--+-----------+----' | | .-(--34--)-. | +---DECFLOAT--+----------+--------------------------------------------------------------+ | '-(--16--)-' | | .-(--1--)-------. | +-+-+-+-CHARACTER-+--+---------------+----------+--+----------------+-----------------+-+ | | | '-CHAR------' '-(--integer--)-' | +-FOR BIT DATA---+ | | | | '-+-+-CHARACTER-+--VARYING-+--(--integer--)-' +-FOR SBCS DATA--+ | | | | | '-CHAR------' | +-FOR MIXED DATA-+ | | | | '-VARCHAR----------------' '-ccsid-clause---' | | | | .-(--1M--)-------------. | | | '---+-+-CHARACTER-+--LARGE OBJECT-+----+----------------------+--+----------------+-' | | | '-CHAR------' | '-(--integer--+---+--)-' +-FOR SBCS DATA--+ | | '-CLOB------------------------' +-K-+ +-FOR MIXED DATA-+ | | +-M-+ '-ccsid-clause---' | | '-G-' | | .-(--1--)-------. | +-+---GRAPHIC----+---------------+-------+--+--------------+----------------------------+ | | '-(--integer--)-' | '-ccsid-clause-' | | +-+-GRAPHIC VARYING-+--(--integer--)---+ | | | '-VARGRAPHIC------' | | | | .-(--1M--)-------------. | | | '---DBCLOB----+----------------------+-' | | '-(--integer--+---+--)-' | | +-K-+ | | +-M-+ | | '-G-' | | .-(--1--)-------. | +-+-+-+-NATIONAL CHARACTER-+--+---------------+----------+---------------------+--------+ | | | +-NATIONAL CHAR------+ '-(--integer--)-' | | | | | | '-NCHAR--------------' | | | | | '-+-+-NATIONAL CHARACTER-+--VARYING-+--(--integer--)-' | | | | | +-NATIONAL CHAR------+ | | | | | | '-NCHAR--------------' | | | | | '-NVARCHAR------------------------' | | | | .-(--1M--)-------------. | | | '-----+-+-NATIONAL CHARACTER-+--LARGE OBJECT-+------+----------------------+-' | | | '-NCHAR--------------' | '-(--integer--+---+--)-' | | '-NCLOB--------------------------------' +-K-+ | | +-M-+ | | '-G-' | | .-(--1--)-------. | +-+-+-BINARY--+---------------+---------+-----------------+-----------------------------+ | | | '-(--integer--)-' | | | | | '-+-BINARY VARYING-+--(--integer--)-' | | | | '-VARBINARY------' | | | | .-(--1M--)-------------. | | | '---+-BLOB----------------+----+----------------------+-' | | '-BINARY LARGE OBJECT-' '-(--integer--+---+--)-' | | +-K-+ | | +-M-+ | | '-G-' | +-+-DATE-------------------+------------------------------------------------------------+ | | .-(--0--)-. | | | +-TIME--+---------+------+ | | | .-(--6--)-. | | | '-TIMESTAMP--+---------+-' | | .-(--200--)-----. | +---DATALINK--+---------------+--+--------------+---------------------------------------+ | '-(--integer--)-' '-ccsid-clause-' | +---ROWID-------------------------------------------------------------------------------+ '---XML---------------------------------------------------------------------------------' ccsid-clause |--CCSID--integer-----------------------------------------------|
If procedure-name () is specified, the procedure identified must have zero parameters.
If an unqualified distinct type or array type name is specified, the database manager searches the SQL path to resolve the schema name for the distinct type or array type.
For data types that have a length, precision, or scale attribute, use one of the following:
Specifying the FOR DATA clause or CCSID clause is optional. Omission of either clause indicates that the database manager ignores the attribute when determining whether the data types match. If either clause is specified, it must match the value that was implicitly or explicitly specified in the CREATE PROCEDURE statement.
There is one descriptor area item for each result set:
See GET DESCRIPTOR for
an explanation of the information that is placed in the SQL descriptor.
When the DESCRIBE statement is executed, the database manager assigns values to the variables of the SQLDA as follows:
If the value of SQLD is n, where n is greater than 0 but less than or equal to the value of SQLN, values are assigned to the first n occurrences of SQLVAR so that the first occurrence of SQLVAR contains a description of the first result set, the second occurrence of SQLVAR contains a description of the second result set, and so on. For each SQLVAR entry:
DESCRIBE PROCEDURE does not return information about the parameters expected by the procedure.
The CALL to the procedure must precede the DESCRIBE PROCEDURE statement.
Allocating the SQL descriptor: Before the DESCRIBE PROCEDURE statement is executed, the SQL descriptor must be allocated using the ALLOCATE DESCRIPTOR statement. If the number of descriptor items allocated is less than the number of result sets for the procedure, a warning (SQLSTATE 01005) is returned.
Allocating the SQLDA: Before the DESCRIBE PROCEDURE statement is executed, the value of SQLN must be set to a value greater than or equal to zero to indicate how many occurrences of SQLVAR are provided in the SQLDA and enough storage must be allocated to contain SQLN occurrences. To obtain the description of the result sets for the procedure, the number of occurrences of SQLVAR must not be less than the number result sets.
If not enough occurrences are provided to return all sets of occurrences, SQLN is set to the total number of occurrences necessary to return all information. Otherwise, SQLN is set to the number of result sets.
Assignment of locator values: If a SET RESULT SETS statement was executed in the procedure, the SET RESULT SETS statement identifies the result sets. The locator values are assigned to the items in the descriptor area or the SQLVAR entries in the SQLDA in the order specified on the SET RESULT SETS statement. If a SET RESULT SETS statement was not executed in the procedure, locator values are assigned to the items in the descriptor area or to the SQLVAR entries in the SQLDA in the order that the associated cursors are opened at run time. Locator values are not provided for cursors that are closed when control is returned to the invoking application. If a cursor was closed and later re-opened before returning to the invoking application, the most recently executed OPEN CURSOR statement for the cursor is used to determine the order in which the locator values are returned for the procedure result sets. For example, assume procedure P1 opens three cursors A, B, and C, closes cursor B, and then issues another OPEN CURSOR statement for cursor B before returning to the invoking application. The locator values are assigned in the order A, C, B.
Alternatively, an ASSOCIATE LOCATORS statement can be used to copy the locator values to result set locator variables.
Place information about the result sets returned by procedure P1 into an SQL descriptor:
EXEC SQL CALL P1; EXEC SQL ALLOCATE DESCRIPTOR 'DESC1'; EXEC SQL DESCRIBE PROCEDURE P1 USING SQL DESCRIPTOR 'DESC1';
