ALTER TABLE and ALTER TABLESPACE in DB2

ALTER TABLE

When you alter a table you change the specifications used to create it. You do not, by that statement alone, rewrite every business value. IBM lists a long set of ALTER TABLE capabilities. Beginners use a handful every week:

1
2
3
4
5
6
7
8
9
10
11
ALTER TABLE HR.EMPLOYEE
  ADD COLUMN STATUS CHAR(1) NOT NULL WITH DEFAULT 'A';
 
ALTER TABLE HR.EMPLOYEE
  RENAME COLUMN COMM TO COMMISSION;
 
ALTER TABLE HR.EMPLOYEE
  ALTER COLUMN LASTNAME SET DATA TYPE VARCHAR(30);
 
ALTER TABLE HR.EMPLOYEE
  ADD CONSTRAINT EMP_SAL_CHK CHECK (SALARY >= 0);

Add, rename, drop columns

ADD COLUMN appends a column. It must be nullable or NOT NULL WITH DEFAULT because old rows already exist. SELECT * programs can break; explicit column lists survive. Some ADD COLUMN forms put the table space in AREOR (advisory REORG-pending). Data stays available. REORG or LOAD REPLACE materializes the new layout.

RENAME COLUMN changes the catalog name. Views, indexes, and packages that referenced the old name need attention. Triggers and constraints that name the column must match.

DROP COLUMN on a universal table space is typically a pending change. You cannot drop certain columns (partitioning keys, columns required by unique constraints you have not dropped, and other restricted cases). Until REORG, further DDL on the same object may be blocked. Pending changes have a documented list of statements that are not allowed until REORG runs.

Data type changes

ALTER COLUMN SET DATA TYPE supports compatible changes—for example VARCHAR(20) to VARCHAR(30), or SMALLINT to INTEGER within product rules. Db2 uses table space versioning: it can keep up to 256 versions (0–255). Version 0 is a space that has never been altered. Rows convert to the latest version as they are updated or when REORG rewrites the space. You do not always unload the table.

Identity column data types are not in that “just ALTER” list; drop and recreate the table. Some LOB, ROWID, and distinct-type changes also need unload/reload. When ALTER refuses, the Administration Guide path is: unload, drop, recreate, load, rebuild indexes, rebind.

Keys, checks, partitions, and options

You can ADD or DROP PRIMARY KEY, UNIQUE, FOREIGN KEY, and CHECK constraints (subject to existing data passing the new rule). You can ADD PARTITION, change partition limits, ROTATE partitions, or instruct Db2 where new rows go on partitioned tables. Other clauses cover DATA CAPTURE, AUDIT ALL/CHANGES/NONE, VALIDPROC, RESTRICT ON DROP, clone tables, APPEND, and activating row or column access control.

Identity attributes (RESTART WITH, ALWAYS versus BY DEFAULT) are ALTER COLUMN on that identity column—covered on the generated-columns page.

1
2
3
4
5
6
7
8
ALTER TABLE HR.EMPLOYEE
  ADD CONSTRAINT FK_DEPT
    FOREIGN KEY (WORKDEPT)
    REFERENCES HR.DEPARTMENT (DEPTNO)
    ON DELETE SET NULL;
 
ALTER TABLE HR.EMPLOYEE
  DROP CONSTRAINT EMP_SAL_CHK;

Pending changes, AREOR, and locks

Modern z/OS schema work is online schema evolution: many alters do not require an outage. Two ideas matter:

• Immediate catalog change — the new definition is visible; old rows may still be in an older version format until updated or reorganized
• Pending definition change — the change is recorded and applied by REORG (common for DROP COLUMN, some table space attributes, some index changes)

AREOR is advisory, not a stop. Applications continue. Utilities and DBAs still schedule REORG so version slots do not fill up and so performance stays predictable. If pending changes exist, a second DROP, CREATE, or ALTER on the same objects may fail until REORG materializes the first change.

ALTER TABLE still needs a lock. It waits for claimers up to IRLMRWT. A started-task program that SELECTs and never COMMITs can hold a claim for hours and time out the alter. -DISPLAY BLOCKERS and SYSIBMADM.BLOCKING_THREADS find those threads. Commit in read-only loops if your shop’s DDL window must succeed.

Some alters invalidate packages. IBM publishes “changes that invalidate packages.” Rebind after those alters or the first execution auto-rebinds with a surprise CPU spike. Plan the rebind in the same change ticket as the ALTER.

ALTER TABLESPACE

The table lives in a table space. Physical knobs are ALTER TABLESPACE, not ALTER TABLE.

1
2
3
4
5
6
7
8
9
ALTER TABLESPACE HRDB.HRTS
  PCTFREE 20
  FREEPAGE 10;
 
ALTER TABLESPACE HRDB.HRTS
  COMPRESS YES;
 
ALTER TABLESPACE HRDB.HRTS
  BUFFERPOOL BP2;

PCTFREE and FREEPAGE affect how REORG and LOAD lay out pages; they do not instantly spread existing pages. COMPRESS YES commonly needs REORG to compress existing data. BUFFERPOOL changes which pool new I/O uses. MAXPARTITIONS and DSSIZE apply to partition-by-growth spaces and can be pending. MEMBER CLUSTER, TRACKMOD, LOCKSIZE, and DEFINE are other space-level attributes you will meet in the table-space pages.

Do not confuse ALTER TABLESPACE with ALTER TABLE ADD PARTITION. Adding a partition to a partitioned table is often ALTER TABLE ADD PARTITION. Changing how large those partitions may grow can be ALTER TABLESPACE. Read the syntax diagram for the object you actually named.

If the attribute you want cannot be altered, the fallback is the same as for illegal column type changes: unload, drop the table space (which drops the table), recreate, load. That is why shops prefer UTS PBG/PBR designs that support pending alters instead of ancient multi-table simple spaces.

A safe alter checklist

• Confirm the object: table versus table space versus index
• Check blockers and a quiet-enough window
• Check whether the change invalidates packages
• Run the ALTER; DISPLAY DATABASE to see AREOR or AREO*
• Schedule REORG (and COPY, RUNSTATS, REBIND) as the materialization step
• Test SELECT lists and COBOL copybooks if you added or renamed columns

Beginners should treat ALTER as a two-step dance: SQL then utilities. The SQL succeeding is not always the end of the change.

ALTER TABLE versus unload/drop/create

Native ALTER is the default. You unload and recreate when the product cannot alter in place: certain data-type conversions, changing an identity column’s type, moving a table to a table space with a different page size, or abandoning a table space type that no longer supports the attribute you need. DBA tools often call the recreate path “alter by rename” or ALT: they rename the old table, create the new one, copy data, and switch dependents. That path needs twice the disk, a change window, and a backout plan.

Online schema evolution still has a version limit. If DISPLAY shows you approaching version 255, REORG before the next type-changing ALTER. Ignoring versions is how a “simple” VARCHAR length change fails on a Friday night.

Clones (ADD CLONE / DROP CLONE) are another ALTER TABLE family: a clone is an empty same-structure table you EXCHANGE with the base for near-online load. That is beyond this intro but it is why you will see ALTER TABLE ADD CLONE in DBA scripts next to ordinary ADD COLUMN.

Always keep the CREATE TABLE source of truth in version control and generate ALTER scripts from a diff. Hand-editing production catalogs without a replayable script is how two subsystems drift. COMMENT ON after an alter helps the next person, but it is not a substitute for the DDL repository.

ALTER TABLE ADD PARTITION and ALTER TABLE ALTER PARTITION change limit keys on partition-by-range tables. Rotating partitions (ROTATE PARTITION FIRST TO LAST) is how some shops implement a rolling history window. Those alters interact with DPSIs and NPSIs: a partition-level REORG is easier when secondary indexes are DPSIs. If you add a partition and forget to copy the new data set pieces for NPSIs, utilities complain. Coordinate table alters with index design, not after the fact.

ALTER TABLESPACE LOCKSIZE (ANY, PAGE, ROW, TABLESPACE) changes the default locking granularity for SQL that does not specify WITH RR/CS options at the statement level in the way you might think—LOCKSIZE is a space attribute the optimizer and lock manager honor. Changing LOCKSIZE ROW on a busy space can increase lock storage in IRLM. That alter is a performance decision, not a column decision. Keep it on the table-space change ticket with monitoring, not on the “add STATUS column” ticket.

DISPLAY DATABASE … SPACENAM … LIMIT(*) after an alter is the beginner habit. Look for AREOR, AREO*, RESTP, and advisory statuses. If SQL works and DISPLAY is clean, you are done. If SQL works and AREOR is on, you are half done: schedule REORG.

ALTER TABLE DROP CONSTRAINT is how you retire a check or foreign key without dropping the table. Data stays; future inserts are no longer checked. If you are dropping a foreign key to allow a parent DROP TABLE, do it in a documented order: drop children constraints, drop parent, recreate if needed. Never DROP CONSTRAINT in production because a load failed once—fix the load data.

Renaming a column does not rewrite COBOL copybooks. The DCLGEN must be regenerated and programs rebound. Schedule application work in the same change as RENAME COLUMN or you will ship a catalog that no longer matches FETCH INTO lists.

Explain It Like I'm Five

ALTER TABLE is rewriting the label on a toy box: “this box now also has a STATUS sticker.” Old toys already in the box get a default sticker so they are not illegal. ALTER TABLESPACE is moving the box to a bigger shelf or squeezing the toys tighter (compression). Sometimes the teacher writes the new rule in the grade book immediately but only restuffs the box at cleanup time (REORG). Until cleanup, the new rule is still real, but the toys on the shelf might still be in the old wrapping.

Exercises

1. Write ALTER TABLE to add NOTES VARCHAR(100) that may be null.
2. Write ALTER TABLE to add STATUS CHAR(1) NOT NULL WITH DEFAULT 'A'.
3. Explain why ADD COLUMN … NOT NULL with no default fails.
4. Write ALTER TABLESPACE to set PCTFREE 15 on HRDB.HRTS.
5. Name the utility that materializes many pending definition changes and clears AREOR.