DB2 Describe Table

Updated March 6, 2023

Introduction to DB2 Describe Table

DB2 provides the different types of commands to the user, the DB2 describe table is one of the commands that is provided by the DB2. Basically describe table command is used to fetch all details structure of a created table that means it displays the information about the columns, index of table or view as well as it is also used in select, call statement to display some basic information about the specified table.db2 describe table it is also useful in data partition of different tables and view. As per user requirements, we can use the describe table command to see the detailed structure of the user-defined table.

Syntax:

describe select * from the specified table name;

Explanation: In the above syntax we use describe table command with select clause, here specified table name means actual table name that is created by the user.

How to describe Table in DB2?

Now let’s see how to describe table works in DB2 as follows:

Basically, the table command shows the following output as follows.

1. End result of select and Xquery statement.
2. It also used to display the OUT and INOUT parameters.
3. Different columns from the specified table and view.
4. Specified index from the table and view

After that, the most important point is Authorization as follows.

The approval required relies upon the kind of data you need to show utilizing the DESCRIBE command.

On the off chance that the SYSTOOLSTMPSPACE tablespace exists, one of the remote users is required to perform the describe table command, so we required the following table to as follows.

1. If we need to display the end result of the select and XQuery statement then we require any one of the following authorization as follows.

• We required database access authority.
• We also required explanatory authority.
• We required the different privileges for select statements on each table and view.
• We required a different privilege for select statements on a schema that contains the table and view.
• Execute privilege.
• Read privilege on global variables.

2. If we use OUT and INOUT parameters in a call statement at that time we require any one of the following authority as follows.

• We required database access authority.
• WE required EXECUTE privileges for the stored procedure.
• WE required EXECUTE privileges for the stored procedure and schema.
• We also required DATAACCESS authority for a schema that contained the store procedure.

3. Column of the table or view at that time we required any one of the following authority as follows.

• We need select privileges.
• We also required selection privileges.
• We required database access authority.

4. Command-line Parameter as follows.

OUTPUT: This is used to display the output statement and it is an optional part of this syntax.

TABLE table-name: It is used to specify the table or view that we need to describe. The completely qualified name in the structure schema.table-name should be utilized. An alias for the table can’t be utilized instead of the real table. Data about certainly covered up sections is returned, yet SHOW DETAIL should be utilized to demonstrate which segments are verifiably covered up.

The DESCRIBE TABLE command contains the accompanying data about every section as follows.

1. Column name
2. Type of schema.
3. Type name.
4. Length
5. Scale

5. Index for specified table name:

Determines the table or view for which records should be portrayed. You can utilize the completely qualified name in the structure schema.table-name or you can simply determine the table-name and default outline will be utilized consequently. A nom de plume for the table can’t be utilized instead of the real table.

The DESCRIBE INDEXES FOR TABLE order records the accompanying data about each list of the table or view:

1. Record pattern(index pattern)
2. Record name(index name)
3. Record type that means index type

On the off chance that the DESCRIBE INDEXES FOR TABLE command is determined with the SHOW DETAIL choice, the record name is shortened when the file name is more noteworthy than 18 bytes. On the off chance that no record type choice is indicated, data for all list types are recorded: social information file, file over XML information, and Text Search file. The yield incorporates the accompanying extra data:

List ID for a social information file, a XML way record, a XML districts file, or a list over XML information

1. Information Type for a list over XML information
2. Hashed for a list over XML information
3. Max VARCHAR Length for a list over XML information

It also utilizes the following parameter as follows.

1. Relational Data.
2. XML Data.
3. TEXT Search.
4. Data Partitions for the specified table name.
5. Show details.

Examples of DB2 Describe Table

Now let’s see the different examples of db2 describe table command as follows.

First, we need to create a new table by using the create table statement as follows.

Command:

create table emp (emp_id int not null, emp_first_name varchar(60), emp_last_name varchar(60), emp_email varchar(40), emp_verification Boolean, PRIMARY KEY(emp_id));

Explanation: In the above example we use a create table statement to create a new table name as emp with different attributes and different data types as shown in the above statement. The end result of the above statement we illustrate by using the following screenshot as follows.

Now use the select statement to see the records from the emp table as follows.

Command:

select * from emp;

Explanation: The emp table does not contain any record. The end result of the above statement we illustrate by using the following screenshot as follows.

Now use the describe command to see metadata about the emp table.

Command:

describe table emp;

Explanation: The end result of the above statement we illustrate by using the following screenshot as follows.

Command:

describe SELECT * FROM emp;

Explanation: By using the above statement we can also describe the table. The end result of the above statement we illustrate by using the following screenshot as follows.

We can also describe table commands with index, call statement, schema.

Conclusion

We hope from this article you have understood about the DB2 describe table. From the above article, we have learned the basic syntax of describing the table and we also see different examples of describe table. From this article, we learned how and when we use the DB2 to describe a table.

Recommended Articles

This is a guide to DB2 Describe Table. Here we discuss the basic concept, syntax, and How to describe the Table in DB2 along with Commands and its examples. You can also go through our other suggested articles to learn more –

1. DB2 Translate
2. DB2 Version
3. DB2 Create Table
4. DB2 Data Types