TRUNCATE and other DML techniques in DB2

TRUNCATE

TRUNCATE TABLE deletes all rows of a base table or a declared global temporary table. The table may live in a simple, segmented, partitioned, or universal table space. If the table has LOB or XML columns, those auxiliary table spaces and the indexes are truncated as well. Do not confuse this statement with the TRUNCATE numeric function.

You need DELETE privilege (or ownership / DBADM / DATAACCESS / SYSADM). IGNORE DELETE TRIGGERS also requires ALTER-level authority on the table. Row and column access control is not enforced for TRUNCATE.

1
2
3
4
5
6
7
8
9
10
11
12
TRUNCATE TABLE INVENTORY
       DROP STORAGE
       IGNORE DELETE TRIGGERS;
 
TRUNCATE TABLE INVENTORY
       REUSE STORAGE
       IGNORE DELETE TRIGGERS;
 
TRUNCATE TABLE INVENTORY
       REUSE STORAGE
       IGNORE DELETE TRIGGERS
       IMMEDIATE;

DROP STORAGE, REUSE STORAGE, and IMMEDIATE

DROP STORAGE releases space at table-space level so any table in that space can reuse it. A later REORG (without REUSE) is still how shops fully tidy physical files; DROP STORAGE is not “delete the VSAM linear data set.”

REUSE STORAGE keeps the empty pages allocated to this table so the next insert wave does not have to grow storage as aggressively. It is ignored on simple table spaces (treated as DROP STORAGE).

IMMEDIATE means the truncate cannot be undone. The table must not have uncommitted updates; in a multi-table table space, uncommitted changes or uncommitted DDL on any table in the space cause failure. After IMMEDIATE, ROLLBACK still undoes other statements in the unit of work, but the table stays empty. Without IMMEDIATE, ROLLBACK can undo the truncate. IMMEDIATE also lets segmented and UTS tables reclaim space for inserts in the same unit of work without a commit.

Identity restart

Truncating a table does not reset an identity column’s next value on Db2 for z/OS. The next INSERT still continues the sequence. If you emptied a work table and want numbers to start over, alter the identity:

1
2
3
4
5
6
TRUNCATE TABLE HR.EMP_WORK
       REUSE STORAGE
       IGNORE DELETE TRIGGERS;
 
ALTER TABLE HR.EMP_WORK
  ALTER COLUMN WORK_ID RESTART WITH 1;

RESTART WITH is an ALTER TABLE identity attribute, not a TRUNCATE clause (LUW’s RESTART IDENTITY / CONTINUE IDENTITY wording is a different product). Cache gaps can still appear if a restart is rolled back after unused cached values were assigned.

Truncate vs DELETE

TRUNCATE is refused if the table is a parent in an enforced RI constraint, even when the child has no rows. It is also refused for system-period temporal tables. Tables with CDC, multi-level security labels, or VALIDPROC may be processed more like a mass delete (row-by-row checks). Use DELETE when you need WHERE, triggers, or RI cascade.

Searched DELETE, positioned DELETE, and mass DELETE

• Searched DELETE — DELETE FROM t WHERE condition; set-based.
• Positioned DELETE — DELETE FROM t WHERE CURRENT OF cursor; after FETCH.
• Mass DELETE — DELETE FROM t with no WHERE; every row, with triggers and RI.

MATCHED and NOT MATCHED are MERGE clauses, not DELETE clauses: they decide UPDATE/ DELETE versus INSERT when a source is applied to a target. See the MERGE page.

Multi-row INSERT and multiple-row VALUES

Embedded SQL can insert several rows in one INSERT using host-variable arrays and FOR n ROWS. Dynamic SQL can use a VALUES list with several rows. FOR n ROWS may appear on the INSERT or on the EXECUTE of a dynamic INSERT.

1
2
3
INSERT INTO HR.EMP_STAGE (EMPNO, LASTNAME, SALARY)
     VALUES (:HV-EMPNO, :HV-LAST, :HV-SALARY)
     FOR :HV-NROWS ROWS;

Each host-variable array supplies one column. An indicator array is required when the SQLTYPE says the column is nullable. The number of rows must fit the array dimension and the FOR n ROWS count.

NOT ATOMIC CONTINUE ON SQLEXCEPTION

On a multi-row INSERT (and on MERGE with multi-row VALUES), NOT ATOMIC CONTINUE ON SQLEXCEPTION means a bad row does not undo rows already processed in that statement. The statement can return a warning or an error with a mix of successes. Without NOT ATOMIC, one failure fails the whole INSERT.

Use GET DIAGNOSTICS after the statement. ROW_COUNT (or the diagnostics area) tells how many row conditions to inspect. Loop DB2_GET_DIAGNOSTICS_DIAGNOSTICS / GET DIAGNOSTICS CONDITION n for SQLSTATE, SQLCODE, and DB2_ROW_NUMBER so the application can retry or report the failing slots.

1
2
3
4
5
6
GET DIAGNOSTICS :HV-ROWCOUNT = ROW_COUNT;
 
-- Then for i from 1 to HV-ROWCOUNT:
GET DIAGNOSTICS CONDITION :I
    :HV-SQLSTATE = RETURNED_SQLSTATE,
    :HV-SQLCODE  = DB2_RETURNED_SQLCODE;

Multiple-column and row-value assignment

UPDATE (and MERGE UPDATE) can assign several columns at once:

1
2
3
4
5
6
7
8
9
10
UPDATE DSN8C10.EMP
   SET (SALARY, BONUS, COMM) = (NULL, NULL, NULL)
 WHERE EMPNO = '000250';
 
UPDATE HR.EMP_WORK E
   SET (DEPTNAME, LOCATION) =
       (SELECT D.DEPTNAME, D.LOCATION
          FROM HR.DEPARTMENT D
         WHERE D.DEPTNO = E.WORKDEPT)
 WHERE E.EMPNO = :HV-EMPNO;

The first form is a list of scalars. The second is a row-fullselect: one row, as many columns as the parenthesized target list. Zero rows assign nulls; extra rows are an error. Correlated UPDATE is the same idea with a correlation name on the target so the subquery can see the current row.

Explain It Like I'm Five

Mass DELETE is throwing every card in the drawer into the bin, one by one, while a librarian writes each name down (triggers) and checks family rules (RI). TRUNCATE is dumping the whole drawer at once. DROP STORAGE is putting the empty drawer back on the shared shelf; REUSE STORAGE is leaving the empty drawer on your desk. IMMEDIATE means you already burned the cards—you cannot glue them back. Multi-row INSERT is stuffing a handful of new cards in one motion; NOT ATOMIC means if one card is torn, the ones already in the drawer stay in.

Exercises

1. Write TRUNCATE for WORK.LOAD_ERR with REUSE STORAGE and IGNORE DELETE TRIGGERS. When would you add IMMEDIATE?
2. A table is a parent in a FOREIGN KEY. Why does TRUNCATE fail even if the child has zero rows? What statement would you use instead?
3. After truncating a table with an identity column, the next INSERT gets 50001 not 1. Write the ALTER that restarts at 1.
4. Explain when you want NOT ATOMIC CONTINUE ON SQLEXCEPTION on a 100-row INSERT, and what you read with GET DIAGNOSTICS.
5. Convert three SET col = … assignments into one multiple-column assignment that uses a row-fullselect.