Column defaults and NOT NULL in DB2 tables

NOT NULL

NOT NULL means the column cannot contain the null value. Every row must have a real value. Primary key columns must be NOT NULL. Foreign keys that must always point at a parent are often NOT NULL as well.

1
2
3
4
5
6
7
CREATE TABLE HR.EMPLOYEE (
  EMPNO      CHAR(6)      NOT NULL,
  LASTNAME   VARCHAR(15)  NOT NULL,
  WORKDEPT   CHAR(3),
  SALARY     DECIMAL(9,2),
  COMM       DECIMAL(9,2)
) IN HRDB.HRTS;

EMPNO and LASTNAME reject nulls. WORKDEPT, SALARY, and COMM may be null unless you add NOT NULL. If you omit NOT NULL, the column is nullable: its default missing-value behaviour is to store NULL when INSERT does not list the column.

NOT NULL is not “no blanks.” CHAR columns can be all spaces and still be NOT NULL. NOT NULL is not “no zero.” INTEGER 0 is a real value. Empty VARCHAR is a real zero-length string. NULL is a separate marker meaning “unknown or not supplied.”

Application programs that FETCH a nullable column need an indicator variable in COBOL (or the language’s null API). NOT NULL columns do not need an indicator for that column, which simplifies host layouts. That convenience is one reason shops mark codes and keys NOT NULL even when a junior designer wanted “optional.”

DEFAULT values

Db2 defines some defaults; you define others with a DEFAULT clause on CREATE TABLE or ALTER TABLE. Classic z/OS wording is NOT NULL WITH DEFAULT. You can also write DEFAULT with an explicit value or special register.

1
2
3
4
5
6
7
8
9
CREATE TABLE HR.EMPLOYEE (
  EMPNO      CHAR(6)       NOT NULL,
  LASTNAME   VARCHAR(15)   NOT NULL,
  HIREDATE   DATE          NOT NULL WITH DEFAULT,
  STATUS     CHAR(1)       NOT NULL WITH DEFAULT 'A',
  PRIMARY_ID CHAR(8)       WITH DEFAULT USER,
  SQL_ID     CHAR(8)       WITH DEFAULT CURRENT SQLID,
  BONUS      DECIMAL(9,2)  NOT NULL WITH DEFAULT
) IN HRDB.HRTS;

• HIREDATE NOT NULL WITH DEFAULT — if INSERT omits HIREDATE, store CURRENT DATE
• STATUS … DEFAULT 'A' — named default, useful for status flags
• WITH DEFAULT USER — the primary authorization ID of the process
• WITH DEFAULT CURRENT SQLID — the SQL authorization ID
• BONUS NOT NULL WITH DEFAULT — system default for DECIMAL is 0

IBM’s audit pattern is exactly those USER and CURRENT SQLID columns: every insert stamps who did it without the program listing the columns.

System defaults by data type

When you specify WITH DEFAULT but do not name a value, Db2 picks a default from the column’s data type:

Distinct types use the default of the source type. XML columns generally cannot have an explicit default other than null. Identity, ROWID, and row-change-timestamp columns have their own generation rules (next page)—do not think of them as ordinary WITH DEFAULT numbers.

Retrieval of a newly added NOT NULL WITH DEFAULT column, before you UPDATE it, returns those same defaults for existing rows (with few documented exceptions). You do not have to run an UPDATE to “fill” the column before the first SELECT; Db2 presents the default when it reads a row that has no stored value for the new column yet. A later REORG can materialize the value on disk.

Missing-value behaviour

IBM’s rule is easy to memorize and easy to get backwards:

• If the column is NOT NULL WITH DEFAULT, or you do not specify NOT NULL, Db2 stores a default whenever INSERT or LOAD does not provide a value.
• If the column is NOT NULL (and you did not give a default), Db2 does not supply a default. The statement must provide a value.

1
2
3
4
5
6
7
8
9
INSERT INTO HR.EMPLOYEE (EMPNO, LASTNAME)
  VALUES ('000010', 'HAAS');
-- HIREDATE becomes CURRENT DATE if NOT NULL WITH DEFAULT
-- STATUS becomes 'A' if that default was defined
-- WORKDEPT becomes NULL if the column is nullable and omitted
 
INSERT INTO HR.EMPLOYEE (EMPNO, LASTNAME, HIREDATE)
  VALUES ('000020', 'THOMPSON', DEFAULT);
-- DEFAULT keyword asks for the column default explicitly

LOAD follows the same idea: omitted fields get defaults or nulls according to the column definition. A NOT NULL column with no default in the LOAD input is a conversion error for that row.

UPDATE … SET COL = DEFAULT restores the default, which is handy for “reset status to A” without hard-coding the literal in every program. You cannot SET a NOT NULL column with no default to DEFAULT successfully—there is nothing to apply.

Adding columns to existing tables

ALTER TABLE ADD COLUMN has a hard rule: the new column must be nullable or NOT NULL WITH DEFAULT. Existing rows already occupy the table. Db2 must know what SELECT should return for those rows.

1
2
3
4
5
ALTER TABLE HR.EMPLOYEE
  ADD COLUMN STATUS CHAR(1) NOT NULL WITH DEFAULT 'A';
 
ALTER TABLE HR.EMPLOYEE
  ADD COLUMN NOTES VARCHAR(50);   -- nullable, existing rows show NULL

Adding NOT NULL without a default is rejected. After ADD COLUMN, application SELECT * layouts can break—another reason production COBOL lists columns. Package invalidation depends on the exact alter; some ADD COLUMN forms do not invalidate, others do. Check IBM’s “changes that invalidate packages” list before a production alter.

Choosing NOT NULL versus nullable

• Business required — employee number, last name: NOT NULL
• Optional fact — commission, middle initial: nullable, or NOT NULL WITH DEFAULT 0 / blank if “missing” should look like zero for arithmetic
• Flags — NOT NULL WITH DEFAULT 'N' so reports never trip on IS NULL
• Dates you did not collect yet — nullable. WITH DEFAULT CURRENT DATE lies about hire dates you never entered

COALESCE in queries can hide nulls at read time. That is not a substitute for a column default if INSERT paths are many and inconsistent. Put the rule in the table when every inserter should see the same fill-in.

CHECK constraints (later page) can further restrict defaults: STATUS CHAR(1) NOT NULL WITH DEFAULT 'A' plus CHECK (STATUS IN ('A','I')) keeps both INSERT DEFAULT and later UPDATEs honest.

INSERT, LOAD, and the DEFAULT keyword

Three ways a value arrives: you list it in INSERT, LOAD supplies it from a file, or Db2 applies a default. The SQL keyword DEFAULT in VALUES or SET is the explicit “please use the column default” request. It is not the same as omitting the column in every client: some INSERT generators list every column and pass NULL, which overrides WITH DEFAULT and stores null on a nullable column—or fails on a NOT NULL column. If your ORM sends NULL, the table default never runs. Teach the application to omit the column or send DEFAULT.

LOAD has DEFAULTIF and similar field-specification options so a blank input field can map to the column default instead of a conversion error. Match LOAD control statements to the NOT NULL / WITH DEFAULT design or the first production load will reject half the file.

Views that omit a NOT NULL column without a default cannot be inserted into unless INSTEAD OF triggers fill the gap. Views that omit a column that has a default can be inserted into: Db2 fills the hidden column. That is another reason to put defaults in the base table rather than only in application code.

WITH DEFAULT USER and CURRENT SQLID

USER and CURRENT SQLID are special-register defaults, not literals. USER is the primary authorization ID. CURRENT SQLID is the SQL ID, which SET CURRENT SQLID can change. If your shop uses secondary IDs and a different CURRENT SQLID than the RACF user, stamp both columns if you need a forensic trail. These defaults are evaluated at insert time on the server, not in the COBOL program, so a client cannot forge them by omitting the column—though a client that is allowed to UPDATE the column later still can, unless you revoke UPDATE on those columns or use a trigger/row permission.

CURRENT DATE / TIME / TIMESTAMP defaults are also evaluated at the server with the statement’s timezone rules. Batch jobs that insert “today” via a host variable and jobs that rely on WITH DEFAULT CURRENT DATE can disagree around midnight and around TIMESTAMP WITH TIME ZONE. Pick one convention per table.

CHAR defaults of blanks look empty in QMF and still fail a CHECK (COL <> ' ') or a COBOL IF COL = SPACES test depending on how you wrote it. If “unknown” should be null, do not use NOT NULL WITH DEFAULT on CHAR. If “unknown” should be a code like '?', name that default explicitly so nobody confuses it with a typed space. DECIMAL defaults of 0 poison averages: AVG(SALARY) includes the zeros. Nullable SALARY plus AVG that skips nulls is often the honest model for “we do not have this number yet.”

Catalog evidence: SYSIBM.SYSCOLUMNS shows DEFAULT, DEFAULTVALUE, and NULLS (Y/N). When a production INSERT fails with SQLCODE -407 (null into NOT NULL), look at those catalog columns before blaming the program. When a column is unexpectedly zero, look for WITH DEFAULT and an omitted column list. EXPLAIN does not show defaults; the catalog and the CREATE TEXT do.

MERGE and UPDATE use the same DEFAULT keyword in SET. INSERT from a fullselect that does not project a NOT NULL column without a default fails even if the source query looks complete. When promoting a SELECT into INSERT … SELECT, list both column lists and confirm every NOT NULL target has either a source expression or a default. That review catches more production -407 errors than any other habit.

Explain It Like I'm Five

A column is a blank on a form. NOT NULL means the blank is not allowed to stay empty with a “I don’t know” stamp. DEFAULT is the pencil mark the teacher writes if you skip that blank: zero for numbers, today’s date for a date blank, the letter A for a status blank. If the teacher forbids empty and also refuses to write a default, you must fill it in yourself or the form is thrown away. Old forms already in the drawer cannot grow a new required blank unless the teacher writes a default on every old form.

Exercises

1. Write a column for STATUS CHAR(1) that is never null and defaults to 'A'.
2. Predict what Db2 stores if INSERT omits a nullable SALARY column with no DEFAULT.
3. Predict what happens if INSERT omits EMPNO CHAR(6) NOT NULL with no default.
4. Name the system default for DATE NOT NULL WITH DEFAULT.
5. Explain why ALTER TABLE ADD COLUMN … NOT NULL (no default) is illegal on a table that already has rows.