Command Reference

DESCRIBE

This command:

• Displays the SQLDA information about a SELECT statement
• Displays columns of a table or a view
• Displays indexes of a table or a view

Authorization

To display the SQLDA information about a SELECT statement, one of the privileges or authorities listed below for each table or view referenced in the SELECT statement is required.

To display the columns or indexes of a table or a view, one of the privileges or authorities listed below for the system catalogs SYSCAT.COLUMNS (DESCRIBE TABLE) and SYSCAT.INDEXES (DESCRIBE INDEXES FOR TABLE) is required:

• SELECT privilege
• CONTROL privilege
• sysadm or dbadm authority

Required Connection

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

Command Syntax

>>-DESCRIBE----------------------------------------------------->
 
>-----+-select-statement---------------------------------------+-><
      '--+-TABLE--table-name--------------+---+--------------+-'
         '-INDEXES FOR TABLE--table-name--'   '-SHOW DETAIL--'
 

Command Parameters

select-statement

Identifies the statement about which information is wanted. The SELECT statement is automatically prepared by CLP.

TABLE table-name

Specifies the table or view to be described. The fully qualified name or alias in the form schema.table-name must be used. The schema is the user name under which the table or view was created.

The DESCRIBE TABLE command lists the following information about each column:

• Column name
• Type schema
• Type name
• Length
• Scale
• Nulls (yes/no)

Note:

This option is not supported for declared temporary tables.

INDEXES FOR TABLE table-name

Specifies the table or view for which indexes need to be described. The fully qualified name or alias in the form schema.table-name must be used. The schema is the user name under which the table or view was created.

The DESCRIBE INDEXES FOR TABLE command lists the following information about each index of the table or view:

• Index schema
• Index name
• Unique rule
• Column count

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
• Partitioning key sequence
• Code page
• Default

For the DESCRIBE INDEXES FOR TABLE command, specifies that output include the following additional information:

• Column names

Examples

Describing a SELECT Statement

The following example shows how to describe a SELECT statement:

   db2 "describe select * from staff"

SQLDA Information sqldaid : SQLDA sqldabc: 896 sqln: 20 sqld: 7 Column Information sqltype sqllen sqlname.data sqlname.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 a Table

The following example shows how to describe a table:

   db2 describe table user1.department

Table: USER1.DEPARTMENT Column Type Type name schema name Length Scale Nulls ------------------ ----------- ------------------ -------- -------- -------- AREA SYSIBM SMALLINT 2 0 No DEPT SYSIBM CHARACTER 3 0 No DEPTNAME SYSIBM CHARACTER 20 0 Yes

Describing a Table Index

The following example shows how to describe a table index:

   db2 describe indexes for table user1.department

Table: USER1.DEPARTMENT Index Index Unique Number of schema name rule columns -------------- ------------------ -------------- -------------- USER1 IDX1 U 2