DESCRIBE is an executable command that can only be embedded in a host language. DESCRIBE cannot be dynamically prepared.
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.
DESCRIBE {statement-name | TABLE host-variable}
INTO descriptor-name [USING {NAMES | LABELS | ANY | BOTH}]
| 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. |
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.
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.
Repeat the following for every processed select list:
This technique allows better storage management than the first technique, but it doubles the number of DESCRIBE statements.
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.
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:
The value of space occurs when:
Each column needs two SQLVAR entries. Two entries per column are required when:
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.
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.
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.
| 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. |
| 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. |
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.
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.
EXEC SQL
DESCRIBE getcust INTO mydesc
END-EXEC
where mydesc is a defined SQLDA in working storage and getcust is a previously prepared command.
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: