Chapter 9 Further Reading: Data Modification

IBM Official Documentation

DB2 for LUW

  • IBM Db2 Knowledge Center: INSERT statement https://www.ibm.com/docs/en/db2/11.5?topic=statements-insert Complete syntax reference for INSERT, including multi-row VALUES and INSERT...SELECT.

  • IBM Db2 Knowledge Center: UPDATE statement https://www.ibm.com/docs/en/db2/11.5?topic=statements-update Full UPDATE syntax including FROM clause (LUW-specific) and SET with subqueries.

  • IBM Db2 Knowledge Center: DELETE statement https://www.ibm.com/docs/en/db2/11.5?topic=statements-delete DELETE syntax, referential integrity behavior, and differences from TRUNCATE.

  • IBM Db2 Knowledge Center: MERGE statement https://www.ibm.com/docs/en/db2/11.5?topic=statements-merge Complete MERGE syntax with MATCHED, NOT MATCHED, and DELETE clauses.

  • IBM Db2 Knowledge Center: COMMIT and ROLLBACK https://www.ibm.com/docs/en/db2/11.5?topic=statements-commit Transaction control statements, savepoint syntax, and behavior.

  • IBM Db2 Knowledge Center: NOT LOGGED INITIALLY https://www.ibm.com/docs/en/db2/11.5?topic=statements-alter-table Documentation on the NOT LOGGED INITIALLY clause and its recoverability implications.

DB2 for z/OS

  • IBM Db2 for z/OS Knowledge Center: SQL Reference https://www.ibm.com/docs/en/db2-for-zos The z/OS SQL reference for INSERT, UPDATE, DELETE, MERGE, COMMIT, and ROLLBACK. Note the differences from LUW syntax, particularly the absence of the UPDATE FROM clause.

  • IBM Db2 for z/OS: TRUNCATE statement https://www.ibm.com/docs/en/db2-for-zos/13?topic=statements-truncate TRUNCATE was introduced in DB2 12 for z/OS. This reference covers the syntax and restrictions.

Books

  • Mullins, Craig S. DB2 Developer's Guide. 6th edition. Addison-Wesley, 2012. Chapters on DML provide extensive examples of INSERT, UPDATE, DELETE, and transaction control patterns. The section on batch processing is particularly relevant to this chapter's coverage of commit frequency and bulk operations.

  • Zikopoulos, Paul C., et al. DB2 SQL PL: Essential Guide for DB2 UDB on Linux, UNIX, Windows, i5/OS, and z/OS. IBM Press, 2004. Covers stored procedure patterns for batch processing, including cursor-based processing with periodic commits and error handling -- directly applicable to the batch patterns in Sections 9.9 and 9.11.

  • Janssen, Cynthia M., et al. DB2 for z/OS: Application Programming and SQL. IBM Redbooks, SG24-8447. IBM Redbook covering DML best practices for z/OS, including transaction design, commit strategies for CICS and batch programs, and performance considerations for high-volume INSERT and UPDATE workloads.

  • Celko, Joe. SQL for Smarties: Advanced SQL Programming. 5th edition. Morgan Kaufmann, 2014. Chapters on data modification include advanced patterns like the MERGE statement, UPDATE with subqueries, and the theoretical foundations of transaction isolation. Platform-agnostic but the SQL concepts apply directly to DB2.

Technical Articles and Papers

  • Chamberlin, Don. "A Complete Guide to DB2 Universal Database." Morgan Kaufmann, 1998. While dated, the sections on transaction processing fundamentals remain excellent. Chamberlin was one of the original SQL designers, and his explanations of COMMIT/ROLLBACK semantics are precise and authoritative.

  • IBM Developer: "Best practices for batch processing in Db2" https://developer.ibm.com/articles/ IBM's guidance on batch processing patterns, commit frequency, and the LOAD utility. Includes performance comparisons between cursor-based and set-based approaches.

  • "The Halloween Problem" -- original IBM research The Halloween Problem (discovered at IBM in 1976) occurs when a query reads and writes the same table. Understanding how DB2 prevents this (through materialization) is important for INSERT...SELECT patterns. Search for "Halloween Problem database" for accessible explanations.

IBM Redbooks

  • SG24-6419: DB2 for z/OS: Data Sharing -- Planning and Administration Relevant to understanding COMMIT behavior in data sharing environments, including the ORDER/NO ORDER option on sequences and its impact on cross-member coordination.

  • SG24-8189: Batch Processing in DB2 for z/OS Comprehensive guide to batch processing patterns, including commit frequency guidelines, checkpoint/restart mechanisms, and utility-based data loading. Directly applicable to the case studies in this chapter.

  • SG24-8462: DB2 12 for z/OS Technical Overview Covers new features in DB2 12 including TRUNCATE TABLE support and enhanced MERGE capabilities.

Online Resources

  • IDUG (International DB2 Users Group) https://www.idug.org The premier DB2 user community. Conference presentations frequently cover batch processing optimization, transaction design patterns, and real-world MERGE use cases. The online forum is a good place to ask questions about specific DML scenarios.

  • DB2Night Show (podcast) Regular episodes covering DB2 administration and development topics. Episodes on batch processing and transaction management are particularly relevant to this chapter.

  • Stack Overflow -- [db2] tag https://stackoverflow.com/questions/tagged/db2 Practical Q&A for DB2 DML issues. Common topics include MERGE syntax differences between platforms, identity column behavior, and sequence gap questions.

IBM Certified Database Administrator (C1000-147)

This chapter covers material tested in the following certification domains: - DML operations: INSERT, UPDATE, DELETE, MERGE syntax and behavior - Transaction management: COMMIT, ROLLBACK, SAVEPOINT - Identity columns and sequences: GENERATED ALWAYS vs. BY DEFAULT, NEXT VALUE FOR - Bulk data operations: LOAD, IMPORT, EXPORT concepts

IBM Certified Application Developer (C1000-150)

Application developer certification emphasizes: - SELECT FROM INSERT/UPDATE/DELETE: The FINAL TABLE and OLD TABLE patterns - Transaction control in application code: Proper COMMIT placement, error handling, and rollback - Batch processing design: Commit frequency, checkpoint/restart patterns

Hands-On Practice Environments

  • IBM Db2 Community Edition (free) Install Db2 for LUW locally and practice all DML statements from this chapter. The Community Edition supports all features covered here except some enterprise-specific utilities.

  • IBM Db2 on Cloud Lite Plan (free tier) https://cloud.ibm.com/catalog/services/db2 A cloud-based DB2 instance for practicing DML without local installation. Limited resources but sufficient for all exercises in this chapter.

  • Docker: ibmcom/db2 Run DB2 for LUW in a Docker container for rapid setup and teardown. Ideal for experimenting with transaction behavior, lock escalation thresholds, and TRUNCATE without affecting other work.

Return to Chapter 9 | Continue to Chapter 10: Advanced SQL