Chapter 9 Key Takeaways: Data Modification

The Five-Minute Summary

If you remember nothing else from this chapter, remember these points:

1. DML Changes the World -- Treat It Accordingly

SELECT is safe. INSERT, UPDATE, DELETE, and MERGE modify data permanently (after COMMIT). Every DML statement deserves the same care you would give to signing a legal document.

2. The Golden Rule: Test with SELECT First

Before running an UPDATE or DELETE, convert the WHERE clause to a SELECT and verify the result set. Check the row count. Check the rows themselves. Only then execute the modification. This habit will save you from at least one career-defining mistake.

3. Never UPDATE or DELETE Without WHERE (Unless You Mean It)

An UPDATE without WHERE changes every row. A DELETE without WHERE removes every row. DB2 will not warn you. If you want to empty a table, use TRUNCATE. If you want to update specific rows, write the WHERE clause before the SET clause.

4. COMMIT and ROLLBACK Are Your Safety Net

  • COMMIT makes changes permanent and releases locks.
  • ROLLBACK undoes all changes since the last COMMIT.
  • After COMMIT, the only "undo" is compensating SQL or restore from backup.
  • Disable autocommit for interactive DML sessions.

5. Transaction Size Matters

Long-running transactions cause lock accumulation, log consumption, and contention. For batch operations, commit every 1,000 to 10,000 rows. The exact number depends on your log capacity and concurrency requirements.

Statement Quick Reference

Statement Purpose Key Safety Concern
INSERT Add new rows Constraint violations, log volume for bulk inserts
UPDATE Modify existing rows Missing WHERE clause, lock escalation for mass updates
DELETE Remove rows Missing WHERE clause, cascading deletes via foreign keys
MERGE Upsert (insert or update) ON clause must use indexed columns, atomicity of match logic
TRUNCATE Remove all rows (fast) Cannot be filtered (no WHERE), does not fire triggers
COMMIT Make changes permanent Point of no return -- verify before executing
ROLLBACK Undo uncommitted changes Only works for uncommitted changes
SAVEPOINT Create intermediate recovery point Released on COMMIT; roll back to savepoint preserves earlier work

Platform Differences to Remember

Feature DB2 for z/OS DB2 for LUW
UPDATE with FROM/JOIN clause Not supported (use correlated subquery or MERGE) Supported
DDL implicit commit No (DDL is part of the UOW, can be rolled back) Yes (DDL triggers implicit commit)
TRUNCATE TABLE DB2 12+ (function level 501) Supported since 9.7
NOT LOGGED INITIALLY Not available (use LOAD with LOG NO) Supported
Autocommit default No autocommit concept; programs use explicit COMMIT CLP defaults to autocommit ON
MERGE with DELETE DB2 11+ DB2 9.7+

Patterns Worth Memorizing

The Safe UPDATE Pattern

-- 1. Test with SELECT
SELECT * FROM target_table WHERE <conditions>;
-- 2. Verify row count and rows
-- 3. Execute UPDATE within a transaction
UPDATE target_table SET <columns> WHERE <conditions>;
-- 4. Verify row count again
-- 5. COMMIT only after verification
COMMIT;

The Batch Processing Pattern

-- Process in batches with checkpoint
WHILE more_rows DO
    -- Modify batch_size rows
    UPDATE/INSERT/DELETE ... WHERE key > last_processed
        FETCH FIRST batch_size ROWS ONLY;
    -- Update checkpoint
    UPDATE batch_control SET last_key = current_key;
    COMMIT;  -- Release locks, persist progress
END WHILE;

The MERGE Upsert Pattern

MERGE INTO target USING source
ON target.key = source.key
WHEN MATCHED THEN UPDATE SET <columns>
WHEN NOT MATCHED THEN INSERT <columns>;

The SELECT FROM INSERT Pattern

SELECT generated_key FROM FINAL TABLE (
    INSERT INTO table (columns) VALUES (values)
);

Common Mistakes to Avoid

  1. Forgetting WHERE on UPDATE/DELETE. This is the most common and most damaging DML mistake.

  2. Running DML with autocommit ON in interactive sessions. One mistake becomes permanent instantly.

  3. Processing millions of rows without committing. Causes log-full conditions and lock escalation.

  4. Using NOT IN with subqueries that might return NULL. Use NOT EXISTS instead (Chapter 8 review).

  5. Assuming sequence values are gapless. They are not. Design accordingly.

  6. Using DELETE instead of TRUNCATE for clearing large tables. DELETE logs every row; TRUNCATE does not.

  7. Ignoring cascading delete rules. A single DELETE on a parent table can remove thousands of dependent rows silently.

  8. Writing MERGE without indexes on the ON clause columns. Results in catastrophic performance for large datasets.

Connections to Other Chapters

  • Chapter 2 (Relational Model): Foreign key constraints (ON DELETE CASCADE/RESTRICT) directly affect DELETE behavior.
  • Chapter 5 (SQL Fundamentals): WHERE clause syntax is identical in SELECT, UPDATE, and DELETE.
  • Chapter 8 (Subqueries/CTEs): Correlated subqueries power UPDATE SET clauses and DELETE WHERE clauses.
  • Chapter 12 (Stored Procedures): Batch processing with COMMIT frequency is typically implemented in stored procedures.
  • Chapter 17 (Utilities): LOAD utility is the high-performance alternative to INSERT for bulk data.
  • Chapter 18 (Backup/Recovery): Transaction logging is the foundation of DB2's recovery capability.
  • Chapter 26 (Locking/Concurrency): Lock escalation, isolation levels, and commit strategy are deeply connected to DML performance.

What to Study Next

Chapter 10 introduces advanced SQL: window functions, OLAP operations, temporal tables, and recursive queries. These analytical tools operate on the data you now know how to create and modify. The combination of data modification (this chapter) and analytical queries (next chapter) covers the full lifecycle of data in a DB2 system.

Return to Chapter 9 | Continue to Further Reading