DESCRIBE

The DESCRIBE statement (embedded SQL only) obtains information about a previously prepared (PREPARE command) SQL statement and places this information inside an SQL Descriptor Area (SQLDA). This information is needed in dynamic SQL queries that utilize a varying-list SELECT statement. A varying-list SELECT statement is a SELECT statement where the number of columns retrieved is not known before runtime. Alternatively, the DESCRIBE statement can provide information about a specified table or view.

Invocation

DESCRIBE is an executable command that can only be embedded in a host language. DESCRIBE cannot be dynamically prepared.

Authorization

No special privileges are required when describing a prepared statement. However, when describing a table or view, the privileges held by the AuthID owning the application must include either ownership of the table, SELECT, INSERT, UPDATE, or DELETE privilege on the table or view, ALTER or INDEX privilege on the table, DBADM authority over the database containing the object (tables only), or SYSADM or SYSCTRL authority for the location.

See the PREPARE statement description for authorization privileges required to prepare a command.

Syntax

DESCRIBE {statement-name | TABLE host-variable}
    INTO descriptor-name [USING {NAMES | LABELS | ANY | BOTH}]

Parameters:

statement-name Identifies an SQL statement already prepared by an application process at the current location.
host-variable The combination of the keyword TABLE followed by host-variable, identifies a table or view.

The name must not identify an auxiliary table

descriptor-name preceded by the keyword INTO, names an SQL Descriptor Area (SQLDA) variable.

Description

You may have a uniquely named SQLDA defined for every cursor utilized in an application process. When the DESCRIBE statement is applied to a prepared query statement, the information returned to the specified SQLDA describes the columns of the result table (for that cursor). When a table or view is named in the DESCRIBE statement, the information returned in the SQLDA defines the columns in the table or view.

The SQLDA must be allocated before the DESCRIBE statement is executed. Once allocated, the SQLDA consists of the SQLDAID, SQLDABC, SQLN, and SQLD fields followed by the SQLVAR array structure (containing one or more occurrences). The number of elements within the SQLVAR must be equal to or greater than the number of columns in the described table. For more details on the SQLDA, see XDB Server SQL Descriptor Area (SQLDA).

Before executing the DESCRIBE statement, the SQLN field in the SQLDA must be set to indicate the number of SQLVAR occurrences in the SQLDA (this value is not changed by the XDB Server). Once SQLN is determined, performance can be improved by resetting this value after the DESCRIBE statement is first executed. With the exception of SQLN, all other SQLDA fields are set by the XDB Server software or are ignored, as indicated in the field descriptions below. Please note that the following descriptions do not necessarily apply to SQLDA usage with PREPARE, EXECUTE, OPEN, and FETCH statements:

  • SQLDAID is set to "SQLDA" by the XDB Server software.
  • SQLDABC is the length of the SQLDA in bytes, and equals SQLN*44+16 (as set by the XDB Server software).
  • SQLD is set to either the number of columns in the object being described or to zero (if DESCRIBE is applied to a nonquery statement). In order for the SQLDA to be large enough for the information requested, the returned value of SQLD must be less than or equal to the value of SQLN. If SQLD is larger than SQLN, no information is returned from the query into the SQLDA.
  • SQLVAR array values are assigned for each column in the object being described, such that the first array value describes the first column in the object, the second array value describes the second column, etc.
  • SQLTYPE is a code indicating the data type of the column and whether or not the column can contain null values.
  • SQLLEN is the length of the result column, which varies according to the data type of the column.
  • SQLDATA stores a dynamic memory address for the column.
  • SQLIND stores an indicator variable address for nullable columns.
  • SQLNAME is the unqualified name of the column.

Allocating SQLDA

To aid in the allocation of SQLDA memory space, first execute a DESCRIBE statement into an SQL Descriptor Area where the SQLN variable is deliberately set to zero. The value returned for the SQLD variable will equal the number of columns that need to be processed in the select list (which is the required value of SQLN). Use this returned value of SQLD to allocate another SQLDA, with the SQLN value preset to at least SQLD (or more) occurrences of the SQLVAR array. Execute the DESCRIBE statement again specifying this new SQLDA.

  • First technique

    Allocate an SQLDA with enough occurrences of SQLVAR to accommodate any select list that the application will have to process. At the extreme, the number of SQLVARs could equal three times the maximum number of columns allowed in a result table. After the SQLDA is allocated, the application can use the SQLDA repeatedly.

    This technique uses a large amount of storage that is never deallocated, even when most of this storage is not used for a particular select list.

  • Second technique

    Repeat the following for every processed select list:

    1. Execute a DESCRIBE statement with an SQLDA that has no occurrences of SQLVAR; that is, an SQLDA for which SQLN is zero.
    2. Allocate a new SQLDA with enough occurrences of SQLVAR. Use the values that are returned in SQLD and SQLCODE to determine the number of SQLVAR entries that are needed. The value of SQLD is the number of columns in the result table, which is either the required number of occurrences of SQLVAR or a fraction of the required number If the SQLCODE is +236, +237, +238, or +239, the number of SQLVAR entries that is needed is two or three times the value in SQLD, depending on whether USING BOTH was specified. Set SQLN to reflect the number of SQLVAR entries that have been allocated.
    3. Execute the DESCRIBE statement again, using the new SQLDA.

    This technique allows better storage management than the first technique, but it doubles the number of DESCRIBE statements.

  • Third technique

    Allocate an SQLDA that is large enough to handle most (hopefully, all) select lists but is also reasonably small. If an execution of DESCRIBE fails because SQLDA is too small, allocate a larger SQLDA and execute the DESCRIBE statement again.

    For the new larger SQLDA, use the values that are returned in SQLD and SQLCODE from the failing DESCRIBE statement to calculate the number of occurrences of SQLVAR that are needed, as described in technique two. Remember to check for SQLCODEs +236, +237, +238, and +239, which indicate whether extended SQLVAR entries are needed because the data includes LOBs or distinct types.

    This third technique is a compromise between the first two techniques. Its effectiveness depends on a good choice of size for the original SQLDA.

SQLDAID

XDB sets the first 6 bytes to 'SQLDA ' (5 letters followed by the space character) and the eighth byte to a space character. The seventh byte is set to indicate the number of SQLVAR entries that are needed to describe each column of the result table as follows:

space

The value of space occurs when:

  • USING BOTH was not specified and the columns being described do not include LOBs or distinct types. Each column only needs one SQLVAR entry. If the SQL standard option is yes, XDB sets SQLCODE to warning code +236. Otherwise, SQLCODE is zero.
  • USING BOTH was specified and the columns being described do not include LOBs or distinct types. Each column needs two SQLVAR entries. XDB sets SQLD to two times the number of columns of the result table. The second set of SQLVARs is used for the labels.

2

Each column needs two SQLVAR entries. Two entries per column are required when:

  • USING BOTH was not specified and the columns being described include LOBs or distinct types or both. XDB sets the second set of SQLVAR entries with information for the LOBs or distinct types being described.
  • USING BOTH was specified and the columns include LOBs but not distinct types. XDB sets the second set of SQLVAR entries with information for the LOBs and labels for the columns being described.

3

Each column needs three SQLVAR entries. Three entries are required only when USING BOTH is specified and the columns being described include distinct types. The presence of LOB data does not matter. It is the distinct types and not the LOBs that cause the need for three SQLVAR entries per column when labels are also requested. XDB sets the second set of SQLVAR entries with information for the distinct types (and LOBs, if any) and the third set of SQLVAR entries with the labels of the columns being described.

SQLD

If the prepared statement is a query, XDB sets the value to the number of columns in the object being described (the value is actually twice the number of columns in the case where USING BOTH was specified and the result table does not include LOBs or distinct types).

Otherwise, if the statement is not a query, XDB sets the value to 0.

SQLVAR

An array of field description information for the column being described. There are two types of SQLVAR entries--the base SQLVAR and the extended SQLVAR.

If the value of SQLD is 0, or is greater than the value of SQLN, no values are assigned to any occurrences of SQLVAR. If the value of SQLN was set so that there are enough SQLVAR occurrences to describe the specified columns (columns with LOBs or distinct types and a request for labels increase the number of SQLVAR entries that are needed), the values are assigned to the first n occurrences of SQLVAR so that the first occurrence of SQLVAR contains a description of the first column, the second occurrence of SQLVAR contains a description of the second column, and so on. This first set of SQLVAR entries are referred to as base SQLVAR entries. Each column always has a base SQLVAR entry.

If the DESCRIBE statement included the USING BOTH clause, or the columns being described include LOBs or distinct types, additional SQLVAR entries are needed. These additional SQLVAR entries are referred to as the extended SQLVAR entries. There can be up to two sets of extended SQLVAR entries for each column.

The base SQLVAR

SQLTYPE A code that indicates the data type of the column and whether the column can contain null values.
SQLLEN A length value depending on the data type of the result columns. SQLLEN is 0 for LOB data types.
SQLDATA The CCSID of a string column.
SQLIND Reserved.
SQLNAME The unqualified name or label of the column, depending on the value of USING (NAMES, LABELS, ANY, or BOTH). The field is a string of length 0 if the column does not have a name or label.

The extended SQLVAR

SQLLONGLEN The length attribute of a BLOB, CLOB, or DBCLOB column.
* Reserved.
SQLDATALEN Not Used.
SQLDATATYPE-NAME For a distinct type, the fully qualified distinct type name. Otherwise, the value is the fully qualified name of the built-in data type.

For a label, the label for the column.

USING

Indicates what value to assign to each SQLNAME variable in the SQLDA. If the requested value does not exist, SQLNAME is set to a length of 0.

NAMES Assigns the name of the column. This is the default.
LABELS Assigns the label of the column. (Column labels are defined by the LABEL ON statement.)
ANY Assigns the column label, and if the column has no label, the column name.
BOTH Assigns both the label and name of the column. In this case, two or three occurrences of SQLVAR per column, depending on whether the result set contains distinct types, are needed to accommodate the additional information. To specify this expansion of the SQLVAR array, set SQLN to 2¦n or 3¦n, where n is the number of columns in the object being described. For each of the columns, the first n occurrences of SQLVAR, which are the base SQLVAR entries, contain the column names. Either the second or third n occurrences of SQLVAR, which are the extended SQLVAR entries, contain the column labels. If there are no distinct types, the labels are returned in the second set of SQLVAR entries. Otherwise, the labels are returned in the third set of SQLVAR entries.

For a declared temporary table, the name of the column is assigned regardless of the value specified in the USING clause because declared temporary tables cannot have labels.

Performance considerations

Although XDB does not change the value of SQLN, you might want to reset this value after the DESCRIBE statement is executed. If the contents of SQLDA from the DESCRIBE statement is used in a later FETCH statement, set SQLN to n (where n is the number of columns of the result table) before executing the FETCH statement.

Example:

EXEC SQL 
    DESCRIBE getcust INTO mydesc
END-EXEC

where mydesc is a defined SQLDA in working storage and getcust is a previously prepared command.