Chapter 16: Key Takeaways

  1. Schema change is normal, not exceptional. A healthy production database evolves continuously — monthly column additions, quarterly table additions, annual structural changes. The goal is not to prevent change but to make change repeatable, reversible, and safe. Schema rigidity leads to workarounds (column overloading, shadow tables, EAV patterns) that are far more costly than managed evolution.

  2. Every schema change must be scripted, reviewed, tested, and reversible. Never type ALTER TABLE directly into a production console. Write the change as a SQL script, peer-review it, test it in lower environments, write and test the rollback script, and execute through a controlled deployment process. This discipline is the difference between a DBA who sleeps through the night and one who gets paged at 3 AM.

  3. Adding a nullable column is the safest ALTER TABLE operation. On both z/OS and LUW, adding a column with NULL default is essentially instantaneous regardless of table size. DB2 records the new column in the catalog without touching existing data pages. This is the operation you should reach for first whenever possible.

  4. Know which ALTER operations require REORG and which do not. VARCHAR widening is instant. CHAR widening triggers REORG PENDING on z/OS. INTEGER to BIGINT triggers REORG PENDING on both platforms. Adding CHECK or FOREIGN KEY constraints does not require REORG but does validate existing data. Keep the impact table from Section 16.2.5 bookmarked.

  5. On z/OS, understand and monitor pending states. REORG PENDING makes data inaccessible. ADVISORY REORG recommends but does not require action. Check pending states through DISPLAY DATABASE or the Real-Time Statistics tables. Batch multiple ALTERs before a single REORG. Use SHRLEVEL(CHANGE) REORG with a mapping table for online reorganization.

  6. On LUW, use inplace REORG for online reorganization. Inplace REORG allows full read/write access during reorganization and can be paused, resumed, and stopped. Use REORGCHK to determine when reorganization is actually needed — do not REORG on a schedule. The three formulas (F1: overflow rows, F2: free space, F3: page fetch efficiency) tell you when the data warrants reorganization.

  7. Use expand-then-contract for complex schema changes. When ALTER TABLE alone cannot accomplish the change — converting CHAR to VARCHAR, restructuring tables, transforming data types — the expand-then-contract pattern provides a safe, phased approach. Add the new structure, migrate applications, then remove the old structure. Each phase is independently deployable and reversible.

  8. Separate safe changes from risky changes in your deployment plan. Nullable column additions (instant) should not be bundled with BIGINT conversions (REORG required). Execute safe changes during business hours to free up maintenance windows for the operations that actually need them.

  9. Data migration uses different tools on each platform. z/OS uses UNLOAD/LOAD for high-speed data movement. LUW uses EXPORT/IMPORT for flexibility and LOAD for speed. db2move handles bulk schema migration on LUW. For large migrations, parallelize by partition or table, and always validate with row counts, checksums, and spot-check comparisons.

  10. Oracle-to-DB2 migration requires careful attention to DATE types, exception handling, and stored procedure semantics. Map Oracle DATE to DB2 TIMESTAMP (never to DB2 DATE). Convert PL/SQL exception blocks to DB2 DECLARE HANDLER with attention to execution flow differences. Use the IBM Database Conversion Workbench for automated conversion of 70-85% of objects, and budget significant manual effort for the remainder.

  11. Version-to-version migration follows a phased approach with built-in fallback. z/OS uses Compatibility Mode, Enabling New Function Mode, and New Function Mode — with fallback possible until NFM. LUW uses db2iupgrade and UPGRADE DATABASE — with fallback requiring restore from backup. Always take a complete, tested backup before any version upgrade. Always REBIND packages after upgrade.

  12. Zero-downtime migration is achievable with the right pattern. Blue-green database deployment, rolling migration in z/OS data sharing groups, and HADR-based migration on LUW each provide near-zero or actual-zero downtime for major changes. The z/OS data sharing rolling migration is the gold standard — upgrading members one at a time while the group remains fully operational.