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 for DESCRIBE PROCEDURE

This statement can only be embedded in an application program. It is an executable statement that cannot be dynamically prepared.

Authorization for DESCRIBE PROCEDURE

None required.

Syntax for DESCRIBE PROCEDURE

Description for DESCRIBE PROCEDURE

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: Start of change

The syntax described above applies to all languages except REXX. For REXX, the syntax is DESCRIBE PROCEDURE :hostvar. End of change

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.

Notes for DESCRIBE PROCEDURE

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 for DESCRIBE PROCEDURE

The statements in the following examples are assumed to be in PL/I programs.

Example 1: Place information about the result sets returned by stored procedure P1 into the descriptor named by SQLDA1. Assume that the stored procedure is called with a one-part name from current server SITE2.

   EXEC SQL CONNECT TO SITE2;
   EXEC SQL CALL P1;
   EXEC SQL DESCRIBE PROCEDURE P1 INTO :SQLDA1;

Example 2: Repeat the scenario in Example 1, but use a two-part name to specify an explicit schema name for the stored procedure to ensure that stored procedure P1 in schema MYSCHEMA is used.

   EXEC SQL CONNECT TO SITE2;
   EXEC SQL CALL MYSCHEMA.P1;
   EXEC SQL DESCRIBE PROCEDURE MYSCHEMA.P1 INTO :SQLDA1;

Example 3: Place information about the result sets returned by the stored procedure identified by host variable HV1 into the descriptor named by SQLDA2. Assume that host variable HV1 contains the value SITE2.MYSCHEMA.P1 and the stored procedure is called with a three-part name.

   EXEC SQL CALL SITE2.MYSCHEMA.P1;
   EXEC SQL DESCRIBE PROCEDURE :HV1 INTO :SQLDA2;

The preceding example would be invalid if host variable HV1 had contained the value MYSCHEMA.P1, a two-part name. For the example to be valid with that two-part name in host variable HV1, the current server must be the same as the location name that is specified on the CALL statement as the following statements demonstrate. This is the only condition under which the names do not have to be specified the same way and a three-part name on the CALL statement can be used with a two-part name on the DESCRIBE PROCEDURES statement.

   EXEC SQL CONNECT TO SITE2;
   EXEC SQL CALL SITE2.MYSCHEMA.P1;
   EXEC SQL ASSOCIATE LOCATORS (:LOC1, :LOC2)
            WITH PROCEDURE :HV1;