Chapter 9 Exercises: Data Modification

These exercises reinforce and extend the concepts from Chapter 9. Data modification carries real consequences -- treat every exercise as if you were running it against a production database. Build the habit of testing with SELECT first, using transactions, and verifying row counts.

Difficulty Ratings: - Beginner -- Recall and comprehension. If you read the chapter, you can answer these. - Intermediate -- Application and analysis. Requires thinking beyond what was explicitly stated. - Advanced -- Synthesis and evaluation. Requires research, judgment, or integration of multiple concepts.

Part A: INSERT Fundamentals

Exercise 9.1 -- Single-Row INSERT (Beginner)

Write an INSERT statement that adds a new branch to the meridian.branch table with the following data: - Branch ID: 110 - Branch Name: 'Northgate Square' - City: 'Windsor' - State: 'CT' - Manager ID: 5010 - Open Date: today's date

Include a column list. Explain why the column list is important even when it is optional.

Exercise 9.2 -- Multi-Row INSERT (Beginner)

Write a single INSERT statement that adds three new customers to the meridian.customer table. Invent realistic data for each customer, including first_name, last_name, email, date_of_birth, and branch_id. All three customers should belong to branch 101.

How many network round trips does your single statement require compared to three separate INSERT statements?

Exercise 9.3 -- INSERT...SELECT (Intermediate)

The bank wants to create a meridian.vip_customer table containing all customers whose total account balances exceed $50,000. Write an INSERT...SELECT statement that populates this table. The target table has columns: customer_id, full_name (concatenation of first and last name), total_balance, and classification_date.

What is the "Halloween Problem" and how does DB2 handle it in this context?

Exercise 9.4 -- Default Values and NULLs (Beginner)

Consider a table meridian.audit_entry with columns: - entry_id (GENERATED ALWAYS AS IDENTITY) - action_type VARCHAR(20) NOT NULL - action_timestamp TIMESTAMP DEFAULT CURRENT TIMESTAMP - performed_by VARCHAR(50) DEFAULT SESSION_USER - notes VARCHAR(500) -- nullable

Write an INSERT that provides values only for action_type and notes, letting the other columns use their defaults or generated values. Then write a second INSERT that provides values for all user-specifiable columns.

Exercise 9.5 -- INSERT with Sequences (Intermediate)

A sequence meridian.transaction_seq exists with CACHE 50. Explain what happens to the sequence values if:

1. You insert 10 rows using NEXT VALUE FOR, then ROLLBACK the transaction.
2. DB2 crashes after generating 25 of the 50 cached values but before writing the cache update to the catalog.
3. Two concurrent sessions each call NEXT VALUE FOR at the same time.

Why are gaps in sequence values normal and expected?

Part B: UPDATE Operations

Exercise 9.6 -- Basic UPDATE with Safety Check (Beginner)

Write a SELECT statement to identify all savings accounts with an interest rate below 1.5%. Then write an UPDATE that changes their interest rate to 2.5%. Explain why you wrote the SELECT first.

Exercise 9.7 -- UPDATE with Expression (Beginner)

Write an UPDATE that applies a $25 monthly maintenance fee to all checking accounts where the balance is below $500. The fee should be subtracted from the current_balance column. Include a WHERE clause that prevents the balance from going below zero (only charge the fee if current_balance >= 25).

Exercise 9.8 -- Correlated UPDATE (Intermediate)

Write an UPDATE that sets each branch's total_deposits column equal to the sum of all account balances for that branch's customers. Use a correlated subquery. Include a WHERE EXISTS clause to avoid updating branches that have no customers.

Exercise 9.9 -- UPDATE with JOIN (Intermediate)

A staging table meridian.address_changes contains columns: customer_id, new_address_line1, new_city, new_state, new_zip_code.

Write an UPDATE that applies these address changes to the meridian.customer table. Write two versions: 1. Using a correlated subquery (works on both z/OS and LUW) 2. Using the FROM clause (LUW only)

Which version do you find more readable? Which is more portable?

Exercise 9.10 -- The Dangerous UPDATE (Beginner)

What does the following statement do? Why is it dangerous? What should the DBA do immediately after realizing the mistake?

UPDATE meridian.account
SET status = 'CLOSED';

Assume autocommit is OFF.

Exercise 9.11 -- Mass UPDATE Design (Advanced)

You need to update the interest_rate column for 2 million savings accounts. The new rate depends on the account balance tier. Design a batch update strategy that:

1. Avoids lock escalation
2. Manages log volume
3. Can be restarted if it fails partway through
4. Commits at appropriate intervals

Write pseudocode or SQL for your solution. What commit frequency would you choose and why?

Part C: DELETE Operations

Exercise 9.12 -- WHERE-Qualified DELETE (Beginner)

Write a DELETE statement that removes all transactions from meridian.transaction_history that are older than 7 years and have a status of 'ARCHIVED'. Before writing the DELETE, write the SELECT you would use to verify the target rows.

Exercise 9.13 -- DELETE with Subquery (Intermediate)

Write a DELETE that removes all accounts that have had no transactions in the past 3 years. Use a NOT EXISTS subquery to identify the accounts. Explain why NOT EXISTS is preferred over NOT IN for this pattern.

Exercise 9.14 -- TRUNCATE vs. DELETE Analysis (Intermediate)

A staging table meridian.daily_feed_staging receives 500,000 rows each night from an external system. At the start of each day's processing, the table needs to be emptied. Compare using DELETE vs. TRUNCATE for this operation. Consider:

1. Execution time
2. Log volume generated
3. Trigger behavior
4. Recoverability
5. Impact on identity column values

Which would you recommend and why?

Exercise 9.15 -- Cascading Delete Investigation (Intermediate)

The meridian.customer table has the following foreign key relationships:

• meridian.account references meridian.customer with ON DELETE CASCADE
• meridian.transaction_history references meridian.account with ON DELETE CASCADE
• meridian.customer_address references meridian.customer with ON DELETE CASCADE

If you execute DELETE FROM meridian.customer WHERE customer_id = 10001, and this customer has 3 accounts with a total of 1,200 transactions and 2 addresses:

1. How many total rows are deleted across all tables?
2. In what order does DB2 process the cascading deletes?
3. Why would Meridian Bank prefer ON DELETE RESTRICT instead of CASCADE for financial tables?

Part D: MERGE Operations

Exercise 9.16 -- Basic MERGE (Intermediate)

Write a MERGE statement that processes a daily customer feed (meridian.customer_daily_feed) against the meridian.customer table. For existing customers, update their email and phone. For new customers, insert them with a status of 'ACTIVE' and enrollment_date of today.

Exercise 9.17 -- MERGE with Multiple WHEN Clauses (Intermediate)

Extend Exercise 9.16 so that the MERGE handles three scenarios: 1. If matched and the source action_code is 'UPDATE', update email and phone. 2. If matched and the source action_code is 'CLOSE', set status to 'CLOSED'. 3. If not matched and the source action_code is 'NEW', insert the customer.

Why is it important that the ON clause uses indexed columns on the target table?

Exercise 9.18 -- MERGE for ETL (Advanced)

Design a MERGE statement for the following ETL scenario:

An external system sends a daily file of account balance snapshots. The file may contain: - New accounts not yet in the system (should be inserted) - Existing accounts with updated balances (should be updated only if the balance has changed) - Accounts marked for closure (should be updated to status 'CLOSED')

The source data is in a table meridian.balance_feed with columns: account_id, customer_id, balance, account_type, feed_action ('NEW', 'UPDATE', 'CLOSE'), feed_date.

Write the complete MERGE. Include a condition that skips updates when the balance has not actually changed (to reduce unnecessary log writes).

Part E: Transaction Control

Exercise 9.19 -- COMMIT and ROLLBACK Scenarios (Beginner)

For each scenario below, state the final state of the data:

Scenario A:

INSERT INTO meridian.branch (branch_id, branch_name) VALUES (120, 'Test Branch');
COMMIT;
ROLLBACK;

Scenario B:

INSERT INTO meridian.branch (branch_id, branch_name) VALUES (121, 'Branch A');
INSERT INTO meridian.branch (branch_id, branch_name) VALUES (122, 'Branch B');
ROLLBACK;

Scenario C:

INSERT INTO meridian.branch (branch_id, branch_name) VALUES (123, 'Branch C');
COMMIT;
INSERT INTO meridian.branch (branch_id, branch_name) VALUES (124, 'Branch D');
ROLLBACK;

Exercise 9.20 -- SAVEPOINT Design (Intermediate)

A customer enrollment process has four steps: 1. Create customer record 2. Open checking account 3. Open savings account 4. Enroll in online banking

Design a transaction with savepoints such that: - If step 3 fails, steps 1 and 2 are preserved - If step 4 fails, steps 1, 2, and 3 are preserved - If step 2 fails, the entire transaction rolls back

Write the SQL framework (INSERT statements can be simplified) with savepoints and ROLLBACK TO SAVEPOINT.

Exercise 9.21 -- Autocommit Investigation (Beginner)

Explain the difference in behavior between these two CLP sessions:

Session 1 (autocommit ON):

db2 "UPDATE meridian.customer SET email = 'wrong@email.com' WHERE customer_id = 10001"
db2 "ROLLBACK"

Session 2 (autocommit OFF):

db2 +c "UPDATE meridian.customer SET email = 'wrong@email.com' WHERE customer_id = 10001"
db2 +c "ROLLBACK"

Which session can recover from the mistake? Why?

Exercise 9.22 -- Commit Frequency Analysis (Advanced)

A batch job needs to update 5 million rows in the meridian.transaction_history table. The average row size is 200 bytes. The DB2 LUW instance has an active log configuration of 500 log files, each 10 MB.

Calculate: 1. The approximate log volume generated by the entire batch (assuming each UPDATE writes a before-image and after-image, plus overhead of approximately 100 bytes per log record). 2. Whether the entire batch can fit in a single transaction without filling the active log. 3. A recommended commit frequency based on keeping each transaction's log usage well within safe limits.

Show your calculations.

Part F: SELECT FROM DML and Identity/Sequence Patterns

Exercise 9.23 -- SELECT FROM INSERT (Intermediate)

Write a SELECT FROM INSERT (using FINAL TABLE) that inserts a new customer and returns the generated customer_id along with the enrollment_date. Explain why this is better than performing a separate INSERT followed by a SELECT using IDENTITY_VAL_LOCAL().

Exercise 9.24 -- Audit Trail with OLD TABLE (Advanced)

Design an audit logging pattern that uses SELECT FROM UPDATE with OLD TABLE to capture before-images. The audit log table (meridian.change_audit) has columns: audit_id, table_name, column_name, old_value, new_value, changed_by, changed_at.

Write a statement that updates a customer's email and simultaneously inserts an audit record capturing the old email address.

Exercise 9.25 -- Identity Column Design Decision (Intermediate)

A new table needs an auto-generated primary key. Compare these three approaches:

1. GENERATED ALWAYS AS IDENTITY
2. GENERATED BY DEFAULT AS IDENTITY
3. A SEQUENCE object with NEXT VALUE FOR in the INSERT

For each approach, describe: - A scenario where it is the best choice - A scenario where it would cause problems - How it handles data migration (bulk loading existing data with pre-assigned keys)

Part G: Meridian Bank Scenarios

Exercise 9.26 -- Account Closure Process (Intermediate)

Design the SQL for Meridian Bank's account closure process. When a customer requests account closure:

1. Verify the account balance is zero
2. Update the account status to 'CLOSED' and set close_date
3. Insert a record into the meridian.account_closure_log
4. If the customer has no remaining active accounts, update their status to 'INACTIVE'

Write the complete SQL with transaction control. Use savepoints if appropriate.

Exercise 9.27 -- Monthly Fee Assessment (Intermediate)

Write SQL to assess monthly maintenance fees at Meridian Bank: - Checking accounts with balance < $1,000: charge $12.00 - Checking accounts with balance < $2,500 but >= $1,000: charge $5.00 - Savings accounts with balance < $300: charge $3.00 - All other accounts: no fee

Record each fee as a transaction in meridian.transaction_history and deduct the fee from the account balance. Design for batch processing with appropriate commit frequency.

Exercise 9.28 -- Data Migration with MERGE (Advanced)

Meridian Bank is acquiring a smaller bank. The acquired bank's customer data is in a staging table meridian.acquired_customers. Some of these customers already bank with Meridian (matched by SSN hash).

Design a MERGE that: - For existing Meridian customers: updates their record to add the acquired bank's account information as a note, and flags them for manual review - For new customers: inserts them into the Meridian system with a status of 'PENDING_REVIEW' - Handles data quality issues (NULL email, invalid phone numbers) gracefully

Discuss how you would handle the associated account data for both new and existing customers.

Exercise 9.29 -- End-of-Day Reconciliation (Advanced)

Write the SQL for Meridian Bank's end-of-day reconciliation process:

1. Sum all transactions for each account for the day
2. Compare the calculated balance (opening balance + day's transactions) against the actual current_balance
3. Flag any accounts where the calculated and actual balances differ by more than $0.01
4. Insert discrepancies into a meridian.reconciliation_exceptions table
5. Generate a summary showing total accounts processed, accounts with discrepancies, and total discrepancy amount

This is a read-heavy operation with a small number of writes. What isolation level would you recommend for the SELECT portions?

Exercise 9.30 -- Disaster Recovery Scenario (Advanced)

At 2:15 PM, a DBA accidentally runs the following against the production database with autocommit ON:

UPDATE meridian.account SET current_balance = 0;

All 350,000 account balances are now zero, and the change is committed.

1. What immediate actions should the DBA take?
2. Can DB2's transaction log be used to recover? How?
3. If the last full backup was taken at midnight, and log archiving is enabled, what recovery procedure would you use?
4. How long would recovery likely take?
5. What process changes would you recommend to prevent this from happening again?

Part H: Mixed Practice (Interleaved Review)

Exercise 9.31 -- Concept Linking (Intermediate)

For each pair of concepts, explain the connection between them in 2-3 sentences:

1. WHERE clause and UPDATE safety
2. Correlated subqueries (Chapter 8) and correlated UPDATE
3. Foreign keys (Chapter 2) and cascading DELETE
4. INSERT...SELECT and the Halloween Problem
5. COMMIT and ACID durability

Exercise 9.32 -- Platform Comparison (Intermediate)

Create a comparison table showing the differences between DB2 for z/OS and DB2 for LUW for the following data modification features:

1. UPDATE with FROM/JOIN clause
2. TRUNCATE TABLE availability
3. Autocommit behavior
4. NOT LOGGED INITIALLY
5. DDL implicit commit behavior
6. MERGE with DELETE clause

Exercise 9.33 -- Error Diagnosis (Intermediate)

For each SQLSTATE code, describe the likely cause and the corrective action:

1. SQLSTATE 23505
2. SQLSTATE 23503
3. SQLSTATE 23513
4. SQLSTATE 22007
5. SQLSTATE 21000 (in the context of an UPDATE with subquery)

Exercise 9.34 -- Performance Analysis (Advanced)

You have a stored procedure that processes 100,000 customer records. The current implementation: - Uses a cursor to iterate through each customer - Executes an UPDATE for each customer individually - Commits after every single row

Elapsed time: 45 minutes.

Propose at least three optimizations and estimate the impact of each. Consider: batch commit frequency, set-based UPDATE vs. cursor-based, MERGE vs. separate statements, and any other techniques from this chapter.

Exercise 9.35 -- Self-Assessment Checklist (Beginner)

Rate your confidence (1 = not confident, 5 = very confident) on each of the chapter's learning objectives:

For any objective rated 3 or below, return to the relevant section and re-read it. Then write a short SQL example from memory without consulting the chapter.

Answers and Discussion Notes

Exercise 9.10 -- The Dangerous UPDATE (Sample Answer)

The statement UPDATE meridian.account SET status = 'CLOSED' sets the status column to 'CLOSED' for every row in the table. There is no WHERE clause, so DB2 treats every row as a match.

This is dangerous because it changes data indiscriminately. Active accounts, pending accounts, and already-closed accounts are all affected.

Immediate action: Because autocommit is OFF, the DBA should immediately execute ROLLBACK. This undoes all changes from the current transaction. The data reverts to its state at the last COMMIT point.

If autocommit were ON, the change would already be committed, and recovery would require restoring from backup or using point-in-time recovery with archived logs.

Exercise 9.19 -- COMMIT and ROLLBACK Scenarios (Sample Answer)

Scenario A: Branch 120 ('Test Branch') exists in the table. The COMMIT made the INSERT permanent. The subsequent ROLLBACK has no effect because there are no uncommitted changes to roll back.

Scenario B: Neither branch 121 nor branch 122 exists. The ROLLBACK undid both INSERTs because neither had been committed.

Scenario C: Branch 123 ('Branch C') exists, but branch 124 ('Branch D') does not. The first INSERT was committed (permanent). The second INSERT was rolled back.

Exercise 9.22 -- Commit Frequency Analysis (Sample Calculation)

1. Log volume per row: before-image (200 bytes) + after-image (200 bytes) + overhead (100 bytes) = 500 bytes per log record. For 5 million rows: 5,000,000 * 500 = 2,500,000,000 bytes = approximately 2.5 GB.

2. Active log capacity: 500 files * 10 MB = 5,000 MB = 5 GB. The batch generates approximately 2.5 GB of log, which is 50% of the active log capacity. In theory, it fits in a single transaction, but in practice you should never consume more than 25-30% of active log in a single transaction (other transactions also need log space). A single transaction is risky.

3. Recommended commit frequency: Target 10% of active log per commit = 500 MB. At 500 bytes per log record, that is 500,000,000 / 500 = 1,000,000 rows per commit. However, a more conservative approach (to leave room for other transactions and overhead) would be to commit every 100,000 to 500,000 rows. Recommended: commit every 250,000 rows, resulting in 20 commits for the entire batch.

Return to Chapter 9 | Continue to Quiz