Creating Tables

A DB2 table is the most basic and most used DB2 object. Actual data is stored in tables.

What is a DB2 table

• A DB2 object that consists of columns and rows that define the physical characteristics of the data to be stored.
• Columns define what kind of data the table holds. Rows hold the actual data values.
• Every table lives inside a tablespace. A tablespace is the physical space where tables are stored.
• Table name is qualified as creator.tablename, for example MM01.EMPLOYEE. The creator is the authorization ID of the person creating the table.
• One DB2 table can be as large as the tablespace that holds it.

Steps to create a table

• Step 1 - Decide the columns the table needs. Each column gets a name, a data type and a length.
• Step 2 - Decide which columns must not be null. Key columns are normally defined as NOT NULL.
• Step 3 - Decide the primary key. A primary key is one or more columns that uniquely identify each row.
• Step 4 - Decide the database and tablespace where the table will be stored.
• Step 5 - Code the CREATE TABLE statement and run it through SPUFI, QMF or a batch job. DB2 checks the syntax, the authorization and the data types before creating the table.

Example of a table definition

• Below is a real CREATE TABLE statement. It creates a table with three columns and a two-column primary key:-
• CREATE TABLE USER.PERIODIC_BALANCES
  (CUSTOMER_NO      CHAR(11)      NOT NULL,
   BALANCE_PERIOD   SMALLINT      NOT NULL,
   BALANCE         DECIMAL(15,2)    ,
   PRIMARY KEY (CUSTOMER_NO, BALANCE_PERIOD)
  );
• CUSTOMER_NO is CHAR(11) and NOT NULL, BALANCE_PERIOD is SMALLINT and NOT NULL, BALANCE is DECIMAL(15,2) and can be null.
• The PRIMARY KEY clause says that the combination of CUSTOMER_NO and BALANCE_PERIOD must be unique.