DESCRIBE TABLE

The DESCRIBE TABLE statement obtains information about a designated table or view.

Invocation

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

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)
  • Start of changeACCESSCTRL authority (catalog tables only)End of change
  • Start of changeSystem DBADMEnd of change
  • Start of changeDATAACCESS authorityEnd of change
  • Start of changeEXPLAIN authorityEnd of change
  • Start of changeSQLADM authorityEnd of change

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.

Syntax

Read syntax diagram
>>-DESCRIBE--TABLE--host-variable--INTO--descriptor-name-------->

>--+-------------------+---------------------------------------><
   |        .-NAMES--. |   
   '-USING--+-LABELS-+-'   
            +-ANY----+     
            '-BOTH---'     

Description

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 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 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

See DESCRIBE OUTPUT for information about the following topics:

  • Allocating the SQLDA
  • The SQLDA contents that are returned on DESCRIBE
  • Performance considerations for DESCRIBE
  • Using host variables in DESCRIBE statements

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