DESCRIBE command using the ADMIN_CMD procedure
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 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
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 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
- EXECUTE privilege on each routine
- READ privilege on each global variable
- USAGE privilege on each sequence
- EXECUTE privilege on each module
OUT and INOUT parameters 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 previously listed authorities.
Required connection
Database
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 as well as a second result set which contains the table data partition expressions (which might return 0 rows if the table is not data partitioned):
- 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:
CALL SYSPROC.ADMIN_CMD('describe select * from emp_photo')The following is an example of output for this SELECT statement.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 = 0Output for this SELECT statement (continued).... SQLNAME_LENGTH SQLDATATYPENAME_DATA SQLDATATYPENAME_LENGTH ... -------------- ---------------------..- ----------------------- ... 5 SYSIBM .CHARACTER 18 ... 12 SYSIBM .VARCHAR 16 ... 7 SYSIBM .BLOB 13 - Describing a table
-
Describing a non-partitioned table.
CALL SYSPROC.ADMIN_CMD('describe table org show detail')The following is an example of output for this CALL statement.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.Output for this CALL statement (continued).... 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 -Output for this CALL statement (continued).Result set 2 -------------- DATA_PARTITION_KEY_SEQ DATA_PARTITION_EXPRESSION ---------------------- -------------------------- 0 record(s) selected. Return Status = 0Describing a partitioned table.CALL SYSPROC.ADMIN_CMD('describe table part_table1 show detail')The following is an example of output for this CALL statement.Result set 1 -------------- COLNAME TYPESCHEMA TYPENAME FOR_BINARY_DATA ... -------...- ----------...- -------- --------------- ... COL1 SYSIBM INTEGER N ... 1 record(s) selected.Output for this CALL statement (continued).... LENGTH SCALE NULLABLE COLNO PARTKEYSEQ CODEPAGE DEFAULT ... ------ ----- -------- ----- ---------- -------- ------- ... 4 0 N 0 1 0 -Output for this CALL statement (continued).Result set 2 -------------- DATA_PARTITION_KEY_SEQ DATA_PARTITION_EXPRESSION ---------------------- -------------------------- 1 COL1 1 record(s) selected - Describing a table index
-
The following example shows how to describe a table index. This call describes table USER1.DEPARTMENT and lists two relational data indexes, six xml data indexes, two text search indexes, and the system indexes:
CALL SYSPROC.ADMIN_CMD('describe indexes for table user1.department')The following is an example of output for this CALL statement.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 = 0Output for this CALL statement (continued).... 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 - Describing index with an expression-based key.
-
Given an expression-based index created with the statement:
CREATE INDEX emp_comp ON staff(UPPER(name), salary+bonus, id)Note: The Column names column of the DESCRIBE command returns the following output for the previous statement:+K00+K01+ID
where K00 and K01 will include the following information:+K00 [UPPER(name)] + K01 [salary + bonus] + id
The following example shows how to describe a expression-based index.CALL SYSPROC.ADMIN_CMD('describe indexes for table staff show detail') - Describing a data partition
-
The following example shows how to describe data partitions.
CALL SYSPROC.ADMIN_CMD('describe data partitions for table part_table2')The following is an example of output for this CALL statement.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.Output for this CALL statement (continued).... HIGH_KEY_INCLUSIVE HIGH_KEY_VALUE ... ------------------ -------------- ... N 10 ... N 20 ... N 40The following example shows how to describe data partitions with 'SHOW DETAIL' clause.CALL SYSPROC.ADMIN_CMD('describe data partitions for table part_table2 show detail')The following is an example of output for this CALL statement.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 = 0Output for this CALL statement (continued).... HIGH_KEY_INCLUSIVE HIGH_KEY_VALUE ... ------------------ -------------- ... N 10 ... N 20 ... N 40Output for this CALL statement (continued).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 = 0Output for this CALL statement (continued).... PARTITION_OBJECT_ID LONG_TBSPID ACCESSMODE STATUS ... ------------------- ----------- ----------- ------ ... 15 3 FULL_ACCESS ... 16 3 FULL_ACCESS ... 17 3 FULL_ACCESS
Usage note
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.
Result set information
Command execution status is returned in the SQLCA resulting from the CALL statement. If execution
is successful, the commands return additional information in result sets as follows:
- Table 1: DESCRIBE select-statement, DESCRIBE call-statement and DESCRIBE XQUERY XQuery-statement commands
- Table 2: Result set 1 for the DESCRIBE TABLE command
- Table 3: Result set 2 for the DESCRIBE TABLE command
- Table 4: DESCRIBE INDEXES FOR TABLE command
- Table 5: Result set 1 for the DESCRIBE DATA PARTITIONS FOR TABLE command
- Table 6: Result set 2 for the DESCRIBE DATA PARTITIONS FOR TABLE command
| 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. |
Note: 1: Yes indicates that non-null values are returned only when there is LOB data
being described.
| 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. |
Note: 2: Yes indicates that non-null values are returned only when the SHOW
DETAIL clause is used.
| 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
XML DATA TEXT SEARCH |
Index schema name. |
| INDNAME | VARCHAR (128) | No |
RELATIONAL DATA
XML DATA TEXT SEARCH |
Index name. |
| UNIQUE_RULE | VARCHAR (30) | No |
RELATIONAL DATA
XML 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
XML DATA |
Number of columns in the key, plus the number of include columns, if any. |
| INDEX_TYPE | VARCHAR (30) | No |
RELATIONAL DATA
XML DATA TEXT SEARCH |
Type of index:
|
| INDEX_ID | SMALLINT | Yes |
RELATIONAL DATA
XML 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
XML DATA TEXT SEARCH |
List of the column names, each preceded with a + to indicate ascending order or a - to indicate descending order. |
Note: 3: Yes indicates that values are returned only when the SHOW
DETAIL clause is used without specifying an index type option. Values might be
NULL.
Note: 4: Indicates the values returned when using DESCRIBE
index-type
INDEXES FOR TABLE. For example, INDEX_ID values are not returned if TEXT
SEARCH is specified as index-type. INDEX_ID values are returned if
either RELATIONAL DATA or XML DATA are specified.
Note: 5: When using DESCRIBE
index-type
INDEXES FOR TABLE
SHOW DETAIL, the values are returned only when the index type is listed. For
example, DATA_TYPE values are returned if XML DATA is specified as
index-type. DATA_TYPE values are not returned if either TEXT
SEARCH or RELATIONAL DATA is specified as
index-type.
| 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. |
Note: 2: Yes indicates that non-null values are returned only when the SHOW
DETAIL clause is used.
| 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:
|