DESCRIBE PROCEDURE
The DESCRIBE PROCEDURE statement gets information about the result sets returned by a stored 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. It is an executable statement that cannot be dynamically prepared.
Authorization
None required.
Syntax
>>-DESCRIBE PROCEDURE--+-procedure-name-+--INTO--descriptor-name->< '-host-variable--'
Description
- procedure-name or host-variable
- Identifies the stored procedure that returned one or more result sets. When the DESCRIBE
PROCEDURE statement is executed, the procedure name must identify a stored procedure that the
requester has already invoked using the SQL CALL statement. The procedure name can be specified as a
one, two, or three-part name. The procedure name in the DESCRIBE PROCEDURE statement must be
specified the same way that it was specified on the CALL statement. For example, if a two-part
procedure name was specified on the CALL statement, you must specify a two-part procedure name in
the DESCRIBE PROCEDURE statement.
If a host variable is used:
- It must be a character string variable with a length attribute that is not greater than 254.
- It must not be followed by an indicator variable.
- The value of the host variable is a specification that depends on the database server.
Regardless of the server, the specification must:
- Be left justified within the host variable
- Not contain embedded blanks
- Be padded on the right with blanks if its length is less than that of the host variable
Exception: The syntax described above applies to all languages except REXX. For REXX, the syntax is DESCRIBE PROCEDURE :hostvar. - INTO descriptor-name
- Identifies
an SQL descriptor area (SQLDA). The information returned in the SQLDA
describes the result sets returned by the stored procedure.
Considerations for allocating and initializing the SQLDA are similar to those for DESCRIBE TABLE.
The contents of the SQLDA after executing a DESCRIBE PROCEDURE statement are:
- The first 5 bytes of the SQLDAID field are set to 'SQLPR'.
A REXX SQLDA does not contain SQLDAID.
- Bytes 6 to 8 of the SQLDAID field are reserved.
- The SQLD field is set to the total number of result sets. A value of 0 in the field indicates there are no result sets.
- There is one SQLVAR entry for each result set.
- The SQLDATA field of each SQLVAR entry is set to the result set
locator value associated with the result set.
For a REXX SQLDA, SQLLOCATOR is set to the result set locator value.
- The SQLIND field of each SQLVAR entry is set to the estimated
number of rows in the result set
For a REXX SQLDA, the SQLIND field is not used for DESCRIBE.
- The SQLNAME field is set to the name of the cursor used by the stored procedure to return the result set. This value is returned in the encoding scheme specified by the ENCODING bind option for the plan or package that contains this statement.
- The first 5 bytes of the SQLDAID field are set to 'SQLPR'.
Notes
SQLDA information: A value of -1 in the SQLIND field indicates that an estimated number of rows in the result set is not provided. DB2® for z/OS® always sets SQLIND to -1. For a REXX SQLDA, the SQLIND field is not used for DESCRIBE.
DESCRIBE PROCEDURE does not return information about the parameters expected by the stored procedure.
Assignment of locator values: Locator values are assigned 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, 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.
Using host variables: If the DESCRIBE PROCEDURE statement contains host variables, the contents of the host variables are assumed to be in the encoding scheme that was specified in the ENCODING parameter when the package or plan that contains the statement was bound.
Examples
The statements in the following examples are assumed to be in PL/I programs.
EXEC SQL CONNECT TO SITE2;
EXEC SQL CALL P1;
EXEC SQL DESCRIBE PROCEDURE P1 INTO :SQLDA1;
EXEC SQL CONNECT TO SITE2;
EXEC SQL CALL MYSCHEMA.P1;
EXEC SQL DESCRIBE PROCEDURE MYSCHEMA.P1 INTO :SQLDA1;
EXEC SQL CALL SITE2.MYSCHEMA.P1;
EXEC SQL DESCRIBE PROCEDURE :HV1 INTO :SQLDA2;
EXEC SQL CONNECT TO SITE2;
EXEC SQL CALL SITE2.MYSCHEMA.P1;
EXEC SQL ASSOCIATE LOCATORS (:LOC1, :LOC2)
WITH PROCEDURE :HV1;