DESCRIBE CURSOR

The DESCRIBE CURSOR statement gets information about a cursor. The information, such as column information, 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 CURSOR cursor-namevariable USINGSQLDESCRIPTORLOCALGLOBALSQL-descriptor-nameINTOdescriptor-name

Description

cursor-name or variable
Identifies a cursor that has already been open or allocated in the source program.

If a variable is specified:

  • It must be a character-string variable or Unicode graphic-string. It cannot be a global variable.
  • It must not be followed by an indicator variable.
  • The cursor 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 of the cursor must be in uppercase unless the cursor name is a delimited 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.
The information returned in the descriptor area describes the columns in the result set associated with the named cursor. After the DESCRIBE CURSOR is executed, the contents of the descriptor area are the same as after a DESCRIBE of a SELECT with the following addition.
  • DB2_CURSOR_HOLD can be returned from the GET DESCRIPTOR statement to indicate whether the cursor was declared WITH HOLD in the procedure.

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 CURSOR 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 CURSOR statement is executed. For information about techniques to determine the number of occurrences required, see Determining how many SQLVAR occurrences are needed.

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 'SQLRS'. Bytes 6 to 8 are reserved. If the cursor is declared WITH HOLD in the procedure, the high-order bit of the 8th byte is set to 1.
SQLDABC
Length of the SQLDA in bytes.
SQLD
The number of columns in the result table plus the number of extended SQLVAR entries. For information about extended SQLVAR entries see, Field descriptions in an occurrence of SQLVAR.
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 column of the result table, the second occurrence of SQLVAR contains a description of the second column of the result table, and so on. For information about the values assigned to SQLVAR occurrences, see Field descriptions in an occurrence of SQLVAR.

Notes

Allocating the SQL descriptor: Before the DESCRIBE CURSOR 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 columns in the cursor result set, a warning (SQLSTATE 01005) is returned.

Allocating the SQLDA: Before the DESCRIBE CURSOR 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 columns of the cursor result set, the number of occurrences of SQLVAR must not be less than the number of columns.

For a description of techniques that can be used to allocate the SQLDA, see SQLDA (SQL descriptor area).

Example

Place information about the result set associated with cursor C1 into an SQL descriptor.

  EXEC SQL ALLOCATE DESCRIPTOR 'DESCR1';
  EXEC SQL DESCRIBE CURSOR C1 USING SQL DESCRIPTOR 'DESCR1';