DROP and object naming in DB2: RENAME, COMMENT, LABEL

DROP is not DELETE

DELETE FROM HR.EMPLOYEE removes rows. The table definition stays. DROP TABLE HR.EMPLOYEE removes the object. Catalog rows, data sets (when Db2-managed), indexes, and often views disappear with it. After COMMIT, there is no ROLLBACK. Restore means image copies plus CREATE scripts from source control, or a documented DBA recovery procedure.

DROP DATABASE

A Db2 database is a catalog grouping of table spaces, not a separate product install. DROP DATABASE dbname drops that database and the table spaces it contains—therefore the tables and indexes in those spaces.

1
DROP DATABASE HRDB;

This is a wide blast radius. Production shops rarely let application IDs issue it. SYSADM or a controlled change ID does. If any table in the database was created WITH RESTRICT ON DROP, DROP DATABASE fails until you ALTER TABLE … DROP RESTRICT ON DROP. That restriction exists so a scripted DROP DATABASE cannot wipe a critical table by accident.

DROP TABLESPACE

DROP TABLESPACE dbname.tsname drops the table space and every table in it. On recommended universal table spaces that is usually one table, plus all of its indexes and auxiliary objects (LOB/XML related spaces can be involved). Older multi-table segmented spaces could lose several tables at once—one reason they are out of fashion.

1
DROP TABLESPACE HRDB.HRTS;

Pending definition changes can block DROP until REORG materializes them. STOP the space if utilities hold it. Privileges: you need the right to drop that space, typically DBADM, SYSADM, or ownership patterns your shop defined.

DROP TABLE

DROP TABLE schema.table drops that table. Indexes defined on it are dropped. Views that depend on it are dropped. Aliases that pointed at it remain as broken names until you DROP ALIAS. Packages that referenced the table become invalid.

1
DROP TABLE HR.EMPLOYEE;

Foreign keys in other tables that referenced this table as a parent must be dropped first, or DROP fails with a dependent-object error. Check SYSIBM.SYSRELS / SYSIBM.SYSFOREIGNKEYS (and related catalog views) before a production DROP. Synonyms and aliases are extra names; dropping the table does not always drop every alias.

Implicitly created table spaces (when CREATE TABLE did not name IN db.ts) may be dropped automatically when the last table goes away, depending on how the table was created. Explicit table spaces remain until DROP TABLESPACE. Know which model your object uses before you assume the VSAM linear data set vanished.

DROP INDEX

DROP INDEX schema.index removes only the index. The table and its data stay. Queries that used that index pick another access path after the next prepare/bind. Unique indexes that support PRIMARY KEY or UNIQUE constraints cannot be dropped while the constraint exists—drop the constraint first, or DROP TABLE if you are discarding the whole object.

1
DROP INDEX HR.EMP_DEPT_IX;

Clustering indexes can be dropped; the table is then without a clustering index until you CREATE INDEX … CLUSTER or ALTER another index to CLUSTER. Partitioning indexes have extra rules on older index-controlled partitioned tables. Prefer table-controlled partitioning so dropping a secondary index does not redefine partition boundaries.

RESTRICT ON DROP

CREATE TABLE … WITH RESTRICT ON DROP (or ALTER TABLE ADD RESTRICT ON DROP) marks the table so DROP TABLE, DROP TABLESPACE of its space, and DROP DATABASE of its database are rejected. Remove it with ALTER TABLE … DROP RESTRICT ON DROP when the table is truly retired. Use it on tables that would be catastrophic to lose in a cleanup script.

1
2
3
4
ALTER TABLE HR.EMPLOYEE ADD RESTRICT ON DROP;
-- DROP TABLE HR.EMPLOYEE;  -- fails while restriction is on
ALTER TABLE HR.EMPLOYEE DROP RESTRICT ON DROP;
DROP TABLE HR.EMPLOYEE;

RENAME

RENAME TABLE and RENAME INDEX change the SQL name without unloading data. The qualified schema usually stays; you change the identifier.

1
2
RENAME TABLE HR.EMPLOYEE TO EMP;
RENAME INDEX HR.EMPLOYEE_PK TO EMP_PK;

Views, aliases, and static SQL still contain the old name until you change and rebind them. RENAME is not a substitute for CREATE ALIAS if you needed a second name pointing at the same table. Privileges on the table generally follow the object, not the string you used to type it—but test grants after a rename in your shop.

You cannot always rename a table that has certain pending changes, clones, or active restrictions. If RENAME fails, read the SQLCODE; the Administration Guide lists unsupported cases. Unload/drop/create/load remains the hammer.

COMMENT ON

COMMENT ON stores a remark in the catalog. It does not affect SQL execution. It is how you document “EMPNO is the employee identifier from HR” next to the object instead of only in a wiki.

1
2
3
4
5
COMMENT ON TABLE HR.EMPLOYEE IS
  'Employee master — source of record for payroll';
 
COMMENT ON COLUMN HR.EMPLOYEE.EMPNO IS
  'Employee identifier, CHAR(6), not null';

Remarks live in catalog columns (SYSIBM.SYSTABLES.REMARKS, SYSIBM.SYSCOLUMNS.REMARKS, and similar). Many DBA tools display them. Replace a comment by issuing COMMENT ON again. An empty string can clear a remark depending on product rules—prefer an explicit new sentence.

LABEL ON

LABEL ON assigns a label, a short external name aimed at end-user tools such as QMF. The SQL name remains EMPNO; the label might be “Employee number.” Labels are not identifiers in SELECT lists unless a tool substitutes them.

1
2
3
4
LABEL ON TABLE HR.EMPLOYEE IS 'Employees';
 
LABEL ON COLUMN HR.EMPLOYEE.EMPNO IS 'Employee number';
LABEL ON COLUMN HR.EMPLOYEE.LASTNAME IS 'Last name';

LABEL ON COLUMN can name several columns in one statement. Labels have length limits (commonly 30 bytes for column labels in classic QMF usage). They are stored in catalog label columns, separate from REMARKS. Use COMMENT for DBA documentation and LABEL for report headings when your shop still runs QMF or equivalent.

Object naming hygiene

Two-part names (schema.object) belong in DROP, RENAME, COMMENT, and LABEL just as in CREATE. Unqualified DROP TABLE EMPLOYEE uses CURRENT SCHEMA or the bind QUALIFIER and can hit the wrong object. Put DROP scripts in source control next to CREATE scripts. Qualify every name. Avoid dropping in the same changeset as an untested CREATE without a backout copy.

Authorization: DROP requires ownership, DROP privilege, or an admin authority. COMMENT and LABEL typically need ALTER or ownership on the object. A developer who can SELECT cannot necessarily DROP. That split is intentional.

Dependents, invalid packages, and recovery

Before DROP TABLE, list dependents: indexes (will go automatically), views, MQTs, triggers, aliases, foreign keys the other way, row permissions, column masks, and clone relationships. SYSCAT-style tools and IBM catalog queries exist for this. Dropping a parent of a FOREIGN KEY fails until the child constraint is dropped. Dropping a table that a static COBOL package uses invalidates that package; the next run autobinds or fails depending on bind options.

DROP is logged, but you cannot ROLLBACK after COMMIT. Point-in-time recovery of a dropped table is a DBA procedure involving image copies, the log, and often CREATE TABLE from DDL, not a developer UNDO. If you dropped the wrong table space, stop and call the DBA. Re-creating an empty table with the same name does not restore the rows.

Implicit databases and table spaces created by CREATE TABLE IN DATABASE or by omitted IN clauses can surprise DROP: dropping the last table may drop an implicitly created space. Explicit HRDB.HRTS stays until DROP TABLESPACE. Document which objects are implicit in the DDL repository so cleanup scripts do not leave orphaned data sets or drop the wrong container.

COMMENT and LABEL survive ALTER TABLE ADD COLUMN for old columns; new columns start without remarks until you COMMENT ON them. After RENAME TABLE, comments follow the object in the catalog, but your wiki links and GRANT scripts that used the old string still need an update. Treat RENAME as a search-and-replace across the shop, not a one-line SQL change.

DROP INDEX on a unique index that is not supporting a constraint is still a production event: the next INSERT can insert duplicates the business thought were impossible, and the next SELECT can tablespace-scan. If you drop an index to speed a load, REBUILD or CREATE it again before online traffic returns. DEFER YES CREATE INDEX plus REBUILD is cleaner than DROP/CREATE when you only needed the index absent during LOAD.

DROP DATABASE cannot be issued while the database is in use by active threads in the usual way; STOP DATABASE and drain claimers first. The same is true of DROP TABLESPACE. Beginners who test DROP in SPUFI against a table they still have open in another session will wait or time out. CLOSE the cursor, COMMIT, then DROP.

Object names are 128-byte identifiers in current Db2 for z/OS (ordinary or delimited). Older limits of 8-byte table-space names still haunt physical objects: table space and index space names have historically been shorter than table names. RENAME TABLE does not rename the table space. COMMENT ON TABLESPACE exists for the space. When you document objects, comment both the table people query and the space DBAs REORG.

Explain It Like I'm Five

DELETE throws away the papers inside a folder. DROP throws away the folder. DROP TABLESPACE throws away the drawer. DROP DATABASE throws away the cabinet. RESTRICT ON DROP is a padlock that says “do not throw this cabinet away.” RENAME writes a new name on the folder tab. COMMENT is a sticky note for grown-ups who maintain the cabinet. LABEL is the pretty sticker QMF shows on a report so people read “Last name” instead of LASTNAME.

Exercises

1. Write DROP INDEX for HR.EMP_DEPT_IX and say whether the table survives.
2. Write DROP TABLESPACE HRDB.HRTS and list what else disappears.
3. Add RESTRICT ON DROP to HR.EMPLOYEE, then show the ALTER that removes it.
4. COMMENT ON COLUMN for WORKDEPT explaining it is a CHAR(3) department code.
5. LABEL ON COLUMN LASTNAME with a user-friendly heading.