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
>>-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.
CALL SYSPROC.ADMIN_CMD('describe select * from emp_photo')
Result set 1
--------------
SQLTYPE_ID SQLTYPE SQLLENGTH SQLSCALE SQLNAME_DATA ...
---------- ---------..- --------- -------- ------------..- ...
452 CHARACTER 6 0 EMPNO ...
448 VARCHAR 10 0 PHOTO_FORMAT ...
405 BLOB 102400 0 PICTURE ...
3 record(s) selected.
Return Status = 0
... SQLNAME_LENGTH SQLDATATYPENAME_DATA SQLDATATYPENAME_LENGTH
... -------------- ---------------------..- -----------------------
... 5 SYSIBM .CHARACTER 18
... 12 SYSIBM .VARCHAR 16
... 7 SYSIBM .BLOB 13
CALL SYSPROC.ADMIN_CMD('describe table org show detail')
Result set 1
--------------
COLNAME TYPESCHEMA TYPENAME FOR_BINARY_DATA ...
-------...- ----------...- --------...- ---------------...- ...
DEPTNUMB SYSIBM SMALLINT N ...
DEPTNAME SYSIBM VARCHAR N ...
MANAGER SYSIBM SMALLINT N ...
DIVISION SYSIBM VARCHAR N ...
LOCATION SYSIBM VARCHAR N ...
5 record(s) selected.
... LENGTH SCALE NULLABLE COLNO PARTKEYSEQ CODEPAGE DEFAULT
... ------ ----- -------- ----- ---------- -------- -------
... 2 0 N 0 1 0 -
... 14 0 Y 1 0 1208 -
... 2 0 Y 2 0 0 -
... 10 0 Y 3 0 1208 -
... 13 0 Y 4 0 1208 -
Result set 2
--------------
DATA_PARTITION_KEY_SEQ DATA_PARTITION_EXPRESSION
---------------------- --------------------------
0 record(s) selected.
Return Status = 0
CALL SYSPROC.ADMIN_CMD('describe table part_table1 show detail')
Result set 1
--------------
COLNAME TYPESCHEMA TYPENAME FOR_BINARY_DATA ...
-------...- ----------...- -------- --------------- ...
COL1 SYSIBM INTEGER N ...
1 record(s) selected.
... LENGTH SCALE NULLABLE COLNO PARTKEYSEQ CODEPAGE DEFAULT
... ------ ----- -------- ----- ---------- -------- -------
... 4 0 N 0 1 0 -
Result set 2
--------------
DATA_PARTITION_KEY_SEQ DATA_PARTITION_EXPRESSION
---------------------- --------------------------
1 COL1
1 record(s) selected
CALL SYSPROC.ADMIN_CMD('describe indexes for table user1.department')
Result set 1
--------------
INDSCHEMA INDNAME UNIQUE_RULE
----------... ------------------... -------------------
SYSIBM SQL070531145253450 DUPLICATES_ALLOWED
SYSIBM SQL070531145253620 UNIQUE_ENTRIES_ONLY
USER1 RELIDX1 DUPLICATES_ALLOWED
USER1 RELIDX2 DUPLICATES_ALLOWED
SYSIBM SQL070531145253650 PRIMARY_INDEX
USER1 XMLIDX1 DUPLICATES_ALLOWED
SYSIBM SQL070531154625650 DUPLICATES_ALLOWED
USER1 XMLIDX2 DUPLICATES_ALLOWED
SYSIBM SQL070531154626000 DUPLICATES_ALLOWED
USER1 XMLIDX3 DUPLICATES_ALLOWED
SYSIBM SQL070531154626090 DUPLICATES_ALLOWED
USER1 XMLIDX4 DUPLICATES_ALLOWED
SYSIBM SQL070531154626190 DUPLICATES_ALLOWED
USER1 XMLIDX5 DUPLICATES_ALLOWED
SYSIBM SQL070531154626290 DUPLICATES_ALLOWED
USER1 XMLIDX6 DUPLICATES_ALLOWED
SYSIBM SQL070531154626400 DUPLICATES_ALLOWED
USER1 TXTIDX1 -
USER1 TXTIDX2 -
19 record(s) selected.
Return Status = 0
... COLCOUNT INDEXTYPE
... --------- --------------------------
... - XML_DATA_REGIONS
... 1 XML_DATA_PATH
... 1 RELATIONAL_DATA
... 2 RELATIONAL_DATA
... 1 RELATIONAL_DATA
... 1 XML_DATA_VALUES_LOGICAL
... 1 XML_DATA_VALUES_PHYSICAL
... 1 XML_DATA_VALUES_LOGICAL
... 1 XML_DATA_VALUES_PHYSICAL
... 1 XML_DATA_VALUES_LOGICAL
... 1 XML_DATA_VALUES_PHYSICAL
... 1 XML_DATA_VALUES_LOGICAL
... 1 XML_DATA_VALUES_PHYSICAL
... 1 XML_DATA_VALUES_LOGICAL
... 1 XML_DATA_VALUES_PHYSICAL
... 1 XML_DATA_VALUES_LOGICAL
... 1 XML_DATA_VALUES_PHYSICAL
... 1 TEXT_SEARCH
... 1 TEXT_SEARCH
CALL SYSPROC.ADMIN_CMD('describe data partitions for table part_table2')
Result set 1
--------------
DATA_PARTITION_ID LOW_KEY_INCLUSIVE LOW_KEY_VALUE ...
----------------- ----------------- ------------- ...
0 Y 1 ...
1 Y 10 ...
2 Y 20 ...
3 record(s) selected.
... HIGH_KEY_INCLUSIVE HIGH_KEY_VALUE
... ------------------ --------------
... N 10
... N 20
... N 40
CALL SYSPROC.ADMIN_CMD('describe data partitions
for table part_table2 show detail')
Result set 1
--------------
DATA_PARTITION_ID LOW_KEY_INCLUSIVE LOW_KEY_VALUE ...
----------------- ----------------- ------------- ...
0 Y 1 ...
1 Y 10 ...
2 Y 20 ...
3 record(s) selected.
Return Status = 0
... HIGH_KEY_INCLUSIVE HIGH_KEY_VALUE
... ------------------ --------------
... N 10
... N 20
... N 40
Result set 2
--------------
DATA_PARTITION_ID DATA_PARTITION_NAME TBSPID ...
----------------- ------------------- ------ ...
0 PART0 3 ...
1 PART1 3 ...
2 PART2 3 ...
3 record(s) selected.
Return Status = 0
... PARTITION_OBJECT_ID LONG_TBSPID ACCESSMODE STATUS
... ------------------- ----------- ----------- ------
... 15 3 FULL_ACCESS
... 16 3 FULL_ACCESS
... 17 3 FULL_ACCESS
If the DESCRIBE command tries to create a temporary table and fails, creation of SYSTOOLSTMPSPACE is attempted, and then creation of the temporary table is attempted again, this time in SYSTOOLSTMPSPACE. SYSCTRL or SYSADM authority is required to create the SYSTOOLSTMPSPACE table space.
Column name | Data type | LOB only1 | Description |
---|---|---|---|
SQLTYPE_ID | SMALLINT | No | Data type of the column, as it appears in the SQLTYPE field of the SQL descriptor area (SQLDA). |
SQLTYPE | VARCHAR (257) | No | Data type corresponding to the SQLTYPE_ID value. |
SQLLEN | INTEGER | No | Length attribute of the column, as it appears in the SQLLEN field of the SQLDA. |
SQLSCALE | SMALLINT | No | Number of digits in the fractional part of a decimal value; 0 in the case of other data types. |
SQLNAME_DATA | VARCHAR (128) | No | Name of the column. |
SQLNAME_LENGTH | SMALLINT | No | Length of the column name. |
SQLDATA_TYPESCHEMA | VARCHAR (128) | Yes | Data type schema name. |
SQLDATA_TYPENAME | VARCHAR (128) | Yes | Data type name. |
Column name | Data type | Detail2 | Description |
---|---|---|---|
COLNAME | VARCHAR (128) | No | Column name. |
TYPESCHEMA | VARCHAR (128) | No | If the column name is distinct, the schema name is returned, otherwise, 'SYSIBM' is returned. |
TYPENAME | VARCHAR (128) | No | Name of the column type. |
FOR_BINARY_DATA | CHAR (1) | Yes | Returns 'Y' if the column is of type CHAR, VARCHAR or LONG VARCHAR, and is defined as FOR BIT DATA, 'N' otherwise. |
LENGTH | INTEGER | No | Maximum length of the data. For DECIMAL data, this indicates the precision. For discinct types, 0 is returned. |
SCALE | SMALLINT | No | For DECIMAL data, this indicates the scale. For all other types, 0 is returned. |
NULLABLE | CHAR (1) | No | One of:
|
COLNO | SMALLINT | Yes | Ordinal of the column. |
PARTKEYSEQ | SMALLINT | Yes | Ordinal of the column within the table's partitioning key. NULL or 0 is returned if the column is not part of the partitioning key, and is NULL for subtables and hierarchy tables. |
CODEPAGE | SMALLINT | Yes | Code page of the column and is one of:
|
DEFAULT | VARCHAR (254) | Yes | Default value for the column of a table expressed as a constant, special register, or cast-function appropriate for the data type of the column. Might also be NULL. |
Column name | Data type | Description |
---|---|---|
DATA_PARTITION_KEY_SEQ | INTEGER | Data partition key number, for example, 1 for the first data partition expression and 2 for the second data partition expression. |
DATA_PARTITION_EXPRESSION | CLOB (32K) | Expression for this data partition key in SQL syntax |
Column name | Data type | Detail3 | Index type option4, 5 | Description |
---|---|---|---|---|
INDSCHEMA | VARCHAR (128) | No | RELATIONAL DATA |
Index schema name. |
INDNAME | VARCHAR (128) | No | RELATIONAL DATA |
Index name. |
UNIQUE_RULE | VARCHAR (30) | No | RELATIONAL DATA |
One of following values:
|
INDEX_PARTITIONING | CHAR(1) | No | N/A | Identifies the partitioning characteristic of
the index. Possible values are:
|
COLCOUNT | SMALLINT | No | RELATIONAL DATA |
Number of columns in the key, plus the number of include columns, if any. |
INDEX_TYPE | VARCHAR (30) | No | RELATIONAL DATA |
Type of index:
|
INDEX_ID | SMALLINT | Yes | RELATIONAL DATA |
Index ID for a relational data index, an XML path index, an XML regions index, or an index over XML data |
DATA_TYPE | VARCHAR (128) | Yes | XML DATA | SQL data type specified for an index over XML
data.
One of the following values:
|
HASHED | CHAR (1) | Yes | XML DATA | Indicates whether or not the value for an index over XML
data is
hashed.
|
LENGTH | SMALLINT | Yes | XML DATA | For an index over XML data, the VARCHAR (integer) length; 0 otherwise. |
PATTERN | CLOB (2M) | Yes | XML DATA | XML pattern expression specified for an index over XML data |
CODEPAGE | INTEGER | Yes | TEXT SEARCH | Document code page specified for the text search index |
LANGUAGE | VARCHAR (5) | Yes | TEXT SEARCH | Document language specified for the text search index |
FORMAT | VARCHAR (30) | Yes | TEXT SEARCH | Document format specified for a text search index |
UPDATEMINIMUM | INTEGER | Yes | TEXT SEARCH | Minimum number of entries in the text search log table before an incremental update is performed |
UPDATEFREQUENCY | VARCHAR (300) | Yes | TEXT SEARCH | Trigger criterion specified for applying updates to the text index |
COLLECTIONDIRECTORY | VARCHAR (512) | Yes | TEXT SEARCH | Directory specified for the text search index files |
COLNAMES | VARCHAR (2048) | Yes | RELATIONAL DATA |
List of the column names, each preceded with a + to indicate ascending order or a - to indicate descending order. |
Column name | Data type | Detail2 | Description |
---|---|---|---|
DATA_PARTITION_ID | INTEGER | No | Data partition identifier. |
LOW_KEY_INCLUSIVE | CHAR (1) | No | 'Y' if the low key value is inclusive, otherwise, 'N'. |
LOW_KEY_VALUE | VARCHAR (512) | No | Low key value for this data partition. |
HIGH_KEY_INCLUSIVE | CHAR (1) | No | 'Y' if the high key value is inclusive, otherwise, 'N'. |
HIGH_KEY_VALUE | VARCHAR (512) | No | High key value for this data partition. |
Column name | Data type | Description |
---|---|---|
DATA_PARTITION_ID | INTEGER | Data partition identifier. |
DATA_PARTITION_NAME | VARCHAR (128) | Data partition name. |
TBSPID | INTEGER | Identifier of the table space where this data partition is stored. |
PARTITION_OBJECT_ID | INTEGER | Identifier of the DMS object where this data partition is stored. |
LONG_TBSPID | INTEGER | Identifier of the table space where long data is stored. |
INDEX_TBSPID | INTEGER | Identifier of the table space where index data is stored. |
ACCESSMODE | VARCHAR (20) | Defines accessibility of the data partition and is one of:
|
STATUS | VARCHAR(64) | Data partition status and can be one of:
|