DB2 - Tables

Db2 - Tables

Tables are the fundamental logical structures for storing data in a relational database. A table represents an entity type (e.g., ‘Employee’, ‘Product’). It is organized into columns (attributes) and rows (tuples). Each row represents a single instance of the entity, and each column has a specific data type (e.g., INTEGER, VARCHAR, TIMESTAMP).

Types of Tables in Modern Db2

Db2 offers several types of tables, each optimized for different workloads and use cases.

• Row-Organized (Regular) Tables: The default and most common table type. Data is stored row by row. Ideal for Online Transaction Processing (OLTP) workloads with frequent INSERT, UPDATE, and DELETE operations on individual rows.
• Column-Organized Tables (‘BLU Acceleration’): A high-performance table type where data is stored column by column. This is optimal for Online Analytical Processing (OLAP) and business intelligence (BI) workloads involving complex queries over large datasets. It features superior compression and data skipping capabilities.
• Partitioned Tables: Allows a table’s data to be divided across multiple storage objects called data partitions, based on a partitioning key. Essential for managing Very Large Databases (VLDBs), as it improves query performance and simplifies data lifecycle management (e.g., attaching new data partitions or detaching old ones).
• Temporal Tables: Automatically track the history of data changes. This is critical for auditing, compliance, and historical analysis. Db2 supports system-period (tracking when data changed in the database), business-period (tracking when data was valid in the real world), and bitemporal tables (combining both).
• Materialized Query Tables (MQTs): These tables are defined by a query, and their content is the pre-computed result of that query. MQTs are used to significantly improve the performance of recurring, complex queries, such as those found in reporting dashboards.
• Temporary Tables (DGTTs): Used for storing transient data for the duration of a session. They are not logged and do not appear in system catalogs, making them efficient for intermediate work during complex procedures.
• Legacy Table Types: Multidimensional Clustering (MDC), Insert Time Clustering (ITC), and Range-Clustered Tables (RCT) exist for specific legacy use cases but are often superseded by modern alternatives like partitioned or column-organized tables for new applications.

Creating Tables with Modern Best Practices

A well-defined table includes not just columns and data types, but also constraints to ensure data integrity.

Syntax:

CREATE TABLE <schema_name>.<table_name> (
    <column1_name> <data_type> [CONSTRAINTS],
    <column2_name> <data_type> [CONSTRAINTS],
    ...
    PRIMARY KEY (<column_name>)
) IN <tablespace_name>;

Example: Create a table for employees, including a primary key, an auto-incrementing ID, a non-nullable name, and a default job role.

db2 "CREATE TABLE professional.employee(
    id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
    employee_code VARCHAR(20) NOT NULL UNIQUE,
    full_name VARCHAR(100) NOT NULL,
    job_role VARCHAR(50) DEFAULT 'Analyst',
    join_date DATE,
    salary DECIMAL(10, 2),
    PRIMARY KEY(id)
) IN userspace1"

Output:

DB20000I  The SQL command completed successfully.

Querying Table Metadata

You can inspect table structures using system catalog views or the DESCRIBE command.

Example: List user-created tables in the current database.

db2 "SELECT SUBSTR(tabschema,1,12) AS schema, SUBSTR(tabname,1,20) AS table_name, tbspace
     FROM syscat.tables WHERE type = 'T' AND tabschema NOT LIKE 'SYS%'"

Output (example):

SCHEMA       TABLE_NAME           TBSPACE
------------ -------------------- ----------
PROFESSIONAL EMPLOYEE             USERSPACE1

  1 record(s) selected.

Example: Describe the columns of the employee table.

db2 describe table professional.employee

Output (example):

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- -------
ID                              SYSIBM    INTEGER                      4     0 No
EMPLOYEE_CODE                   SYSIBM    VARCHAR                     20     0 No
FULL_NAME                       SYSIBM    VARCHAR                    100     0 No
JOB_ROLE                        SYSIBM    VARCHAR                     50     0 Yes
JOIN_DATE                       SYSIBM    DATE                         4     0 Yes
SALARY                          SYSIBM    DECIMAL                     10     2 Yes

  6 record(s) selected.

Implicitly Hidden Columns

You can define columns as IMPLICITLY HIDDEN. These columns are not returned by a SELECT * query and are ignored by INSERT statements without an explicit column list. This is useful for system-managed columns like row_change_timestamp that are used for auditing but not typically needed by applications.

Example: Create a table with a hidden column to track when a row was last updated.

db2 "CREATE TABLE professional.customer(
    cust_id INT NOT NULL PRIMARY KEY,
    full_name VARCHAR(100),
    last_updated TIMESTAMP NOT NULL
        WITH DEFAULT CURRENT TIMESTAMP
        IMPLICITLY HIDDEN
)"

When you run SELECT * FROM professional.customer, the last_updated column will not appear. To see it, you must explicitly name it in your query: SELECT cust_id, full_name, last_updated FROM professional.customer.

Data Manipulation (INSERT, SELECT)

Example: Inserting data into the customer table (note we don’t provide a value for the hidden column).

db2 "INSERT INTO professional.customer(cust_id, full_name) VALUES (101, 'Anika Sharma')"

Example: Retrieving data, explicitly requesting the hidden column.

db2 "SELECT cust_id, full_name, last_updated FROM professional.customer"

Output (example):

CUST_ID     FULL_NAME                 LAST_UPDATED
----------- ------------------------- --------------------------
        101 Anika Sharma              2023-10-27-10.30.00.123456

  1 record(s) selected.

Altering and Dropping Tables

The ALTER TABLE statement is used to modify a table’s structure, such as adding a column, changing a data type, or renaming a column.

Syntax: Add a new column

ALTER TABLE <table_name> ADD COLUMN <column_name> <data_type>;

Syntax: Rename an existing column

ALTER TABLE <table_name> RENAME COLUMN <old_name> TO <new_name>;

The DROP TABLE command deletes a table and all its data. This is an irreversible action, so use it with extreme caution.

Syntax:

DROP TABLE <table_name>;

Example: To permanently delete the customer table.

db2 "DROP TABLE professional.customer"

Practical Guidance: Choosing the Right Table Type

• For e-commerce sites, banking systems (OLTP): Start with Row-Organized Tables.
• For data warehouses, BI dashboards (OLAP): Use Column-Organized Tables for significant performance gains.
• For tables exceeding several gigabytes or terabytes: Use Partitioned Tables for better management and query performance.
• For tables requiring a full audit trail: Implement Temporal Tables.