DESCRIBE CLPPlus command
The DESCRIBE CLPPlus command displays a list of columns and their data types and lengths for a table view; a list of parameters for a procedure or function; or a list of procedures and functions and their parameters for a package.
The DESCRIBE CLPPlus command allows you to specify type of database object you want to describe. If you do not specify the type of object you want to describe, then all objects that are found with the given name and schema are described. The default schema is CURRENT SCHEMA.
The DESCRIBE CLPPlus command supports temporal tables. Temporal tables are new for Db2® for z/OS® Version 10.
Invocation
You can run this command from the CLPPlus interface.
Authorization
None
Required connection
You must be connected to a database.
Command syntax
Command parameters
-
TABLE
- Specifies that the type of database object to be described is a table.
-
VIEW
- Specifies that the type of database object to be described is a view.
-
ALIAS
- Specifies that the type of database object to be described is a alias.
-
PROCEDURE
- Specifies that the type of database object to be described is a procedure.
-
FUNCTION
- Specifies that the type of database object to be described is a function.
-
SYNONYM
- Specifies that the type of database object to be described is a synonym.
-
PACKAGE
- Specifies that the type of database object to be described is a package.
-
MODULE
- Specifies that the type of database object to be described is a module.
-
schema
- Specifies the name of the schema containing an object to be described. The default schema is CURRENT SCHEMA.
-
object
- Specifies the name of a table, view, procedure, function, or package to be described.
Example
In the following example, the DESCRIBE command is run to get details on the table named ABCD.
SQL> DESCRIBE TABLE ABCD ;
TABLE - ABCD
Name Data Type Type schema Length Scale Nulls Hidden
-------------------- --------------- --------------- -------- -------- ----- --------
ROLL INTEGER SYSIBM 4 0 Y N
NAME CHARACTER SYSIBM 10 0 Y P
In the following example, the DESCRIBE command is run to get details on a bitemporal table.
SQL > create table policy
(
policy_id int NOT NULL,
coverage int NOT NULL IMPLICITLY HIDDEN,
bus_start date NOT NULL,
bus_end date NOT NULL,
system_start TIMESTAMP(12) generated always as row begin NOT NULL,
system_end TIMESTAMP(12) generated always as row end NOT NULL,
trans_start generated always as transaction start ID,
period BUSINESS_TIME(bus_start, bus_end),
period SYSTEM_TIME (system_start, system_end)
);
DB250000I: The command completed successfully.
SQL> create table policy_hist LIKE policy;
DB250000I: The command completed successfully.
SQL> ALTER TABLE policy ADD VERSIONING USE HISTORY TABLE policy_hist;
DB250000I: The command completed successfully.
SQL> describe policy
TABLE - POLICY
********************************************************************************
Name Data Type Type schema Length Scale Nulls Hidden
------------------- ------------- ------------- -------- ------- ----- --------
POLICY_ID INTEGER SYSIBM 4 0 N Not
COVERAGE INTEGER SYSIBM 4 0 N Implicit
BUS_START TIMESTAMP SYSIBM 7 0 N Not
BUS_END TIMESTAMP SYSIBM 7 0 N Not
SYSTEM_START TIMESTAMP SYSIBM 13 12 N Not
SYSTEM_END TIMESTAMP SYSIBM 13 12 N Not
TRANS_START TIMESTAMP SYSIBM 13 12 Y Not
Temporal Type : Bitemporal
Table is versioned and has the following periods
---------------------------------------------------------
Name Type Begin Column End Column
-------------------- ---- --------------- ---------------
SYSTEM_TIME S SYSTEM_START SYSTEM_END
BUSINESS_TIME A BUS_START BUS_END
********************************************************************************In the following example, the DESCRIBE command is run to get details on a table with system period, but not versioned.
SQL> create table demo_nontemp
(
policy_id int NOT NULL,
coverage int NOT NULL IMPLICITLY HIDDEN,
system_start TIMESTAMP(12) generated always as row begin NOT NULL,
system_end TIMESTAMP(12) generated always as row end NOT NULL,
trans_start generated always as transaction start ID,
period SYSTEM_TIME (system_start, system_end)
);
DB250000I: The command completed successfully.
SQL> describe demo_nontemp
TABLE - TEMPTAB
********************************************************************************
Name Data Type Type schema Length Scale Nulls Hidden
------------------- ------------- ------------- -------- ------- ----- --------
POLICY_ID INTEGER SYSIBM 4 0 N Not
COVERAGE INTEGER SYSIBM 4 0 N Implicit
SYSTEM_START TIMESTAMP SYSIBM 13 12 N Not
SYSTEM_END TIMESTAMP SYSIBM 13 12 N Not
TRANS_START TIMESTAMP SYSIBM 13 12 Y Not
Table has the following periods
---------------------------------------------------------
Name Type Begin Column End Column
-------------------- ---- --------------- ---------------
SYSTEM_TIME S SYSTEM_START SYSTEM_END
********************************************************************************In the following example, the DESCRIBE command is run to get details on a application period temporal table.
SQL> create table demo_app
(
policy_id int NOT NULL,
coverage int NOT NULL IMPLICITLY HIDDEN,
bus_start date NOT NULL,
bus_end date NOT NULL,
period BUSINESS_TIME(bus_start, bus_end));
DB250000I: The command completed successfully.
SQL> describe demo_app
TABLE - DEMO_APP
********************************************************************************
Name Data Type Type schema Length Scale Nulls Hidden
------------------- ------------- ------------- -------- ------- ----- --------
POLICY_ID INTEGER SYSIBM 4 0 N Not
COVERAGE INTEGER SYSIBM 4 0 N Implicit
BUS_START TIMESTAMP SYSIBM 7 0 N Not
BUS_END TIMESTAMP SYSIBM 7 0 N Not
Temporal Type : Application period temporal
Table has the following periods
---------------------------------------------------------
Name Type Begin Column End Column
-------------------- ---- --------------- ---------------
BUSINESS_TIME A BUS_START BUS_END
********************************************************************************In the following example, the DESCRIBE command is run to get details on a system period temporal table.
SQL> create table demo_sys
(
policy_id int NOT NULL,
coverage int NOT NULL IMPLICITLY HIDDEN,
system_start TIMESTAMP(12) generated always as row begin NOT NULL,
system_end TIMESTAMP(12) generated always as row end NOT NULL,
trans_start generated always as transaction start ID,
period SYSTEM_TIME (system_start, system_end)
);
DB250000I: The command completed successfully.
SQL> create table demo_sys_history like demo_sys ;
DB250000I: The command completed successfully.
SQL> ALTER TABLE DEMO_SYS ADD VERSIONING USE HISTORY TABLE DEMO_SYS_HISTORY;
DB250000I: The command completed successfully.
SQL> desc demo_sys
TABLE - DEMO_SYS
********************************************************************************
Name Data Type Type schema Length Scale Nulls Hidden
------------------- ------------- ------------- -------- ------- ----- --------
POLICY_ID INTEGER SYSIBM 4 0 N Not
COVERAGE INTEGER SYSIBM 4 0 N Implicit
SYSTEM_START TIMESTAMP SYSIBM 13 12 N Not
SYSTEM_END TIMESTAMP SYSIBM 13 12 N Not
TRANS_START TIMESTAMP SYSIBM 13 12 Y Not
Temporal Type : System period temporal
Table is versioned and has the following periods
---------------------------------------------------------
Name Type Begin Column End Column
-------------------- ---- --------------- ---------------
SYSTEM_TIME S SYSTEM_START SYSTEM_END
********************************************************************************