Tables are sensible structure kept up by Database supervisor. In a table every vertical square called as section (Tuple) and every flat square called as line (Entity). The assortment of information put away as segments and columns is known as a table. In tables, every segment has various information type. Tables are utilized to store relentless information.
Type of tables
- Base Tables: They hold tenacious information. There are various types of base tables, including:
- Customary Tables: General reason tables, Common tables with records are broadly useful tables.
- Multidimensional Clustering Table (MDC): This kind of table truly bunched on more than one key, and it used to keep up huge database situations. These kind of tables are not upheld in DB2 pureScale.
- Addition time bunching Table (ITC): Similar to MDC tables, lines are grouped when they are embedded into the tables. They can be divided tables. They as well, don't bolster pureScale condition.
- Range-Clustered tables Table (RCT): These kind of tables give quick and direct access of information. These are executed as successive bunches. Each record in the table has a record ID. These sort of tables are utilized where the information is grouped firmly with at least one segments in the table. This kind of tables likewise don't bolster in DB2 pureScale.
- Apportioned Tables: These kind of tables are utilized in information association composition, in which table information is separated into various capacity objects. Information parcels can be added to, connected to and segregated from a divided table. You can store different information segment from a table in one tablespace.
- Fleeting Tables: History of a table in a database is put away in worldly tables, for example, subtleties of the changes done beforehand.
- Impermanent Tables: For transitory work of various database activities, you have to utilize brief tables. The brief tables (DGTTs) don't show up in framework index, XML segments can't be utilized in made transitory tables.
- Emerged Query Tables: MQT can be utilized to improve the presentation of questions. These kinds of tables are characterized by a question, which is utilized to decide the information in the tables.
Creating Tables
The accompanying language structure makes 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 conceal a whole section of a table. In the event that you call "select * from" inquiry, the shrouded segments are not returned in the subsequent table. At the point when you embed information into a table, a "Supplement" explanation without a segment list doesn't anticipate values for any certainly concealed segments. These sort of segments are exceptionally referenced in appeared inquiry tables. These sort of sections don't support to make transitory tables.
Creating table with hidden column
The accompanying linguistic structure makes table with concealed sections:
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