DESCRIBE CURSOR

The DESCRIBE CURSOR statement obtains information about the result set that is associated with the cursor. The information, such as column information, is put into a descriptor. Use DESCRIBE CURSOR for result set cursors from stored procedures. The cursor must be defined with the ALLOCATE CURSOR statement.

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

Read syntax diagram
>>-DESCRIBE CURSOR--+-cursor-name---+--INTO--descriptor-name---><
                    '-host-variable-'                          

Description

cursor-name or host-variable
Identifies a cursor by the specified cursor-name or the cursor name contained in host-variable. The name must identify a cursor that has already been allocated in the source program.

If host-variable is used:

  • It must be a character string variable that has a maximum length of 18 bytes.
  • It must not be followed by an indicator variable.
  • The cursor name must be left justified within the host variable and must not contain embedded blanks.
  • If the length of the cursor name is less than the length of the host variable, it must be padded on the right with blanks.
Exception: Start of changeThe syntax described above applies to all languages except REXX. For REXX, the syntax is DESCRIBE CURSOR :hostvar.End of change
INTO descriptor-name
Identifies an SQL descriptor area (SQLDA). The information returned in the SQLDA describes the columns in the result set associated with the named cursor.

The considerations for allocating and initializing the SQLDA are similar to those of a varying-list SELECT statement. For more information, see DB2 Application Programming and SQL Guide.

For REXX: The SQLDA is not allocated before it is used.

After the DESCRIBE CURSOR statement is executed, the contents of the SQLDA are the same as after a DESCRIBE for a SELECT statement, with the following exceptions:

  • The first 5 bytes of the SQLDAID field are set to 'SQLRS'.
  • Bytes 6 to 8 of the SQLDAID field are reserved. If the cursor is declared WITH HOLD in a stored procedure, the high-order bit of the 8th byte is set to 1.

These exceptions do not apply to a REXX SQLDA, which does not include the SQLDAID field.

Notes

Using cursors for result sets: Column names are included in the information that DESCRIBE CURSOR obtains when the statement that generates the result set is either:

  • Dynamic
  • Static and the value of field DESCRIBE FOR STATIC on installation panel DSNTIP4 was YES when the package or stored procedure was bound. If the value of the field was NO, the returned information includes only the data type and length of the columns.

Using host variables: If the DESCRIBE CURSOR 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.

Example 1: Place information about the result set associated with cursor C1 into the descriptor named by :sqlda1. 
   EXEC SQL DESCRIBE CURSOR C1 INTO :sqlda1
Example 2: Place information about the result set associated with the cursor named by :hv1 into the descriptor named by :sqlda2. 
   EXEC SQL DESCRIBE CURSOR :hv1 INTO :sqlda2