Identity and generated columns in DB2 (intro)

Identity columns

An identity column holds a numeric value that Db2 can generate in sequence as rows are inserted. It is a common surrogate primary key: you do not pick employee numbers by hand. Define the column with AS IDENTITY. The data type must be an exact numeric with scale zero: SMALLINT, INTEGER, BIGINT, or DECIMAL(n,0) (or a distinct type based on one of those). Floating-point types are not allowed.

A table may have only one identity column. Identity is not the same as ROWID. ROWID is its own type for direct addressing; identity is an ordinary integer you can show on screens and use as a foreign key.

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE HR.EMPLOYEE (
  EMPID     INTEGER NOT NULL
              GENERATED ALWAYS AS IDENTITY
              (START WITH 100
               INCREMENT BY 1
               NO CYCLE
               CACHE 20),
  LASTNAME  VARCHAR(15) NOT NULL,
  SALARY    DECIMAL(9,2)
) IN HRDB.HRTS;
 
CREATE UNIQUE INDEX HR.EMPLOYEE_PK
  ON HR.EMPLOYEE (EMPID);

Common identity options:

• START WITH — first value to generate (can sit outside MIN/MAX; the next cycle value still respects MINVALUE/MAXVALUE)
• INCREMENT BY — step; negative values count down
• MINVALUE / MAXVALUE — bounds for cycling
• CYCLE / NO CYCLE — whether values may wrap and repeat
• CACHE / NO CACHE — preallocate numbers in memory for speed; unused cached values can be skipped after a crash or rollback, leaving gaps
• ORDER / NO ORDER — whether values must be assigned in request order (data sharing cares about this)

Gaps are normal. A rolled-back INSERT, a cached block that was never used, or a restart can skip numbers. Identity is not a gap-free invoice sequence unless you design extra controls. Do not use it as a row count.

Uniqueness is not automatic

IBM is explicit: if you want uniqueness, define the column GENERATED ALWAYS and NO CYCLE and create a unique index on it. AS IDENTITY without that index can still produce duplicates if CYCLE is on or if BY DEFAULT lets users insert colliding values. Treat the unique index (or PRIMARY KEY) as part of the identity design, not an optional extra.

GENERATED ALWAYS

GENERATED ALWAYS means Db2 always produces the value for that column on INSERT (and, for some generated types, on UPDATE). For identity, you do not list EMPID in the INSERT column list. ALWAYS is the recommended setting unless you are doing data propagation or unload and reload of existing keys.

1
2
3
INSERT INTO HR.EMPLOYEE (LASTNAME, SALARY)
  VALUES ('HAAS', 52750.00);
-- EMPID is assigned by Db2

If a program must see the number it just got, use SELECT FROM FINAL TABLE (INSERT …) or IDENTITY_VAL_LOCAL() in the same session with care (it returns the value from a previous INSERT identity assignment in that application process—read the function’s rules before relying on it in nested calls).

Special INSERT clauses OVERRIDING SYSTEM VALUE and OVERRIDING USER VALUE exist for identity and some other generated columns when you truly must force a value or force generation. Beginners should not need them on day one. ALWAYS plus a unique index is the default design.

GENERATED BY DEFAULT

GENERATED BY DEFAULT means Db2 generates a value when you omit the column or specify DEFAULT, but an explicit value in INSERT is accepted. IBM recommends BY DEFAULT for propagation and UNLOAD/LOAD of tables that already have keys.

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE HR.EMPLOYEE (
  EMPID    INTEGER NOT NULL
             GENERATED BY DEFAULT AS IDENTITY,
  LASTNAME VARCHAR(15) NOT NULL
) IN HRDB.HRTS;
 
-- Let Db2 assign
INSERT INTO HR.EMPLOYEE (LASTNAME) VALUES ('KWAN');
 
-- Keep an old key during reload
INSERT INTO HR.EMPLOYEE (EMPID, LASTNAME) VALUES (7, 'PULASKI');

After you insert a high explicit value, the internal counter may still be behind. You can hit duplicate-key errors on the unique index until you ALTER … RESTART WITH a number past the loaded keys. Plan restarts as part of every reload playbook.

Generated columns beyond identity

ROW CHANGE TIMESTAMP is a timestamp Db2 maintains. Define it NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP. Inserts and updates refresh it. It is how you ask “when did this row last change?” without a trigger.

1
2
3
4
5
6
7
8
9
CREATE TABLE HR.CUSTOMER (
  CUSTNO CHAR(8) NOT NULL,
  CUST_INFOCHANGE NOT NULL
    GENERATED ALWAYS
    FOR EACH ROW ON UPDATE
    AS ROW CHANGE TIMESTAMP,
  CUST_NAME VARCHAR(50),
  PRIMARY KEY (CUSTNO)
) IN HRDB.HRTS;

Later Db2 versions also allow non-deterministic generated expression columns such as GENERATED ALWAYS AS (SESSION_USER) or AS (DATA CHANGE OPERATION) for audit trails, especially with temporal tables. Those are CHAR/VARCHAR columns whose expression is a special register or operation code, not arbitrary arithmetic.

Do not copy LUW examples like GENERATED ALWAYS AS (C1 + 10) onto z/OS and expect CREATE TABLE to succeed. That computed-column style is not the z/OS identity feature. If you need a stored C1 + 10, use a BEFORE INSERT/UPDATE trigger, or compute it in the SELECT list and do not store it.

Altering identity attributes

ALTER TABLE ALTER COLUMN can change identity attributes (ALWAYS versus BY DEFAULT, RESTART WITH, CACHE, and so on) except the data type. If the ALTER is rolled back, a gap can appear because cached values were already consumed. Changing the data type of an identity column requires dropping and recreating the table.

1
2
3
ALTER TABLE HR.EMPLOYEE
  ALTER COLUMN EMPID
    SET GENERATED ALWAYS RESTART WITH 1000;

RESTART WITH, like START WITH, can sit outside MINVALUE/MAXVALUE; the next generated value after that restart still follows the cycle rules. Document RESTART in change control—running it twice in production is how teams mint colliding keys if CYCLE is on or the unique index was never created.

Recovery of tables with identity columns has extra notes in the Administration Guide (regenerating missing identity values after certain recoveries). Coordinate with DBAs after a point-in-time recovery; the counter in the catalog and the max key on disk can disagree.

Identity versus sequences versus ROWID

Db2 also has CREATE SEQUENCE objects. A sequence is not tied to one table; NEXT VALUE FOR seq can feed several tables or a host variable. Identity is baked into one column of one table. Use identity when the number belongs to that table forever. Use a sequence when several tables share a numbering scheme or when the application must fetch a number before INSERT.

ROWID is a different generated identifier: an internally generated value used for direct row access and for some LOB locators. You do not treat ROWID as a friendly invoice number. You do not join on ROWID across tables as a business key. Identity integers are what you print on documents. If a table has both, they solve different problems.

In COBOL, FETCH an identity column into a PIC S9(9) COMP or BIGINT host variable that matches the SQL type. Do not FETCH into a string and hope. SELECT FROM FINAL TABLE (INSERT INTO …) is the clean way to get generated keys in one round trip without a second SELECT MAX, which is wrong under concurrency.

CACHE 20 (or larger) is a performance knob: Db2 hands out a block of numbers per member so data-sharing members do not serialize on every INSERT. The cost is larger gaps after a member fails. NO CACHE + ORDER is the strictest numbering and the slowest in a data sharing group. Most OLTP tables should accept gaps.

GENERATED ALWAYS identity columns still appear in SELECT. They are ordinary integers once stored. You can ORDER BY them, join on them, and use them as foreign keys in child tables. The child table does not use AS IDENTITY for the foreign key; it stores the parent’s number as a normal NOT NULL INTEGER (or BIGINT) with a FOREIGN KEY. Only the parent generates.

LOAD REPLACE of a table with GENERATED ALWAYS identity usually needs IDENTITYOVERRIDE or a similar load option, or you load into a GENERATED BY DEFAULT table. UNLOAD from production and LOAD into QA must keep keys if children already reference them. That is the practical reason BY DEFAULT exists. After LOAD, RESTART WITH max(key)+1 so the next online INSERT does not collide. Skip that step and the unique index returns SQLCODE -803 on the first new row.

ROW CHANGE TIMESTAMP is not a substitute for a business “last updated by” name. Pair it with SESSION_USER generated columns or with USER defaults if you need both when and who. Triggers can still do extra work (write to an audit table) that generated columns cannot.

CYCLE is almost never what an employee-id wants. When the sequence hits MAXVALUE with NO CYCLE, INSERT fails until you ALTER RESTART or widen the type. Plan MAXVALUE for BIGINT identities so you do not hit the wall in five years. SMALLINT identities look cute in demos and run out at 32,767. INTEGER is the usual start; BIGINT if the table is an event log.

GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP requires NOT NULL and a timestamp type. You do not UPDATE that column yourself. If a load job must preserve old change times, that is a BY DEFAULT row-change-timestamp design or a load override— read the LOAD documentation for the exact option on your version rather than guessing.

Application Compatibility (APPLCOMPAT) can gate newer generated-expression syntax. If CREATE TABLE with SESSION_USER generated columns fails, check APPLCOMPAT and the SQL Reference for your Db2 12/13 function level before rewriting as a trigger.

Explain It Like I'm Five

An identity column is a ticket dispenser. ALWAYS means only the machine prints tickets; you may not write a number with a crayon. BY DEFAULT means the machine prints a ticket if you forget, but you may hand in an old ticket with a number already on it (reload). The dispenser can skip numbers if tickets fall on the floor (gaps). A unique index is the rule that two people cannot hold the same number. A row-change timestamp is a stamp the machine thumps on the ticket every time someone scribbles on it.

Exercises

1. Write an INTEGER identity column GENERATED ALWAYS starting at 1, increment 1, NO CYCLE.
2. Add the CREATE UNIQUE INDEX that should accompany it.
3. Explain when you would choose GENERATED BY DEFAULT instead.
4. Write ALTER TABLE … RESTART WITH 5000 for that column.
5. State whether GENERATED ALWAYS AS (SALARY * 1.1) is a z/OS CREATE TABLE feature, and what you would use instead.