TABLE

TABLE

What is a Table?

A table is a fundamental database object used to store and organize data in rows and columns. Each table in DB2 is defined by its columns (which specify data types) and rows (which hold individual data entries). Tables are a core part of the DB2 database structure, providing a means for data storage, retrieval, and management.

A table in DB2 is a two-dimensional structure consisting of rows and columns. Each row represents an individual record, and each column represents a specific attribute of the data stored. Tables are stored within tablespaces, which provide physical storage and manage data organization on disk.

Purpose:

  • Data Storage: Tables store data in a structured format for easy retrieval and management.
  • Data Organization: Columns define the types of data stored, while rows store individual records.
  • Efficient Access: Tables allow quick access to specific records or groups of data based on conditions.

Types of Tables :

  1. Base Table: The standard table type used to store persistent data. Defined and stored within a tablespace.
  2. Temporary Table: Stores temporary data for the duration of a session or transaction. Not persistent.
  3. Declared Global Temporary Table (DGTT): Defined by the user at runtime, often within a stored procedure or application. Holds data temporarily.
  4. Materialized Query Table (MQT): Stores the result of a query for faster retrieval, effectively caching data for complex queries.
  5. Clone Table: A copy of an existing table structure and data, often used in testing or data migration scenarios.

The user should have SYSADM, and SYSCTRL authority is required to create a table. If the user doesn't have the above authority, then it will return a -551 error when the CREATE TABLE is triggered.

Creating a Table

To create a table in DB2, use the CREATE TABLE statement, defining the table's name, columns, data types, constraints, and storage location.

Syntax -

CREATE TABLE table_name (
column_name1 data_type1 [NOT NULL | NULL],
column_name2 data_type2 [NOT NULL | NULL],
...
PRIMARY KEY (column1,column2….column-n),
FORIEGN KEY (column1,column2….column-n)
) 
IN database_name.tablespace_name;
  • table_name: The name of the table you are creating.
  • column_name: Names of columns within the table.
  • data_type: Specifies the data type for each column (e.g., INTEGER, CHAR, DATE).
  • NOT NULL | NULL: Specifies if the column can accept null values.
  • PRIMARY KEY: Optional. The unique column, used to identify the row uniquely, is known as the primary key. The primary key can be one or more columns.
  • FORIEGN KEY: Optional. A foreign key is a reference from another table. It is a primary key on another table (Parent table), referred to in the current table (child table) with another column name or the same column name. A foreign key can have a NULL value in the current table (child table).
  • IN database_name.tablespace_name: Specifies the database and tablespace where the table will reside.

Examples - Creating a Base Table

CREATE TABLE EMPLOYEE (
EMP_ID INTEGER NOT NULL,
EMP_NAME CHAR(50),
DEPARTMENT CHAR(30),
SALARY DECIMAL(10, 2),
JOIN_DATE DATE,
PRIMARY KEY(EMP_ID)
)
IN EMPLDB.EMPLTS;
  • EMPLOYEE: Creates a table named EMPLOYEE.
  • Columns: Defines columns for employee ID, name, department, salary, and joining date.
  • IN EMPLDB.EMPLTS: Specifies that the table is stored in the EMPLOYEE_TS tablespace within the EMPLOYEE_DB database.

Altering a Table

To modify an existing table, use the ALTER TABLE statement. We can add columns, modify constraints, or change column data types.

Syntax -

ALTER TABLE table_name
ADD column_name data_type [NOT NULL | NULL];

Examples - Altering a Table

ALTER TABLE EMPLOYEE
ADD PHONE_NUMBER CHAR(15);

Adds a new column PHONE_NUMBER with data type CHAR(15) to the EMPLOYEE table.

Deleting a Table

To delete a table, use the DROP TABLE statement. This removes the table and all data stored within it.

Syntax -

DROP TABLE table_name;

Examples - Deleting a Table

DROP TABLE EMPLOYEE;