DB2 - Tables



Tables are logical structure maintained by Database manager. In a table each vertical block called as column (Tuple) and each horizontal block called as row (Entity). The collection of data stored in the form of columns and rows is known as a table. In tables, each column has different data type. Tables are used to store persistent data.

Type of tables

  • Base Tables: They hold persistent data. There are different kinds of base tables, including:
    • Regular Tables: General purpose tables, Common tables with indexes are general purpose tables.
    • Multidimensional Clustering Table (MDC): This type of table physically clustered on more than one key, and it used to maintain large database environments. These type of tables are not supported in DB2 pureScale.
    • Insert time clustering Table (ITC): Similar to MDC tables, rows are clustered by the time they are inserted into the tables. They can be partitioned tables. They too, do not support pureScale environment.
    • Range-Clustered tables Table (RCT): These type of tables provide fast and direct access of data. These are implemented as sequential clusters. Each record in the table has a record ID. These type of tables are used where the data is clustered tightly with one or more columns in the table. This type of tables also do not support in DB2 pureScale.
    • Partitioned Tables: These type of tables are used in data organization schema, in which table data is divided into multiple storage objects. Data partitions can be added to, attached to and detached from a partitioned table. You can store multiple data partition from a table in one tablespace.
    • Temporal Tables: History of a table in a database is stored in temporal tables such as details of the modifications done previously.
  • Temporary Tables: For temporary work of different database operations, you need to use temporary tables. The temporary tables (DGTTs) do not appear in system catalog, XML columns cannot be used in created temporary tables.
  • Materialized Query Tables: MQT can be used to improve the performance of queries. These types of tables are defined by a query, which is used to determine the data in the tables.

Creating Tables

The following syntax creates table:

Syntax: [To create a new table]

db2 create table <schema_name>.<table_name>
(column_name column_type....) in <tablespace_name>   

Example: We create a table to store “employee” details in the schema of “professional”. This table has “id, name, jobrole, joindate, salary” fields and this table data would be stored in tablespace “ts1”.

db2 create table professional.employee(id int, name 
varchar(50),jobrole varchar(30),joindate date, 
salary double) in ts1    

Output:

DB20000I The SQL command completed successfully.    

Listing table details

The following syntax is used to list table details:

Syntax: [To see the list of tables created with schemas]

db2 select tabname, tabschema, tbspace from syscat.tables    

Example: [To see the list of tables in the current database]

db2 select tabname, tabschema, tbspace from syscat.tables     

Output:

TABNAME      TABSCHEMA     TBSPACE 
------------ ------------- -------- 
EMPLOYEE     PROFESSIONAL    TS1  


 1 record(s) selected.     

Listing columns in a table

The following syntax lists columns in a table:

Syntax: [To see columns and data types of a table]

db2 describe table <table_name>    

Example: [To see the columns and data types of table ‘employee’]

db2 describe table professional.employee     

Output:

             Data type                   Column 
Column name  schema    Data type name    Length    Scale Nulls 
------ ----- --------- ----------------- --------- ----- ------ 
ID           SYSIBM    INTEGER             4         0     Yes 
NAME         SYSIBM    VARCHAR             50        0     Yes 
JOBROLE      SYSIBM    VARCHAR             30        0     Yes 
JOINDATE     SYSIBM    DATE                4         0     Yes 
SALARY       SYSIBM    DOUBLE              8         0     Yes  

  5 record(s) selected.   

Hidden Columns

You can hide an entire column of a table. If you call “select * from” query, the hidden columns are not returned in the resulting table. When you insert data into a table, an “INSERT” statement without a column list does not expect values for any implicitly hidden columns. These type of columns are highly referenced in materialized query tables. These type of columns do not support to create temporary tables.

Creating table with hidden column

The following syntax creates table with hidden columns:

Syntax: [To create a table with hidden columns]

db2 create table <tab_name> (col1 datatype,col2 datatype 
implicitly hidden)    

Example: [To create a ‘customer’ table with hidden columns ‘phone’]

db2 create table professional.customer(custid integer not 
null, fullname varchar(100), phone char(10) 
implicitly hidden)     

Inserting data values in table

The following syntax inserts values in the table:

Syntax: [To insert values into a table]

db2 insert into <tab_name>(col1,col2,...)
 values(val1,val2,..)    

Example: [To insert values in ‘customer’ table]

db2 insert into professional.customer(custid, fullname, phone) 
values(100,'ravi','9898989')


db2 insert into professional.customer(custid, fullname, phone) 
values(101,'krathi','87996659')


db2 insert into professional.customer(custid, fullname, phone) 
values(102,'gopal','768678687')

Output:

DB20000I  The SQL command completed successfully.

Retrieving values from table

The following syntax retrieves values from the table:

Syntax: [To retrieve values form a table]

db2 select * from <tab_name>    

Example: [To retrieve values from ‘customer’ table]

db2 select * from professional.customer 

Output:

CUSTID      FULLNAME 
----------- ------------------------ 
        100 ravi
		
        101 krathi
		
        102 gopal  
		
  3 record(s) selected. 

Retrieving values from a table including hidden columns

The following syntax retrieves values from selected columns:

Syntax: [To retrieve selected hidden columns values from a table]

db2 select col1,col2,col3 from <tab_name>    

Example: [To retrieve selected columns values result from a table]

db2 select custid,fullname,phone from professional.customer  

Output:

CUSTID  FULLNAME    PHONE 
------- ---------   ------------ 
100     ravi        9898989
 
101     krathi      87996659 

102     gopal       768678687 

  3 record(s) selected. 

If you want to see the data in the hidden columns, you need to execute “DESCRIBE” command.

Syntax:

db2 describe table <table_name> show detail     

Example:

db2 describe table professional.customer show detail     

Output:

Column name     Data type schema     Data type name  Column
           column    Partitionkey  code 
                                       Length   Scale    Nulls     
number     sequence      page     Hidden      Default 
--------------- -------------------- --------------- -------- ----
---- -------- ---------- ------------- -------- ----------- ------ 
---  
CUSTID          SYSIBM               INTEGER         4        0 
No       0          0         0        No 
FULLNAME        SYSIBM               VARCHAR         100      0
Yes      1          0        1208     No     

PHONE           SYSIBM               CHARACTER       10       0     
Yes      2          0             1208     Implicitly  
  
3 record(s) selected.  

Altering the type of table columns

You can modify our table structure using this “alter” command as follows:

Syntax:

db2 alter table <tab_name> alter column <col_name> set data type <data_type>     

Example: [To modify the data type for column “id” from “int” to “bigint” for employee table]

db2 alter table professional.employee alter column id set data type bigint      

Output::

DB20000I The SQL command completed successfully.    

Altering column name

You can change column name as shown below:

Syntax: [To modify the column name from old name to new name of a table]

db2 alter table <tab_name> rename column <old_name> to <new_name>     

Example: [To modify the column name from “fullname” to “custname” in “customers” table.]

db2 alter table professional.customer rename column fullname to custname       

Dropping the tables

To delete any table, you need to use the “DROP” command as follows:

Syntax:

db2 drop table <tab_name>     

Example: [To drop customer table form database]

db2 drop table professional.customers       

To delete the entire hierarchy of the table (including triggers and relation), you need to use “DROP TABLE HIERARCHY” command.

Syntax:

db2 drop table hierarchy <tab_name>

Example: [To drop entire hierarchy of a table ‘customer’]

db2 drop table hierarchy professional.customers       
Advertisements