Chapter 16 Exercises: Schema Evolution and Data Migration

These exercises progress from foundational recall through applied production scenarios. Difficulty ratings: [Beginner], [Intermediate], [Advanced], [Challenge].

Section A: ALTER TABLE Fundamentals (Exercises 1-8)

Exercise 1 — Safe vs. Unsafe ALTER Operations [Beginner]

Classify each of the following ALTER TABLE operations as "Instant" (no REORG required), "Advisory REORG" (data accessible, REORG recommended), or "REORG PENDING" (data inaccessible until REORG). Specify for both z/OS and LUW where behavior differs.

a) ALTER TABLE T ADD COLUMN NEW_COL VARCHAR(50) b) ALTER TABLE T ALTER COLUMN NAME SET DATA TYPE VARCHAR(200) (was VARCHAR(100)) c) ALTER TABLE T ALTER COLUMN CODE SET DATA TYPE CHAR(10) (was CHAR(5)) d) ALTER TABLE T ALTER COLUMN ID SET DATA TYPE BIGINT (was INTEGER) e) ALTER TABLE T ADD CONSTRAINT CK_STATUS CHECK (STATUS IN ('A','I')) f) ALTER TABLE T DROP COLUMN OLD_COL

Exercise 2 — Writing Safe Column Additions [Beginner]

Write ALTER TABLE statements to add the following columns to the MERIDIAN.CUSTOMER table. Each statement must be safe for execution during business hours (no REORG PENDING).

a) A column for storing the customer's preferred notification method (email, SMS, or mail), defaulting to email. b) A column for storing the customer's date of last KYC (Know Your Customer) review, initially NULL. c) A column for storing a risk score as a decimal with 2 decimal places. d) A column for a boolean-like flag indicating whether the customer has opted into marketing communications, defaulting to 'N'.

Exercise 3 — Constraint Impact Analysis [Intermediate]

You need to add the following constraints to MERIDIAN.ACCOUNT, which has 12 million rows. For each constraint, describe: (a) what DB2 must do when the ALTER runs, (b) how long it might take, (c) what locks are acquired, and (d) what happens if existing data violates the constraint.

a) ADD CONSTRAINT CK_BAL CHECK (BALANCE >= 0) b) ADD CONSTRAINT FK_BRANCH FOREIGN KEY (BRANCH_ID) REFERENCES MERIDIAN.BRANCH(BRANCH_ID) c) ALTER COLUMN STATUS SET NOT NULL

Exercise 4 — Pre-Drop Dependency Check [Intermediate]

Write the SQL queries you would run on both z/OS and LUW to find all objects that depend on the table MERIDIAN.AUDIT_LOG before dropping it. Your queries should check for:

a) Foreign keys referencing the table b) Views that depend on the table c) Stored procedures and functions that reference the table d) Packages bound against the table (z/OS) e) Triggers defined on the table f) Materialized Query Tables (MQTs) based on the table

Exercise 5 — Column Widening Strategy [Intermediate]

The MERIDIAN.BRANCH table has a BRANCH_NAME column defined as VARCHAR(50). Management has decided that branch names need to support up to 150 characters to accommodate international branches. Additionally, the BRANCH_CODE column is CHAR(5) and needs to become CHAR(10).

a) Write the ALTER statements for both changes. b) Which change is safe during business hours? Which requires a maintenance window on z/OS? c) What is your recommended execution order and why? d) If you could redesign the BRANCH_CODE column from scratch, would you choose CHAR or VARCHAR? Why?

Exercise 6 — NOT NULL Migration [Advanced]

The MERIDIAN.CUSTOMER table has 5 million rows. The EMAIL_ADDRESS column currently allows NULLs, and approximately 120,000 rows have NULL email addresses. Management wants to make EMAIL_ADDRESS NOT NULL.

a) Why can you not simply run ALTER TABLE MERIDIAN.CUSTOMER ALTER COLUMN EMAIL_ADDRESS SET NOT NULL? b) Write a complete migration script that handles the NULLs and adds the NOT NULL constraint. Use batch processing to avoid locking the entire table. c) What default value strategy would you recommend for the 120,000 rows with NULL emails? Discuss the tradeoffs of using a placeholder like 'noemail@meridianbank.com' versus a generated unique placeholder. d) How would you communicate this change to application teams?

Exercise 7 — INTEGER to BIGINT Planning [Advanced]

The MERIDIAN.TRANSACTION table has 1.8 billion rows with an INTEGER primary key (TRANSACTION_ID). The identity column is currently at 1,750,000,000. You need to convert to BIGINT.

a) How much time do you estimate before the INTEGER identity sequence exhausts? (Assume 50,000 new transactions per day.) b) Write the migration plan, including: ALTER statement, REORG command (for both z/OS and LUW), index rebuild, and statistics update. c) Estimate the downtime required for a table of this size. What factors affect the duration? d) Can this migration be performed with zero downtime using any of the patterns described in Section 16.5? If so, which one and how? e) What is your rollback plan if the REORG fails at the 80% mark?

Exercise 8 — Expand-Then-Contract in Practice [Advanced]

The MERIDIAN.ACCOUNT table currently stores account type as a CHAR(2) code ('CK', 'SV', 'MM', 'CD', 'LN'). The business wants to normalize this into a reference table and store a SMALLINT foreign key instead.

a) Design the new ACCOUNT_TYPE reference table. b) Write the complete expand-then-contract migration plan: - Phase 1 (Expand): Add new column, create reference table, populate data. - Phase 2 (Migrate): Dual-write application changes (describe, not code). - Phase 3 (Contract): Drop old column. c) At each phase, what is the rollback plan? d) How long would you expect each phase to take in calendar time for a team of 4 developers?

Section B: REORG and Pending States (Exercises 9-14)

Exercise 9 — Pending State Identification [Beginner]

For each scenario, identify the pending state that results on z/OS and describe the resolution.

a) You add a nullable VARCHAR column to a table. b) You run LOAD REPLACE on a table but the job abends before building indexes. c) You alter a SMALLINT column to INTEGER. d) You run REORG with SHRLEVEL CHANGE but the drain phase times out.

Exercise 10 — Monitoring REORG Progress [Intermediate]

Write the SQL and commands to monitor the progress of:

a) An online REORG (SHRLEVEL CHANGE) on z/OS. What information does the DISPLAY UTILITY command provide? b) An inplace REORG on LUW. Write a query that shows phase, status, and estimated completion percentage. c) How would you set up an automated alert if a REORG has been running for longer than the expected duration?

Exercise 11 — REORG Decision Making [Intermediate]

You run REORGCHK on the MERIDIAN.TRANSACTION table on LUW and get the following results:

Table: MERIDIAN.TRANSACTION
F1: 15.2%  * (threshold: 10%)
F2: 32.1%  * (threshold: 20%)
F3: 5.8%     (threshold: 10%)

a) What do F1, F2, and F3 measure? b) Based on these results, would you recommend a REORG? Why or why not? c) Would you choose classic or inplace REORG? What factors influence this decision? d) If this table processes 2,000 transactions per second during business hours, when would you schedule the REORG?

Exercise 12 — Online REORG Setup (z/OS) [Advanced]

You need to perform an online REORG (SHRLEVEL CHANGE) on the MERIDIAN.ACCOUNT tablespace. The tablespace has 3 partitions.

a) Create the mapping table DDL. b) Write the REORG utility control statement with appropriate DRAIN_WAIT, RETRY, and RETRY_DELAY values for a high-transaction environment. c) What happens if an application holds a long-running lock during the drain phase? How do the RETRY parameters help? d) After the REORG completes, what cleanup steps are needed?

Exercise 13 — Batch ALTER with Single REORG [Advanced]

You have five ALTER TABLE operations to perform on MERIDIAN.CUSTOMER:

1. ADD COLUMN LOYALTY_TIER VARCHAR(10)
2. ADD COLUMN RISK_SCORE DECIMAL(5,2)
3. ALTER COLUMN MIDDLE_NAME SET DATA TYPE VARCHAR(100) (was VARCHAR(50))
4. ADD COLUMN LAST_REVIEW_DATE DATE
5. ADD COLUMN PREFERRED_BRANCH_ID INTEGER

a) Can all five operations be combined in a single ALTER TABLE statement? If so, write it. b) On z/OS, what pending state does each operation produce individually? c) If you run them individually, how many REORGs do you need? If you batch them, how many? d) Write the optimal execution plan (ALTER + REORG + RUNSTATS).

Exercise 14 — Handling REORG PENDING Emergency [Challenge]

It is 9:15 AM on a Monday. A junior DBA ran ALTER TABLE MERIDIAN.TRANSACTION ALTER COLUMN AMOUNT SET DATA TYPE DECIMAL(17,2) (was DECIMAL(12,2)) on z/OS production. The tablespace has entered REORG PENDING, and the online banking application is returning errors.

a) What is the immediate priority? What do you tell the application team? b) Can you reverse the ALTER to clear the pending state? If not, why not? c) The table has 800 million rows across 12 partitions. Estimate the REORG time at 5 million rows per minute. d) Can you REORG individual partitions independently? Would this help? e) Write an incident response plan with specific steps and time estimates. f) What process changes would you recommend to prevent this from happening again?

Section C: Data Migration (Exercises 15-21)

Exercise 15 — Export/Import Basics [Beginner]

Write the commands to:

a) Export the MERIDIAN.CUSTOMER table from LUW to IXF format. b) Import the IXF file into a different LUW database. c) Export only active customers (STATUS = 'A') to a delimited (DEL) file. d) Import a DEL file with a custom column delimiter (pipe character) and string delimiter (double quotes).

Exercise 16 — UNLOAD/LOAD on z/OS [Intermediate]

Write the JCL for:

a) An UNLOAD of the MERIDIAN.ACCOUNT table, selecting only accounts opened in the last year. b) A LOAD of the unloaded data into a table called MERIDIAN.ACCOUNT_RECENT on a test system, replacing any existing data. c) What LOG option would you use for the LOAD, and why? d) After the LOAD, what utilities must you run before the table is fully usable?

Exercise 17 — db2move for Schema Migration [Intermediate]

You need to migrate the entire MERIDIAN schema (42 tables) from a DB2 10.5 system to a DB2 11.5 system.

a) Write the db2move commands for export and import. b) In what order should tables be loaded to avoid foreign key violations? c) How would you handle tables with identity columns? What option preserves identity values? d) After db2move completes, what post-migration tasks must you perform?

Exercise 18 — High-Volume Migration Planning [Advanced]

The MERIDIAN.TRANSACTION table has 2 billion rows (approximately 800 GB). You need to migrate it from one z/OS system to another.

a) Estimate the UNLOAD time at a rate of 200 GB/hour. b) Estimate the LOAD time at a rate of 150 GB/hour. c) The table has 12 range partitions by date. Design a parallel migration strategy. d) Calculate the total elapsed time with 4-way parallelism vs. sequential. e) What network bandwidth is required to transfer 800 GB between data centers within a 4-hour window?

Exercise 19 — Data Validation After Migration [Advanced]

After migrating the MERIDIAN schema to a new DB2 system, you need to validate that the migration was successful.

a) Write SQL to compare row counts between source and target for all tables. b) Design a spot-check validation that compares specific rows (by primary key) between systems. c) How would you validate that the sum of all account balances matches between source and target? d) Write a validation query for referential integrity — verify that every foreign key value in ACCOUNT.CUSTOMER_ID exists in CUSTOMER.CUSTOMER_ID on the target system. e) How would you validate that identity column values and sequences are correctly positioned on the target?

Exercise 20 — Continuous Replication Migration [Challenge]

You are migrating Meridian Bank from an on-premises DB2 LUW system to a new DB2 LUW system in a cloud data center. The requirement is less than 5 minutes of downtime.

a) Design a migration plan using HADR. b) At what point do you stop application writes to the old system? c) How do you verify that the standby has fully caught up before takeover? d) What is the rollback plan if the takeover fails? e) Draw a timeline showing each step and its expected duration.

Exercise 21 — Cross-Schema Data Migration with Transformation [Challenge]

An acquired bank (Lakeview Credit Union) uses a different schema design. You must migrate their data into Meridian's schema. Key differences:

• Lakeview uses a single PERSON table; Meridian has CUSTOMER and CUSTOMER_ADDRESS.
• Lakeview stores account type as full text ('Checking', 'Savings'); Meridian uses codes ('CK', 'SV').
• Lakeview uses Oracle-style sequences (SEQ_NAME.NEXTVAL); Meridian uses DB2 identity columns.
• Lakeview has no BRANCH_ID; all accounts must be assigned to Meridian's branch 'MAIN'.

a) Write the SQL transformation queries to map Lakeview data into Meridian's schema. b) How do you handle identity column values for merged data? c) Design a key mapping table to track old Lakeview IDs to new Meridian IDs. d) How do you handle duplicate customers (same SSN in both systems)?

Section D: Cross-Platform and Version Migration (Exercises 22-28)

Exercise 22 — Oracle Data Type Mapping [Beginner]

Map each Oracle data type to its DB2 equivalent. Explain any cases where the mapping is not straightforward.

Exercise 23 — Oracle SQL to DB2 SQL Translation [Intermediate]

Translate each Oracle SQL statement to DB2 SQL:

a)

SELECT employee_id, NVL(manager_id, 0) AS mgr_id
FROM employees
WHERE ROWNUM <= 10
ORDER BY hire_date;

b)

SELECT department_id,
       DECODE(status, 'A', 'Active', 'I', 'Inactive', 'Unknown') AS status_desc
FROM departments;

c)

INSERT INTO audit_log (log_id, action, log_date)
VALUES (audit_seq.NEXTVAL, 'LOGIN', SYSDATE);

d)

SELECT * FROM employees e, departments d
WHERE e.department_id = d.department_id(+);

Exercise 24 — PL/SQL to SQL PL Conversion [Advanced]

Convert the following Oracle PL/SQL procedure to DB2 SQL PL:

CREATE OR REPLACE PROCEDURE close_account(
    p_account_id IN NUMBER,
    p_reason     IN VARCHAR2
) AS
    v_balance    NUMBER;
    v_status     VARCHAR2(10);
    e_has_balance EXCEPTION;
BEGIN
    SELECT balance, status INTO v_balance, v_status
    FROM accounts WHERE account_id = p_account_id
    FOR UPDATE;

    IF v_status = 'CLOSED' THEN
        RAISE_APPLICATION_ERROR(-20001, 'Account already closed');
    END IF;

    IF v_balance > 0 THEN
        RAISE e_has_balance;
    END IF;

    UPDATE accounts
    SET status = 'CLOSED', closed_date = SYSDATE, close_reason = p_reason
    WHERE account_id = p_account_id;

    INSERT INTO audit_log (log_id, table_name, action, record_id, action_date, details)
    VALUES (audit_seq.NEXTVAL, 'ACCOUNTS', 'CLOSE', p_account_id, SYSDATE, p_reason);

    COMMIT;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20002, 'Account not found: ' || p_account_id);
    WHEN e_has_balance THEN
        RAISE_APPLICATION_ERROR(-20003, 'Cannot close account with positive balance');
END;
/

Exercise 25 — Version Migration Planning (z/OS) [Advanced]

You are planning a migration from DB2 11 to DB2 12 for z/OS in a data sharing group with 4 members.

a) Draw the migration timeline showing when each member moves through CM, ENFM stages, and NFM. b) During the rolling migration, what DB2 12 features can you use while some members are still in CM? c) What is the maximum time you should spend in CM before proceeding to ENFM? Why? d) Write the pre-migration checklist (at least 10 items). e) What are the three most common issues encountered during z/OS DB2 migration, based on the chapter material?

Exercise 26 — Version Migration Planning (LUW) [Advanced]

You are upgrading from DB2 10.5 to DB2 11.5 on LUW. The database is 2 TB with HADR configured.

a) Write the complete upgrade procedure, including HADR considerations. b) Do you upgrade the primary first or the standby first? Why? c) What happens to HADR during the upgrade? d) How do you verify that the upgrade was successful? e) Write the rollback procedure if the upgrade fails.

Exercise 27 — Database Conversion Workbench Assessment [Challenge]

An Oracle database has 450 tables, 120 stored procedures, 85 views, and 35 triggers. You are using IBM Database Conversion Workbench to assess migration feasibility.

a) DCW reports 78% automatic conversion rate for DDL and 65% for stored procedures. Calculate the estimated manual work items for each category. b) The remaining 22% of DDL issues are: 8% Oracle-specific features (database links, synonyms), 7% data type incompatibilities, 7% unsupported storage clauses. Describe the resolution strategy for each. c) The remaining 35% of stored procedure issues are: 15% cursor handling differences, 10% exception handling, 10% dynamic SQL syntax. Prioritize these and describe the conversion approach for each. d) Estimate the total person-months for the conversion, assuming 40 manual items per person-month.

Exercise 28 — Zero-Downtime Schema Migration Design [Challenge]

Meridian Bank needs to split the CUSTOMER table into two tables: CUSTOMER (core data) and CUSTOMER_CONTACT (phone, email, address). This is a fundamental schema restructuring.

a) Design the new table structures. b) Write a complete zero-downtime migration plan using the shadow table approach with triggers. c) How do you handle the cutover — switching applications from the old structure to the new structure? d) How do you maintain referential integrity during the migration period when both structures exist? e) What is the total duration you would estimate for this migration project, from planning to completion? f) What are the three biggest risks, and how would you mitigate each?

Section E: Integrated Scenarios (Exercises 29-32)

Exercise 29 — Complete Migration Playbook [Advanced]

Create a complete migration playbook for adding a new "Investment Accounts" product line to Meridian Bank. The changes include:

• 3 new tables (INVESTMENT_ACCOUNT, PORTFOLIO_HOLDING, MARKET_PRICE)
• 2 new columns on existing ACCOUNT table
• 1 column type change on TRANSACTION table
• New indexes on all affected tables
• New foreign key relationships

Write the playbook with: pre-migration checklist, execution scripts (with timing estimates), validation queries, and rollback procedures.

Exercise 30 — Disaster Recovery During Migration [Challenge]

During a planned schema migration on z/OS, the system experiences an unplanned outage (power failure) at the following point:

• ALTER TABLE for new columns: COMPLETE
• REORG for ACCTTS: IN PROGRESS (60% complete)
• REORG for CUSTTS: NOT STARTED
• Data backfill: NOT STARTED

a) What is the state of each tablespace when DB2 restarts? b) What recovery steps must be taken? c) Do you resume the migration plan or restart from the beginning? d) How do indoubt units of recovery (URs) affect the restart?

Exercise 31 — Multi-Environment Migration Pipeline [Challenge]

Design a schema migration pipeline that promotes changes through four environments: DEV -> QA -> STAGING -> PROD. For each environment:

a) What validation must pass before promoting to the next environment? b) How do you handle data differences between environments (DEV has 10,000 rows; PROD has 10 million)? c) How do you ensure the migration scripts are identical across environments? d) What tooling would you recommend for managing and versioning schema changes? e) How do you handle a situation where PROD has a hotfix that DEV does not?

Exercise 32 — Comprehensive Oracle-to-DB2 Migration Plan [Challenge]

A mid-size insurance company is migrating from Oracle 19c to DB2 11.5 on LUW. Their Oracle database has:

• 280 tables totaling 1.5 TB
• 95 PL/SQL packages with 400+ procedures
• 200 views (including 30 materialized views)
• 15 database links to external systems
• Oracle-specific features: Virtual Private Database (VPD), Flashback queries, Oracle Text search

a) Create a 6-month migration timeline with major milestones. b) For each Oracle-specific feature, identify the DB2 equivalent or workaround. c) Estimate the testing effort (types of testing, number of test cases, duration). d) Design the parallel-run strategy — how do you run both systems simultaneously and compare results? e) What is the go/no-go decision framework for the final cutover?

Solutions to selected exercises are available in the instructor's companion guide.