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.
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:
|
Output of a CALL statement | Any of the following privileges or authorities:
|
Columns of a table or a view | Any of the following privileges or authorities
for the SYSCAT.COLUMNS system catalog table:
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:
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:
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. |
Database. If implicit connect is enabled, a connection to the default database is established.
>>-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--------------'
Specify an index type to list information for only a specific index type. Specifying multiple index types is not supported.
If SHOW DETAIL is specified, the column names information is also listed.
If SHOW DETAIL is specified, the following information for an index over XML data is also listed:
If SHOW DETAIL is specified, the following text search index information is also listed:
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.
Describing the output of 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
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
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
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
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
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
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
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
db2 describe text search indexes for table user1.department
Index Index
schema name
-------------- ------------------
USER1 TXTIDX1
USER1 TXTIDX2
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
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
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