DESCRIBE TABLE statement

Invocation for DESCRIBE TABLE

This statement can only be embedded in an application program. It is an executable statement that cannot be dynamically prepared. It must not be specified in Java™.

Authorization for DESCRIBE TABLE

The privileges that are held by the authorization ID that owns the plan or package must include at least one of the following (if there is a plan, authorization checking is done only against the plan owner):

• Ownership of the table or view
• The SELECT, INSERT, UPDATE, DELETE, or REFERENCES privilege on the object
• The ALTER or INDEX privilege on the object (tables only)
• DBADM authority over the database that contains the object (tables only)
• SYSADM authority
• SYSCTRL authority (catalog tables only)
• ACCESSCTRL authority (catalog tables only)
• System DBADM
• DATAACCESS authority
• EXPLAIN authority
• SQLADM authority

If the database is implicitly created, the database privileges must be on the implicit database or on DSNDB04.

For an RRSAF application that does not have a plan and in which the requester and the server are Db2 for z/OS® systems, authorization to execute the package is performed against the primary or secondary authorization ID of the process.

Description for DESCRIBE TABLE

TABLE host-variable

Identifies the table or view. The name must not identify an auxiliary table. When the DESCRIBE statement is executed, the host variable must contain a name which identifies a table or view that exists at the current server. This variable must be a fixed-length or varying-length character string with a length attribute less than 256. The name must be followed by one or more blanks if the length of the name is less than the length of the variable. It cannot contain a period as the first character and it cannot contain embedded blanks. In addition, the quotation mark is the escape character regardless of the value of the string delimiter option. An indicator variable must not be specified for the host variable.

INTO descriptor-name

Identifies an SQL descriptor area (SQLDA), which is described in SQL descriptor area (SQLDA). See Identifying an SQLDA in C or C++ for how to represent descriptor-name in C.

For languages other than REXX: Before the DESCRIBE statement is executed, the user must set the following variable in the SQLDA and the SQLDA must be allocated.

SQLN

Indicates the number of SQLVAR occurrences provided in the SQLDA. Db2 does not change this value. For techniques to determine the number of required occurrences, see Allocating the SQLDA.

For REXX: The SQLDA is not allocated before it is used. An SQLDA consists of a set of stem variables. There is one occurrence of variable stem.SQLD, followed by zero or more occurrences of a set of variables that is equivalent to an SQLVAR structure. Those variables begin with stem.n.

After the DESCRIBE statement is executed, all the fields in the SQLDA except SQLN are either set by Db2 or ignored. For information on the contents of the fields, see The SQLDA contents returned after DESCRIBE.

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 statement.)

ANY

Assigns the label of the column. If the column has no label or the label is a string of length 0, the column name is used instead.

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 2xn or 3xn, 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.

Notes for DESCRIBE TABLE

Allocating the SQLDA:

Before the DESCRIBE or PREPARE INTO 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. Also, enough storage must be allocated to contain the number of occurrences that SQLN specifies. To obtain the description of the columns of the result table of a prepared SELECT statement, the number of occurrences of SQLVAR must be at least equal to the number of columns. Furthermore, if USING BOTH is specified, or if the columns include LOBs or distinct types, the number of occurrences of SQLVAR should be two or three times the number of columns. See Determining how many SQLVAR occurrences are needed for more information.

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 two steps 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 (see Determining how many SQLVAR occurrences are needed for details). 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, u

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

   sing the new SQLDA.

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.

SQLDA contents returned on DESCRIBE:

After a DESCRIBE statement is executed, the following list describes the contents of the SQLDA fields as they are set by Db2 or ignored. These descriptions do not necessarily apply to the uses of an SQLDA in other SQL statements (EXECUTE, OPEN, FETCH). For more on the other uses, see SQL descriptor area (SQLDA).

SQLDAID

Db2 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, Db2 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. Db2 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. Db2 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. Db2 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. Db2 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.

A REXX SQLDA does not contain this field.

SQLDABC

The length of the SQLDA in bytes. Db2 sets the value to SQLN×44+16.

A REXX SQLDA does not contain this field.

SQLD

If the prepared statement is a query, Db2 sets the value to the number of columns in the object being described. (For languages other than REXX, in the case where USING BOTH was specified and the result table does not include LOBs or distinct types, the value is actually twice the number of columns. For REXX, if USING BOTH is specified, the value is twice the number of columns, regardless of whether the result table include LOBs or distinct types.) Otherwise, if the statement is not a query, Db2 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.

For REXX, the SQLVAR is a set of stem variables that begin with stem.n, instead of a structure. The REXX SQLDA uses only a base SQLVAR. However, REXX uses The way in which Db2 assigns values to the SQLVAR variables is the same as for other languages. That is, the stem.1 variables describe the first column in the result table, the stem.2 variables describe the second column in the result table, and so on. If USING BOTH is specified, the stem+1 variables also describe the first column in the result table, the stem.n+2 variables also describe the second column in the result table, and so on.

The base SQLVAR:

SQLTYPE

A code that indicates the data type of the column and whether the column can contain null values. For the possible values of SQLTYPE, see SQLTYPE and SQLLEN.

SQLLEN

A length value depending on the data type of the result columns. SQLLEN is 0 for LOB and XML data types. For the other possible values of SQLLEN, see SQLTYPE and SQLLEN.

In a REXX SQLDA, for DECIMAL or NUMERIC columns, Db2 sets the SQLPRECISION and SQLSCALE fields instead of the SQLLEN field.

SQLDATA

The CCSID of a string column. For possible values, see SQLDATA.

In a REXX SQLDA, Db2 sets the SQLCCSID field instead of the SQLDATA field.

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. For more details on unnamed columns, see the discussion of the names of result columns under select-clause. This value is returned in the encoding scheme specified by the ENCODING bind option for the plan or package that contains the statement.

SQLLONGL

For REXX only, the length attribute of a LOB column. Other languages use SQLLONGLEN in the extended SQLVAR.

SQLCCSID

For REXX only, the length attribute of a LOB column.

SQLTNAME

For REXX only, the fully qualified distinct type name of the nth column in the result table. Other languages use SQLDATATYPE-NAME in the extended SQLVAR.

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.

This value is returned in the encoding scheme specified by the ENCODING bind option for the plan or package that contains this statement.

Performance considerations:

Although Db2 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. For details, see Preparing the SQLDA for data retrieval

Using host variables:

If the DESCRIBE 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.

Considerations for implicitly hidden columns:

A DESCRIBE TABLE statement does return information about implicitly hidden columns in tables.