DESCRIBE PROCEDURE

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.

Invocation

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.

Authorization

None required.

Syntax

Read syntax diagramSkip visual syntax diagramDESCRIBE PROCEDUREROUTINEprocedure-name(,parameter-type)SPECIFICPROCEDUREROUTINEspecific-namePROCEDUREvariable USINGSQLDESCRIPTORLOCALGLOBALSQL-descriptor-nameINTOdescriptor-name
parameter-type
Read syntax diagramSkip visual syntax diagramdata-typeAS LOCATOR
data-type
Read syntax diagramSkip visual syntax diagrambuilt-in-typedistinct-type-name
built-in-type
Read syntax diagramSkip visual syntax diagramSMALLINTINTEGERINTBIGINTDECIMALDECNUMERICNUM(5,0)(integer,0, integer)FLOAT(53)(integer)REALDOUBLEPRECISIONDECFLOAT(34)(16)CHARACTERCHAR(1)(integer)CHARACTERCHARVARYINGVARCHAR(integer)FOR BIT DATAFOR SBCS DATAFOR MIXED DATAccsid-clauseCHARACTERCHARLARGE OBJECTCLOB(1M)(integerKMG)FOR SBCS DATAFOR MIXED DATAccsid-clauseGRAPHIC(1)(integer)GRAPHIC VARYINGVARGRAPHIC(integer)DBCLOB(1M)(integerKMG)ccsid-clauseNATIONAL CHARACTERNATIONAL CHARNCHAR(1)(integer)NATIONAL CHARACTERNATIONAL CHARNCHARVARYINGNVARCHAR(integer)NATIONAL CHARACTERNCHARLARGE OBJECTNCLOB(1M)(integerKMG)BINARY(1)(integer)BINARY VARYINGVARBINARY(integer)BLOBBINARY LARGE OBJECT(1M)(integerKMG)DATETIME(0)TIMESTAMP(6)(integer)DATALINK(200)(integer)ccsid-clauseROWIDXML
ccsid-clause
Read syntax diagramSkip visual syntax diagramCCSIDinteger

Description

procedure-name or specific-name or variable
Identifies the procedure that returned one or more result sets. When the DESCRIBE PROCEDURE statement is executed, the procedure name must identify a procedure that the requester has already invoked using the SQL CALL statement.
PROCEDURE or SPECIFIC PROCEDURE
Identifies the procedure to be described. The procedure-name must identify a procedure that exists at the current server.
PROCEDURE procedure-name
Identifies the procedure by its name. The procedure-name must identify exactly one procedure. The procedure may have any number of parameters defined for it. If there is more than one procedure of the specified name in the specified or implicit schema, an error is returned.
PROCEDURE procedure-name (parameter-type, ...)
Identifies the procedure by its procedure signature, which uniquely identifies the procedure. The procedure-name (parameter-type, ...) must identify a procedure with the specified procedure signature. The specified parameters must match the data types in the corresponding position that were specified when the procedure was created. The number of data types, and the logical concatenation of the data types is used to identify the specific procedure instance which is to be labeled on. Synonyms for data types are considered a match. Parameters that have defaults must be included in this signature.

If procedure-name () is specified, the procedure identified must have zero parameters.

procedure-name
Identifies the name of the procedure.
(parameter-type, ...)
Identifies the parameters of the procedure.

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:

  • Empty parentheses indicate that the database manager ignores the attribute when determining whether the data types match. For example, DEC() will be considered a match for a parameter of a procedure defined with a data type of DEC(7,2). However, FLOAT cannot be specified with empty parenthesis because its parameter value indicates a specific data type (REAL or DOUBLE).
  • If a specific value for a length, precision, or scale attribute is specified, the value must exactly match the value that was specified (implicitly or explicitly) in the CREATE PROCEDURE statement. If the data type is FLOAT, the precision does not have to exactly match the value that was specified because matching is based on the data type (REAL or DOUBLE).
  • If length, precision, or scale is not explicitly specified, and empty parentheses are not specified, the default attributes of the data type are implied. The implicit length must exactly match the value that was specified (implicitly or explicitly) in the CREATE PROCEDURE statement.

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.

AS LOCATOR
Specifies that the procedure is defined to receive a locator for this parameter. If AS LOCATOR is specified, the data type must be a LOB or XML or a distinct type based on a LOB or XML. If AS LOCATOR is specified, FOR SBCS DATA or FOR MIXED DATA must not be specified.
SPECIFIC PROCEDURE specific-name
Identifies the procedure by its specific name. The specific-name must identify a specific procedure that exists at the current server.
variable
Specifies a variable that contains a procedure or specific name. If variable is specified:
  • It must be a character-string variable or Unicode graphic-string variable. It cannot be a global variable.
  • It must not be followed by an indicator variable.
  • The name that is contained within the variable must be left-justified and must be padded on the right with blanks if its length is less than that of the variable.
  • The name must be in uppercase unless it is a delimited name.

If only one procedure with this name has been invoked using the CALL statement, the variable is used as a procedure name. If multiple procedures with this name have been invoked, the variable is used as a specific name.

USING
Identifies an SQL descriptor.
LOCAL
Specifies the scope of the name of the descriptor to be local to program invocation.
GLOBAL
Specifies the scope of the name of the descriptor to be global to the SQL session.
SQL-descriptor-name
Names the SQL descriptor. The name must identify a descriptor that already exists with the specified scope.
After the DESCRIBE PROCEDURE is executed, the following values can be retrieved with the GET DESCRIPTOR statement:
  • DB2_RESULT_SETS_COUNT contains the total number of result sets. A value of 0 indicates there are no result sets.

There is one descriptor area item for each result set:

  • DB2_RESULT_SET_LOCATOR contains the result set locator value associated with the result set.
  • DB2_CURSOR_NAME contains the name of the cursor used by the procedure to return the result set.
  • DB2_RESULT_SET_ROWS contains the estimated number of rows in the result set. This is set to -1 if the number is unknown.

See GET DESCRIPTOR for an explanation of the information that is placed in the SQL descriptor.

INTO descriptor-name
Identifies an SQL descriptor area (SQLDA), which is described in SQLDA (SQL descriptor area). Before the DESCRIBE PROCEDURE statement is executed, the following variable in the SQLDA must be set.
SQLN
Specifies the number of SQLVAR occurrences provided in the SQLDA. SQLN must be set to a value greater than or equal to zero before the DESCRIBE PROCEDURE statement is executed.

When the DESCRIBE statement is executed, the database manager assigns values to the variables of the SQLDA as follows:

SQLDAID
The first 5 bytes are set to 'SQLPR'. Bytes 6 to 8 are reserved.
SQLDABC
Length of the SQLDA in bytes.
SQLD
The total number of result sets. A value of 0 indicates there are no result sets.
SQLVAR
If the value of SQLD is 0, or greater than the value of SQLN, no values are assigned to occurrences of SQLVAR.

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:

  • The SQLDATA field is set to the result set locator value associated with the result set.
  • The SQLIND field is set to the estimated number of rows in the result set. This is set to -1 if the number is unknown.
  • The SQLNAME field is set to the name of the cursor used by the stored procedure to return the result set. The cursor name is truncated to 30 characters if it is longer than 30 characters.

Notes

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.

Example

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';