CREATE TABLE

CREATE TABLE is the DDL statement that builds a new table in the DB2 catalog and reserves space for it.

CREATE TABLE syntax

• CREATE TABLE is a DDL (Data Definition Language) statement, just like CREATE DATABASE, CREATE STOGROUP, CREATE TABLESPACE and CREATE INDEX.
• The basic form names the table, lists the columns with their data types, and ends with a semicolon:-
• CREATE TABLE MM01.EMP
  (EMPNO      CHAR(6)         NOT NULL,
   LASTNAME   VARCHAR(15)    NOT NULL,
   WORKDEPT   CHAR(3)         ,
   SALARY      DECIMAL(9,2)    ,
   PRIMARY KEY (EMPNO)
  );
• Each column is defined as column-name data-type [NOT NULL] [DEFAULT value].
• NOT NULL means the column must always hold a value. Columns that allow nulls need a null indicator in COBOL programs.

Choosing columns, data types and constraints

• Use CHAR or VARCHAR for character data, SMALLINT, INTEGER or DECIMAL for numbers, DATE, TIME and TIMESTAMP for date and time values.
• Table-level constraints such as PRIMARY KEY and FOREIGN KEY are coded after the column list, inside the same statement.
• A FOREIGN KEY links the table to a parent table and enforces referential integrity on every INSERT, UPDATE and DELETE.
• Keep the row size reasonable. DB2 has page-size limits, so very wide tables may not fit on a 4K page.

Placing the table in a database and tablespace

• Add the IN clause to choose the storage location:-
• CREATE TABLE MM01.EMP
  (EMPNO      CHAR(6)         NOT NULL,
   LASTNAME   VARCHAR(15)    NOT NULL,
   WORKDEPT   CHAR(3)         ,
   PRIMARY KEY (EMPNO),
   FOREIGN KEY (WORKDEPT) REFERENCES MM01.DEPT (DEPTNO)
  )
  IN MM01DB.EMPTS;
• If the IN clause is missing, DB2 creates the table in the default database and a default tablespace.
• Creating the table in the right database matters for utility runs, because DB2 utilities like COPY, REORG and RUNSTATS run at tablespace or database level.