DESCRIBE command
The DESCRIBE command displays metadata about the columns, indexes, and data partitions of tables or views. This command can also display metadata about the output of SELECT, CALL, or XQuery statements.
- Output of a SELECT or XQuery statement
- OUT and INOUT parameters of a CALL statement
- Columns of a table or a view
- Indexes of a table or a view
- Data partitions of a table or view
Authorization
- If the SYSTOOLSTMPSPACE table space exists, one of the authorities shown in
the following table is required.
Object to display information about Privileges or authorities required Output of a SELECT statement or XQuery statement At least one of the following authorizations: - DATAACCESS authority
- DBADM authority
- SQLADM authority
- EXPLAIN authority
- For each object referenced in the statement:
- SELECT privilege on each table, view or nickname
- SELECTIN privilege on the schema containing the table, view, or nickname
- EXECUTE privilege on each routine
- READ privilege on each global variable
- USAGE privilege on each sequence
- EXECUTE privilege on each module
- DATAACCESS on the schema containing the object
OUT and INOUT parameters of a CALL statement Any of the following privileges or authorities: - DATAACCESS authority
- EXECUTE privilege on the stored procedure
- EXECUTEIN privilege on the schema containing the stored procedure
- DATAACCESS authority on the schema containing the stored procedure
Columns of a table or a view Any of the following privileges or authorities for the SYSCAT.COLUMNS system catalog table: - SELECT privilege
- SELECTIN privilege on SYSCAT
- ACCESSCTRL authority
- DATAACCESS authority
- DBADM authority
- SECADM authority
- SQLADM authority
If you want to use the SHOW DETAIL parameter, you also require any of these privileges or authorities on the SYSCAT.DATAPARTITIONEXPRESSION system catalog table.
Because PUBLIC has all the privileges over declared temporary tables, you can use the command to display information about any declared temporary table that exists within your connection.
Indexes of a table or a view Any of the following privileges or authorities on the SYSCAT.INDEXES system catalog table: - SELECT privilege
- SELECTIN privilege
- ACCESSCTRL authority
- DATAACCESS authority
- DBADM authority
- SECADM authority
- SQLADM authority
If you want to use the SHOW DETAIL parameter, you also require EXECUTE privilege on the GET_INDEX_COLNAMES() UDF.
Because PUBLIC has all the privileges over declared temporary tables, you can use the command to display information about any declared temporary table that exists within your connection.
Data partitions of a table or view Any of the following privileges or authorities on the SYSCAT.DATAPARTITIONS system catalog table: - SELECT privilege
- SELECTIN privilege
- ACCESSCTRL authority
- DATAACCESS authority
- DBADM authority
- SECADM authority
- SQLADM authority
Because PUBLIC has all the privileges over declared temporary tables, you can use the command to display information about any declared temporary table that exists within your connection.
- If the SYSTOOLSTMPSPACE table space does not exist, SYSADM or SYSCTRL authority is also required in addition to the one of the previously listed authorities.
Required connection
Database. If implicit connect is enabled, a connection to the default database is established.
Command syntax
Command parameters
- OUTPUT
- Indicates that the output of the statement should be described. This keyword is optional.
- select-statement | call-statement | XQUERY XQuery-statement
- Identifies the statement about which information is wanted. The statement is automatically prepared by CLP. To identify an XQuery statement, precede the statement with the keyword XQUERY. A DESCRIBE OUTPUT statement only returns information about an implicitly hidden column if the column is explicitly specified as part of the SELECT list of the final result table of the query described.
- TABLE table-name
- Specifies the table or view to be described. The fully qualified name in the form
schema.table-name must be used. An alias for the table cannot
be used in place of the actual
table.
Information about implicitly hidden columns is returned, but SHOW DETAIL must be used to indicate
which columns are implicitly hidden. The DESCRIBE TABLE command lists the following information about each column:
- Column name
- Type schema
- Type name
- Length
- Scale
- Nulls (yes/no)
- INDEXES FOR TABLE table-name
- Specifies the table or view for which indexes need to be described. You can use the fully
qualified name in the form schema.table-name or you can just
specify the table-name and default schema will be used automatically. An alias
for the table cannot be used in place of the actual table.
The DESCRIBE INDEXES FOR TABLE command lists the following information about each index of the table or view:
- Index schema
- Index name
- Unique rule
- Number of columns
- Index type
If the DESCRIBE INDEXES FOR TABLE command is specified with the SHOW DETAIL option, the index name is truncated when the index name is greater than 18 bytes. If no index type option is specified, information for all index types is listed: relational data index, index over XML data, and Text Search index. The output includes the following additional information:- Index ID for a relational data index, an XML path index, an XML regions index, or an index over XML data
- Data Type for an index over XML data
- Hashed for an index over XML data
- Max VARCHAR Length for an index over XML data
- XML Pattern specified for an index over XML data
- Codepage for a text search index
- Language for a text search index
- Format specified for a text search index
- Update minimum for a text search index
- Update frequency for a text search index
- Collection directory for a text search index
- Column names of the indexes that are preceded by
+
for an ascending order,-
for a descending order, and*
for a random order. - Expressions in square brackets following the generated column name for any part of an index key that is based on an expression.
- Whether the BUSINESS_TIME WITHOUT OVERLAPS clause is specified
Specify an index type to list information for only a specific index type. Specifying multiple index types is not supported.
Column name is truncated when it is greater than 8256 bytes.
- RELATIONAL DATA
- If the RELATIONAL DATA index type option is specified without the
SHOW DETAIL option, only the following information is listed:
- Index schema
- Index name
- Unique rule
- Number of columns
- Null keys
If SHOW DETAIL is specified, the column names information is also listed.
- XML DATA
- If the XML DATA index type option is specified without the SHOW
DETAIL option, only the following information is listed:
- Index schema
- Index name
- Unique rule
- Number of columns
- Index type
If SHOW DETAIL is specified, the following information for an index over XML data is also listed:
- Index ID
- Data type
- Hashed
- Max Varchar length
- XML Pattern
- Column names
- TEXT SEARCH
- If the TEXT SEARCH index type option is specified without the
SHOW DETAIL option, only the following information is listed:
- Index schema
- Index name
If SHOW DETAIL is specified, the following text search index information is also listed:
- Column name
- Codepage
- Language
- Format
- Update minimum
- Update frequency
- Collection directory
If the TEXT SEARCH option is specified and a text search option is not installed or not properly configured, an error (SQLSTATE 42724) is returned.
See Db2® Text Search for information listed in the columns.
- DATA PARTITIONS FOR TABLE table-name
- Specifies the table or view for which data partitions need to be described. The information
displayed for each data partition in the table includes; the partition identifier and the
partitioning intervals. Results are ordered according to the partition identifier sequence. The
fully qualified name in the form schema.table-name must be
used. An alias for the table cannot be used in place of the actual table. The
schema is the user name under which the table or view was created.For the DESCRIBE DATA PARTITIONS FOR TABLE command, specifies that output include a second table with the following additional information:
- Data partition sequence identifier
- Data partition expression in SQL
- SHOW DETAIL
-
For the DESCRIBE TABLE command, specifies that output include the following additional information:
- Whether a CHARACTER, VARCHAR or LONG VARCHAR column was defined as FOR BIT DATA
- Column number
- Distribution key sequence
- Code page
- Hidden attribute
- Default
- Table partitioning type (for tables partitioned by range this output appears after the original output)
- Partitioning key columns (for tables partitioned by range this output appears after the original output)
- Identifier of table space used for the index
- Periods that are defined on the table (for temporal tables this output appears after the original output)
- Whether versioning is enabled on the table (for temporal tables this output appears after the original output)
Examples
- Describing the output of a SELECT Statement
-
The following example shows how to describe a SELECT statement:
db2 describe output select * from staff
Column Information Number of columns: 7 Data Type Length Column Name Name Length -------------------- ------ ------------------------------ -------------- 500 SMALLINT 2 ID 2 449 VARCHAR 9 NAME 4 501 SMALLINT 2 DEPT 4 453 CHARACTER 5 JOB 3 501 SMALLINT 2 YEARS 5 485 DECIMAL 7,2 SALARY 6 485 DECIMAL 7,2 COMM 4
- Describing the OUT and INOUT parameters of a CALL Statement
-
Given a stored procedure created with the statement:
The following example shows how to describe the OUT and INOUT of a CALL statement:CREATE PROCEDURE GIVE_BONUS (IN EMPNO INTEGER, IN DEPTNO INTEGER, OUT CHEQUE INTEGER, INOUT BONUS DEC(6,0)) ...
db2 describe output call give_bonus(123456, 987, ?, 15000.) Column Information Number of Columns: 2 Data Type Length Column Name Name Length -------------------- ------ ------------------------------ -------------- 497 INTEGER 4 CHEQUE 6 485 DECIMAL 6, 0 BONUS 5
If the procedure has one or more parameters of an array type, the output from the DESCRIBE command has one additional column, that indicates the maximum cardinality of array parameters. An empty value indicates that the parameter is not an array.
Given the array type and procedure created with the statements:
CREATE TYPE PRODUCT_LIST AS INTEGER ARRAY[100] CREATE TYPE CUSTOMER_LIST AS INTEGER ARRAY[1000] CREATE PROCEDURE DISCONTINUE_PROD (IN PROD_LIST PRODUCT_LIST, IN EFFECTIVE_DATE DATE, OUT NUM_PENDING_ORDERS INTEGER, OUT CUST_LIST CUSTOMER_LIST) ...
The following example shows how to describe the OUT and INOUT parameters of a CALL statement with array parameters. The only format difference with the previous example is the
Max cardinality
column.db2 describe output call discontinue_prod(ARRAY[12, 34, 26],'04/13/2006',?) Column Information Number of Columns: 2 SQL type Type length Column name Name length Max cardinality -------------------- ----------- ------------------------------ -------------- --------------- 497 INTEGER 4 NUM_PENDING_ORDERS 17 497 INTEGER 10 CUSTOMER_LIST 13 1000
- Describing the output of an XQuery Statement
-
Given a table named CUSTOMER that has a column named INFO of the XML data type, the following example shows how to describe an XQuery statement:
db2 describe xquery for $cust in db2-fn:xmlcolumn("CUSTOMER.INFO") return $cust
Column Information Number of Columns: 1 SQL type Type length Column name Name length -------------------- ----------- ------------------------------ -------------- 998 XML 0 1 1
If the keyword XQUERY is not specified, SQL0104N is returned.
db2 describe for $cust in db2-fn:xmlcolumn("CUSTOMER.INFO") return $cust SQL0104N An unexpected token "for" was found following "DESCRIBE". Expected tokens may include: "OUTPUT". SQLSTATE=42601
If the DESCRIBE XQUERY command is issued against a previous server that does not support the XQUERY option, the message DB21108E is returned to indicate that the functionality is not supported by the previous server.
- Describing a Table
-
The following example shows how to describe a table:
db2 describe table user1.department
Table: USER1.DEPARTMENT Column Data Type Data Type Column name schema name length Scale Nulls ------------------ ----------- ------------------ -------- -------- -------- AREA SYSIBM SMALLINT 2 0 No DEPT SYSIBM CHARACTER 3 0 No DEPTNAME SYSIBM CHARACTER 20 0 Yes
The following example shows how to describe a table with details. If the table is partitioned, as in this example, additional details appear after the existing output. For a non-partitioned table, the additional table heading is not displayed:db2 describe table user1.employee show detail
Column Data Type Column Data Type Column Hidden name schema number name length ------------------ ----------- --------- ----------- -------- ---------- FIRST SYSIBM 0 CHARACTER 10 No LAST SYSIBM 1 CHARACTER 10 No EMPLOYEENUM SYSTEM 2 CHARACTER 10 Implicitly Table is partitioned by range (ordered on the following column/s): ------------------------------------------------------------------ LAST FIRST EMPLOYEENUM
- Describing a Table Index
-
The following example shows how to describe a table index. This command lists two relational data indexes, six xml data indexes, two text search indexes, and the system indexes:
db2 describe indexes for table user1.department
Index Index Unique Number of Index schema name rule columns type -------------- ------------------ -------------- ------------- -------------- SYSIBM SQL070531145253450 D - XML DATA - REGIONS SYSIBM SQL070531145253620 U 1 XML DATA - PATH USER1 RELIDX1 D 1 RELATIONAL DATA USER1 RELIDX2 D 2 RELATIONAL DATA SYSIBM SQL070531145253650 P 1 RELATIONAL DATA USER1 XMLIDX1 D 1 XML DATA - VALUES LOGICAL SYSIBM SQL070531154625650 D 1 XML DATA - VALUES PHYSICAL USER1 XMLIDX2 D 1 XML DATA - VALUES LOGICAL SYSIBM SQL070531154626000 D 1 XML DATA - VALUES PHYSICAL USER1 XMLIDX3 D 1 XML DATA - VALUES LOGICAL SYSIBM SQL070531154626090 D 1 XML DATA - VALUES PHYSICAL USER1 XMLIDX4 D 1 XML DATA - VALUES LOGICAL SYSIBM SQL070531154626190 D 1 XML DATA - VALUES PHYSICAL USER1 XMLIDX5 D 1 XML DATA - VALUES LOGICAL SYSIBM SQL070531154626290 D 1 XML DATA - VALUES PHYSICAL USER1 XMLIDX6 D 1 XML DATA - VALUES LOGICAL SYSIBM SQL070531154626400 D 1 XML DATA - VALUES PHYSICAL USER1 TXTIDX1 - 1 TEXT SEARCH USER1 TXTIDX2 - 1 TEXT SEARCH
The following command lists the relational data indexes for table USER1.DEPARTMENT:db2 describe relational data indexes for table user1.department
Index Index Unique Number of Null schema name rule columns keys -------------- ------------------ -------------- ------------- ---- SYSIBM SQL070531145253650 P 1 Y USER1 RELIDX1 D 1 N USER1 RELIDX2 D 2 Y
The following command lists the indexes over XML data for table USER1.DEPARTMENT:db2 describe xml data indexes for table user1.department
Index Index Unique Number of Index schema name rule columns type -------------- ------------------ -------------- ------------- -------------- SYSIBM SQL070531145253450 D - XML DATA - REGIONS SYSIBM SQL070531145253620 U 1 XML DATA - PATH USER1 XMLIDX1 D 1 XML DATA - VALUES LOGICAL SYSIBM SQL070531154625650 D 1 XML DATA - VALUES PHYSICAL USER1 XMLIDX2 D 1 XML DATA - VALUES LOGICAL SYSIBM SQL070531154626000 D 1 XML DATA - VALUES PHYSICAL USER1 XMLIDX3 D 1 XML DATA - VALUES LOGICAL SYSIBM SQL070531154626090 D 1 XML DATA - VALUES PHYSICAL USER1 XMLIDX4 D 1 XML DATA - VALUES LOGICAL SYSIBM SQL070531154626190 D 1 XML DATA - VALUES PHYSICAL USER1 XMLIDX5 D 1 XML DATA - VALUES LOGICAL SYSIBM SQL070531154626290 D 1 XML DATA - VALUES PHYSICAL USER1 XMLIDX6 D 1 XML DATA - VALUES LOGICAL SYSIBM SQL070531154626400 D 1 XML DATA - VALUES PHYSICAL
The following command lists the text search index information for table USER1.DEPARTMENT:db2 describe text search indexes for table user1.department
Index Index schema name -------------- ------------------ USER1 TXTIDX1 USER1 TXTIDX2
The following command lists information about both partitioned and nonpartitioned indexes on the partitioned table myDpartT:db2 describe indexes for table myDPartT
Index Index Unique Number of Index Partitioning schema name rule columns ------------------------------- ------------------- -------------- -------------- ------------- NEWTON IDXNDP D 1 N NEWTON IDXDP D 1 P
- Describing Data Partitions
-
The following example shows how to describe data partitions:
db2 describe data partitions for table user1.sales
PartitionId Inclusive (y/n) Inclusive (y/n) Low Value High Value ------------- -- -------------- -- ------------- 0 Y 2001,1 Y 2001,3 1 N 2001,3 Y 2001,6 3 N 2001,6 Y 2001,9
Describing the data partitions with details returns the same output, as in the previous example, and includes an additional table showing the Partition ID and table space where the data for the data partition is stored, and the ID of the table space where the index is stored:db2 describe data partitions for table user1.employee show detail
PartitionId Inclusive (y/n) Inclusive (y/n) Low Value High Value ------------- -- ------------------ -- ------------- 0 Y MINVALUE,MINVALUE Y 'beck','kevin' 1 N 'beck','kevin' N 'treece','jeff' 2 Y 'treece','jeff' Y 'zhang','liping' 3 Y 'zyzyck',MINVALUE Y MAXVALUE,MAXVALUE PartitionId PartitionName TableSpId LongTblSpId IndexTblSpId AccessMode Status ----------- ------------- --------- ----------- ------------ ---------- ------ 0 PARTx 3 43 50 F 1 PARTNew 13 13 13 N A 2 PART3 31 33 35 F 3 PART4 23 34 23 N A