DB2 10.5 for Linux, UNIX, and Windows

DESCRIBE OUTPUT statement

The DESCRIBE OUTPUT statement obtains information about a prepared 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 diagramSkip visual syntax diagram
             .-OUTPUT-.                   
>>-DESCRIBE--+--------+--statement-name------------------------->

>--INTO--descriptor-name---------------------------------------><

Description

statement-name
Identifies the prepared statement. When the DESCRIBE OUTPUT statement is executed, the name must identify a statement that has been prepared by the application process at the current server.

If the prepared statement is a SELECT or VALUES INTO statement, the information returned describes the columns in its result table. If the prepared statement is a CALL statement, the information returned describes the output parameters, defined as OUT or INOUT, of the procedure.

INTO descriptor-name
Identifies an SQL descriptor area (SQLDA). Before the DESCRIBE OUTPUT 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 OUTPUT statement is executed.
When the DESCRIBE OUTPUT statement is executed, the database manager assigns values to the variables of the SQLDA as follows:
SQLDAID
The first 6 bytes are set to 'SQLDA ' (that is, 5 letters followed by the space character).
The seventh byte, defined as SQLDOUBLED, is set based on the results columns or parameter markers described:
  • If the SQLDA contains two SQLVAR entries for every column or output parameter, the seventh byte is set to '2'. This technique is used to accommodate LOB, distinct type, structured type, or reference type columns, or output parameters.
  • Otherwise, the seventh byte is set to the space character.

The seventh byte is set to the space character if there is not enough room in the SQLDA to contain the description of all result columns or output parameter markers.

The eighth byte is set to the space character.

SQLDABC
Length of the SQLDA in bytes.
SQLD
If the prepared statement is a SELECT, SQLD is set to the number of columns in its result table. If the prepared statement is a CALL statement, SQLD is set to the number of OUT and INOUT parameters of the procedure. Otherwise, SQLD is set to 0.
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 SQLTYPE, SQLLEN, SQLNAME, SQLLONGLEN, and SQLDATATYPE_NAME for the first n occurrences of SQLVAR. These values describe either columns of the result table or parameter markers for the output parameters of the procedure. The first occurrence of SQLVAR describes the first column or output parameter marker, the second occurrence of SQLVAR describes the second column or output parameter marker, and so on.

Base SQLVAR
SQLTYPE
A code showing the data type of the column or parameter and whether or not it can contain null values.
SQLLEN
A length value depending on the data type of the column or parameter. SQLLEN is 0 for LOB data types.
SQLNAME
The sqlname is derived as follows:
  • If the SQLVAR corresponds to a derived column for a simple column reference in the select list of a select-statement, sqlname is the name of the column.
  • If the SQLVAR corresponds to a parameter marker that is in the parameter list of a procedure and is not part of an expression, sqlname contains the name of the parameter if one was specified on CREATE PROCEDURE.
  • Otherwise sqlname contains an ASCII numeric literal value that represents the SQLVAR's position within the SQLDA.
Secondary SQLVAR
These variables are only used if the number of SQLVAR entries is doubled to accommodate LOB, distinct type, structured type, or reference type columns or parameters.
SQLLONGLEN
The length attribute of a BLOB, CLOB, or DBCLOB column or parameter.
SQLDATATYPE_NAME
For any user-defined type (distinct or structured) column or parameter, the database manager sets this to the fully qualified user-defined type name. For a reference type column or parameter, the database manager sets this to the fully qualified user-defined type name of the target type of the reference. Otherwise, schema name is SYSIBM and the type name is the name in the TYPENAME column of the SYSCAT.DATATYPES catalog view.

Notes

Example

In a C program, execute a DESCRIBE OUTPUT statement with an SQLDA that has no occurrences of SQLVAR. If SQLD is greater than zero, use the value to allocate an SQLDA with the necessary number of occurrences of SQLVAR and then execute a DESCRIBE statement using that SQLDA.
   EXEC SQL  BEGIN DECLARE SECTION;
     char  stmt1_str[200];
   EXEC SQL  END DECLARE SECTION;
   EXEC SQL  INCLUDE SQLDA;
   EXEC SQL  DECLARE DYN_CURSOR CURSOR FOR STMT1_NAME;

   ... /* code to prompt user for a query, then to generate */
       /* a select-statement in the stmt1_str            */
   EXEC SQL  PREPARE STMT1_NAME FROM :stmt1_str;
   
   ... /* code to set SQLN to zero and to allocate the SQLDA */
   EXEC SQL  DESCRIBE STMT1_NAME INTO :sqlda;

   ... /* code to check that SQLD is greater than zero, to set */
       /* SQLN to SQLD, then to re-allocate the SQLDA          */
   EXEC SQL  DESCRIBE STMT1_NAME INTO :sqlda;

   ... /* code to prepare for the use of the SQLDA             */
       /* and allocate buffers to receive the data             */
   EXEC SQL  OPEN DYN_CURSOR;

   ... /* loop to fetch rows from result table                 */
   EXEC SQL  FETCH DYN_CURSOR USING DESCRIPTOR :sqlda;
   .
   .
   .