Advanced DB2 table options

The TABLE object

A base table is the persistent object you create with CREATE TABLE. The catalog (SYSIBM.SYSTABLES) stores the description; the table space stores the rows. Everything on this page hangs off that object: column options, table constraints, and ALTER features that add a second physical instance (clone) or a companion table (history, archive).

Related but different: views (named SELECTs), aliases, MQTs, global temporary tables, and auxiliary tables for LOB/XML. Do not confuse CREATE TABLE with CREATE AUXILIARY TABLE or with implicit objects Db2 builds when you omit IN.

Column defaults and NOT NULL

NOT NULL forbids the null marker. Every INSERT must supply a value or a default must exist. Primary-key and identity columns are NOT NULL. Omit NOT NULL and the column is nullable — missing INSERT values become null, not blank or zero.

WITH DEFAULT (or DEFAULT constant / CURRENT DATE / USER, and so on) tells Db2 what to store when the INSERT list skips the column. NOT NULL without a default means omitting the column fails. Nullable without DEFAULT stores null. The system default when you write WITH DEFAULT with no value depends on the data type (0 for numbers, blanks for fixed CHAR, empty string for VARCHAR, and so on).

1
2
3
4
5
6
7
CREATE TABLE HR.EMP_STATUS
     (EMPNO      CHAR(6)     NOT NULL,
      STATUS     CHAR(1)     NOT NULL WITH DEFAULT 'A',
      UPDATED    DATE        NOT NULL WITH DEFAULT,
      COMMENT    VARCHAR(70),
      PRIMARY KEY (EMPNO))
     IN HRDB.EMPTS;

STATUS defaults to 'A'. UPDATED defaults to the current date. COMMENT may be omitted and becomes null. Details live on the column-defaults page; here the point is that advanced tables spell nullability and defaults on every column on purpose.

CHECK constraints

A CHECK constraint is a search condition Db2 evaluates on INSERT and UPDATE of that row. It can only see columns of the same table (same row), not other tables — that is what foreign keys are for.

1
2
3
4
5
6
7
8
CREATE TABLE HR.EMP
     (EMPNO      CHAR(6)      NOT NULL,
      SALARY     DECIMAL(9,2) NOT NULL,
      BONUS      DECIMAL(9,2) NOT NULL WITH DEFAULT,
      CONSTRAINT CK_SAL CHECK (SALARY >= 0),
      CONSTRAINT CK_PAY CHECK (SALARY + BONUS <= 500000),
      PRIMARY KEY (EMPNO))
     IN HRDB.EMPTS;

Name constraints so SYSIBM.SYSCHECKS and error messages are readable. Adding CHECK with ALTER TABLE on a populated table can put the space in CHECK-pending until CHECK DATA runs. CHECK is not a substitute for application validation, but it stops bad rows from every interface (SQL, LOAD ENFORCE CONSTRAINTS, utilities that honour RI).

FOREIGN KEY and referential constraints

A referential constraint says: values in the child columns must match a parent key (primary key or unique key) in the parent table. You write it as FOREIGN KEY (cols) REFERENCES parent (cols) ON DELETE rule. If you omit parent column names, Db2 uses the parent’s primary key.

1
2
3
4
5
6
7
8
9
CREATE TABLE DSN8C10.EMPPROJACT
     (EMPNO     CHAR(6)   NOT NULL,
      PROJNO    CHAR(6)   NOT NULL,
      ACTNO     SMALLINT  NOT NULL,
      CONSTRAINT REPAPA FOREIGN KEY (PROJNO, ACTNO)
        REFERENCES DSN8C10.PROJACT ON DELETE RESTRICT,
      CONSTRAINT REPAE FOREIGN KEY (EMPNO)
        REFERENCES DSN8C10.EMP ON DELETE RESTRICT)
     IN DATABASE DSN8D13A;

Self-referencing tables (EMP.MGRNO → EMP.EMPNO) usually need CREATE TABLE first, then ALTER TABLE ADD FOREIGN KEY. Adding a foreign key to a populated table sets CHECK-pending. Index the foreign key if parents are deleted often. Business-time temporal RI (PERIOD BUSINESS_TIME on FOREIGN KEY and REFERENCES, Db2 12 FL 500) is a special form: the child’s period must be covered by parent periods, with matching BUSINESS_TIME indexes; ON DELETE RESTRICT is required.

Generated columns, identity, row change timestamp, ROWID

AS IDENTITY — one per table, exact numeric scale zero. GENERATED ALWAYS means Db2 supplies the number; GENERATED BY DEFAULT lets INSERT provide a value (reload, data sharing between systems). ALWAYS plus NO CYCLE plus a unique index is how you get a surrogate key you can trust.

ROW CHANGE TIMESTAMP — GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP. Db2 stamps insert and update time (often TIMESTAMP(12)). Useful for optimistic locking and replication. If you ADD the column to a table that already has rows, values are materialized on later UPDATE, REORG, or LOAD REPLACE — not all at ALTER time.

ROWID — uniquely identifies a row in the subsystem. LOB and XML designs use it to find auxiliary data. GENERATED ALWAYS vs BY DEFAULT matches identity: ALWAYS for ordinary tables; BY DEFAULT when you must unload/reload the same identifiers. Implicitly hidden ROWID columns can exist for LOB tables you did not declare yourself.

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE APP.ORDER_HDR
     (ORD_RID    ROWID GENERATED ALWAYS NOT NULL,
      ORD_ID     INTEGER GENERATED ALWAYS AS IDENTITY
                   (START WITH 1, INCREMENT BY 1, NO CYCLE),
      CUSTNO     CHAR(6) NOT NULL,
      CHANGE_TS  TIMESTAMP(12) NOT NULL
                   GENERATED ALWAYS FOR EACH ROW ON UPDATE
                   AS ROW CHANGE TIMESTAMP,
      NOTES      CLOB(1M) INLINE LENGTH 200,
      PRIMARY KEY (ORD_ID))
     IN APPDB.ORDTS;

LOB columns and inline LOB

BLOB, CLOB, and DBCLOB columns store large values in an auxiliary table in a LOB table space. The base row holds a descriptor and ROWID linkage. You can CREATE AUXILIARY TABLE yourself or let Db2 create LOB objects implicitly (CURRENT RULES and whether the base space was explicit both matter).

INLINE LENGTH integer keeps the first n bytes of the LOB in the base table row. Short values never touch the auxiliary space; long values still do. That can avoid a second page set for typical document sizes, at the cost of a wider base row (page size, PCTFREE, and MAXROWS all feel it). Inline length 0 means not inline.

LOB table spaces inherit LOGGED / NOT LOGGED from the base space. Utilities (COPY, REORG, LOAD) treat base and LOB spaces as a family. Do not put PCTFREE on the LOB space.

XML columns

An XML column stores well-formed XML in an implicit XML table space (and related objects) that follows the base table’s partitioning: PBG XML grows on its own; PBR XML is data-partitioned like the base. You can add an XML type modifier (XMLSCHEMA) so inserts are validated. Views that project XML inherit the type modifier unless INSTEAD OF triggers change the rules. XML indexes (CREATE INDEX … GENERATE KEY USING XMLPATTERN) are a separate topic; the table option is simply the XML column and its storage.

CCSID

CCSID ASCII | EBCDIC | UNICODE on CREATE TABLE sets the encoding scheme for character and graphic columns that do not override it. Individual columns can specify CCSID (for example CCSID 1208 for UTF-8 VARCHAR on an EBCDIC table in later function). Mixing schemes in one table is tightly restricted; mismatches on compare and UNION fail or require CAST.

The table space also has a CCSID. In practice the database, table space, and table should tell one encoding story. Implicit objects inherit from ZPARMs and the database default.

1
2
3
4
5
6
CREATE TABLE APP.UNI_CUST
     (CUSTNO   CHAR(6) NOT NULL,
      NAME     VARCHAR(128) CCSID UNICODE,
      PRIMARY KEY (CUSTNO))
     IN APPDB.CUSTTS
     CCSID UNICODE;

Clone tables

A clone is a structurally identical second table in another instance of the same partition-by-range or partition-by-growth UTS (Db2-managed data sets only). You do not CREATE TABLE the clone; you issue:

1
2
ALTER TABLE APP.ORDER_HDR
     ADD CLONE APP.ORDER_HDR_CLONE;

The clone gets the same indexes, before triggers, and LOB objects. SYSTABLESPACE still shows one table; a clone flag indicates the extra instance. Typical pattern: load the clone, then EXCHANGE DATA between base and clone for a near-instant switch (online load replacement). Restrictions: not on archive tables, history tables, and several other special table types. Creating an index on a base that already has a clone also creates the index on the clone (often rebuild-pending if the clone has rows).

Temporal tables

System-period temporal tables keep old row versions automatically. You define begin and end TIMESTAMP(12) columns, a PERIOD SYSTEM_TIME, a history table with matching columns, then:

1
2
ALTER TABLE POLICY_INFO
     ADD VERSIONING USE HISTORY TABLE HIST_POLICY_INFO;

Updates and deletes copy the previous image to history. SELECT … FOR SYSTEM_TIME AS OF timestamp reads the past. The history table cannot be an MQT, archive table, or archive-enabled table, and cannot itself have a clone.

Application-period (BUSINESS_TIME) tables store effective dates you supply. Bitemporal tables have both periods. Those designs need careful unique indexes (BUSINESS_TIME WITHOUT OVERLAPS on the parent).

Archive tables

An archive-enabled table is a live table linked with ENABLE ARCHIVE TO archive-table. When rows are deleted from the live table, Db2 can insert them into the archive table. Global variables control whether statements see live only or live-plus-archive. Archive tables cannot have clones or history tables piled on them. Use archive when you want to move cold rows out of the operational table without a home-grown move job; use temporal history when you need time-travel versions of updates, not only deletes.

Implicit table creation

If CREATE TABLE omits IN database.tablespace, Db2 implicitly creates a table space. Current function uses a partition-by-growth UTS (historically SEGSIZE 4, DSSIZE 4 G, MAXPARTITIONS 256, LOCKSIZE ROW, LOCKMAX SYSTEM — always confirm your release). If you also omit the database, Db2 uses or creates an implicit database named DSNxxxxx (sequence SYSIBM.DSNSEQ_IMPLICITDB), storage group SYSDEFLT, buffer pools from ZPARMs.

Implicit objects also include unique indexes for PRIMARY KEY / UNIQUE, and LOB/XML auxiliary objects — especially when CURRENT RULES is STD versus DB2 and when the base space was implicit. Convenient for tools. Painful for naming standards, STOGROUP placement, and locking defaults. Production DDL should create the database and table space first, then CREATE TABLE … IN.

1
2
3
4
5
6
7
8
9
10
-- Prefer explicit objects in production
CREATE DATABASE APPDB STOGROUP APPSTO;
CREATE TABLESPACE CUSTTS IN APPDB
     USING STOGROUP APPSTO
     SEGSIZE 32 MAXPARTITIONS 16
     LOCKSIZE ROW CLOSE NO;
CREATE TABLE APP.CUSTOMER
     (CUSTNO CHAR(6) NOT NULL PRIMARY KEY,
      NAME   VARCHAR(40) NOT NULL)
     IN APPDB.CUSTTS;

Explain It Like I'm Five

A table is a labeled box of index cards. NOT NULL means every card must have that field filled in; DEFAULT is the stamp you use if someone forgets. CHECK is a rule on one card (“salary cannot be negative”). FOREIGN KEY is “this department number must already exist in the department box.” Identity and ROWID are serial numbers the librarian writes. A clone is a second identical box you can swap in a second. Temporal history is a dusty archive shelf of old versions of the card. Archive is moving cards you deleted into a storage box. Implicit creation is the librarian building a box with a generated name because you forgot to bring one — fine at home, messy in a real library.

Exercises

1. Write CREATE TABLE DEPT with DEPTNO CHAR(3) NOT NULL primary key, MGRNO CHAR(6) nullable, and a CHECK that DEPTNO <> ' '.
2. Add EMP with WORKDEPT FOREIGN KEY to DEPT ON DELETE RESTRICT. Why is CASCADE dangerous here?
3. Add an IDENTITY order-number GENERATED ALWAYS and explain what breaks if you also need to reload yesterday’s numbers.
4. Choose INLINE LENGTH for a CLOB that is usually 100 bytes and rarely 2 MB. What happens to page size?
5. List three reasons not to omit IN on CREATE TABLE in a shared production subsystem.