DB2 Version 9.7 for Linux, UNIX, and Windows

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.

Use the DESCRIBE command to display information about any of the following items:
  • Output of a SELECT, CALL, or XQuery statement
  • Columns of a table or a view
  • Indexes of a table or a view
  • Data partitions of a table or view

Authorization

The authorization required depends on the type of information you want to display using the DESCRIBE command.
  • 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 Any of the following privileges or authorities for each table or view referenced in the SELECT statement:
    • SELECT privilege
    • DATAACCESS authority
    • DBADM authority
    • SQLADM authority
    • EXPLAIN authority
    Output of a CALL statement Any of the following privileges or authorities:
    • DATAACCESS authority
    • EXECUTE privilege on 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
    • 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
    • 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
    • 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 above authorities.

Required connection

Database. If implicit connect is enabled, a connection to the default database is established.

Command syntax

Read syntax diagramSkip visual syntax diagram
>>-DESCRIBE----------------------------------------------------->

     .-OUTPUT-.                                                                  
>--+-+--------+--+-select-statement---------+--------------------------------+-><
   |             +-call-statement-----------+                                |   
   |             '-XQUERY--XQuery-statement-'                                |   
   '-+-TABLE----------------------------------+--table-name--+-------------+-'   
     +-+-----------------+--INDEXES FOR TABLE-+              '-SHOW DETAIL-'     
     | +-RELATIONAL DATA-+                    |                                  
     | +-XML DATA--------+                    |                                  
     | '-TEXT SEARCH-----'                    |                                  
     '-DATA PARTITIONS FOR TABLE--------------'                                  

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

Specify an index type to list information for only a specific index type. Specifying multiple index types is not supported.

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

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
  • Default
  • Table partitioning type (for tables partitioned by range this output appears below the original output)
  • Partitioning key columns (for tables partitioned by range this output appears below the original output)
  • Identifier of table space used for the index

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 output of a CALL Statement

Given a stored procedure created with the statement:
   CREATE PROCEDURE GIVE_BONUS (IN EMPNO INTEGER,
                             IN DEPTNO INTEGER,
                             OUT CHEQUE INTEGER,
                             INOUT BONUS DEC(6,0))
...
The following example shows how to describe the output of a CALL statement:
 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 output 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 downlevel server that does not support the XQUERY option, the message DB21108E is returned to indicate that the functionality is not supported by the downlevel 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 below 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
name               schema      number    name        length 
------------------ ----------- --------- ----------- --------
FIRST              SYSIBM              0 CHARACTER         10 
LAST               SYSIBM              1 CHARACTER         10


Table is partitioned by range (ordered on the following column/s):
------------------------------------------------------------------
LAST
FIRST

Describing a Table Index

The following examples 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
schema          name                 rule            columns
--------------  ------------------   --------------  -------------
SYSIBM          SQL070531145253650   P                           1
USER1           RELIDX1              D                           1
USER1           RELIDX2              D                           2
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