CREATE TABLE in DB2 for z/OS

CREATE TABLE

A table is a named collection of rows that share the same columns. CREATE TABLE records that description in the catalog and (when the table space is already DEFINE YES) makes the object ready for INSERT. You need CREATETAB privilege on the database (and typically USE on the table space and storage group your shop uses).

The name is usually schema.table (HR.EMPLOYEE). Unqualified names resolve with CURRENT SCHEMA for dynamic SQL or the bind QUALIFIER for static SQL. The name must not collide with an existing table, view, or alias in that schema.

Basic syntax

1
2
3
4
5
6
7
8
CREATE TABLE schema.table-name
(
    column-name data-type [NOT NULL] [DEFAULT constant],
    column-name data-type [NOT NULL],
    ...
    PRIMARY KEY (column-name)
)
IN database.tablespace;

Parentheses wrap the column list. Commas separate columns. The IN clause is the z/OS placement clause: database name, then table space name. You can also write IN DATABASE dbname and let Db2 pick a space according to shop standards, or omit IN when implicit databases are allowed (often DSNDB04)—many production shops forbid implicit placement and require an explicit IN.

Optional clauses you will meet soon, but not required for a first table: PARTITION BY, ORGANIZE BY, EDITPROC, VALIDPROC, AUDIT, CCSID, COMPRESS, and the full referential constraint syntax. CREATE TABLE LIKE copies an existing column list. CREATE TABLE AS (fullselect) DEFINITION ONLY defines columns from a query without inserting rows.

Column definitions

Each column needs a unique name in the table and a data type. Common beginner types:

NOT NULL means every row must supply a value (or a default that is not null). Omit NOT NULL and the column accepts NULL. PRIMARY KEY columns must be NOT NULL; if you forget, Db2 treats them as not null when the primary key is defined. IDENTITY columns are implicitly NOT NULL and cannot have a DEFAULT clause of your own.

DEFAULT supplies a value when INSERT omits the column: a literal, USER, CURRENT DATE / TIME / TIMESTAMP, NULL, or a cast. GENERATED ALWAYS AS IDENTITY and GENERATED ALWAYS AS (expression) are covered on the identity page that follows in the DDL track.

Character columns may add FOR MIXED DATA, FOR SBCS DATA, FOR BIT DATA, or a CCSID. FOR BIT DATA means “bytes, not letters”—legacy binary in a character type. New designs prefer BINARY / VARBINARY / BLOB for raw bytes.

LOB and XML columns imply extra objects (auxiliary table spaces, indexes). You can still list CLOB(1M) in CREATE TABLE; Db2 creates the supporting objects. Keep LOB columns off your first two-column scratch table until you need them.

Simple examples

A small employee table with a primary key, placed in a known table space:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE TABLE HR.EMPLOYEE
(
    EMPNO      CHAR(6)       NOT NULL,
    FIRSTNME   VARCHAR(12)   NOT NULL,
    LASTNAME   VARCHAR(15)   NOT NULL,
    WORKDEPT   CHAR(3),
    PHONENO    CHAR(4),
    HIREDATE   DATE,
    JOB        CHAR(8),
    EDLEVEL    SMALLINT,
    SEX        CHAR(1),
    BIRTHDATE  DATE,
    SALARY     DECIMAL(9,2),
    BONUS      DECIMAL(9,2),
    COMM       DECIMAL(9,2),
    PRIMARY KEY (EMPNO)
)
IN HRDB.EMPTS;

EMPNO is CHAR(6) NOT NULL and the primary key, so Db2 will enforce uniqueness with a unique index. WORKDEPT is nullable so an employee can exist before a department is assigned (or you add a foreign key later). DECIMAL(9,2) is a typical salary shape: nine digits, two after the decimal.

A staging table without a primary key (duplicates allowed until you cleanse):

1
2
3
4
5
6
7
8
9
CREATE TABLE HR.EMP_STAGE
(
    EMPNO      CHAR(6),
    LASTNAME   VARCHAR(15),
    SALARY     DECIMAL(9,2),
    LOAD_DATE  DATE          NOT NULL WITH DEFAULT,
    STATUS     CHAR(8)       NOT NULL WITH DEFAULT 'NEW'
)
IN HRDB.STAGETS;

NOT NULL WITH DEFAULT on LOAD_DATE uses the date default (CURRENT DATE when the default is not a literal). STATUS defaults to the character string NEW. INSERT can omit those columns and still get legal rows.

A tiny code table:

1
2
3
4
5
6
CREATE TABLE HR.DEPT_CODE
(
    DEPTNO    CHAR(3)      NOT NULL PRIMARY KEY,
    DEPTNAME  VARCHAR(36)  NOT NULL
)
IN HRDB.CODETS;

Column-level PRIMARY KEY is shorthand for a single-column primary key. Multi-column keys belong in the table-level PRIMARY KEY (a, b) clause after the column list.

After CREATE TABLE, GRANT the privileges applications need, CREATE INDEX for non-unique access paths, and consider COMMENT ON TABLE / COLUMN so the catalog explains the business meaning. DROP TABLE removes the table (and its data) when you are done with a scratch object—never as a substitute for DELETE in production.

What CREATE TABLE does not do

• It does not load data. Use INSERT, LOAD, or MERGE next.
• It does not replace a missing table space. CREATE TABLESPACE first in shops that do not use implicit spaces.
• It does not define every index you will want—only uniqueness enforcers for PRIMARY KEY / UNIQUE.
• It is not ALTER TABLE. Changing a column later is a different statement, sometimes a pending change that needs REORG.

Explain It Like I'm Five

CREATE TABLE is drawing a blank form. Each column is a box on the form: this box is for a six-letter employee number, that box is for a name, that box is for money with two decimal places. NOT NULL means the box cannot be left empty. PRIMARY KEY means the employee number box must be different on every form. IN HRDB.EMPTS is “put the finished forms in this filing cabinet.” Filling in the forms is INSERT, not CREATE TABLE.

Exercises

1. Write CREATE TABLE for a PROJECT table with PROJNO CHAR(6) NOT NULL primary key, PROJNAME VARCHAR(24) NOT NULL, and DEPTNO CHAR(3), in database APPDB table space PROJTS.
2. Add a nullable DECIMAL(9,2) column named BUDGET. Why might you choose DECIMAL instead of FLOAT for money?
3. Change STATUS CHAR(8) so it is NOT NULL WITH DEFAULT 'NEW'. What happens on INSERT if STATUS is omitted?
4. Explain the difference between HR.EMPLOYEE as a name and IN HRDB.EMPTS as a location.
5. Why does a PRIMARY KEY on (EMPNO, PROJNO) belong at table level rather than on one column?