Chapter 8 Further Reading

Essential IBM Documentation

DB2 for z/OS Administration Guide — Locking

  • What it covers: Complete reference for DB2's locking mechanism, lock modes, compatibility matrix, lock escalation, and isolation levels.
  • Why read it: This is the authoritative source. Every concept in this chapter traces back to this documentation. When you encounter a locking behavior in production that doesn't match your expectations, this is where you start.
  • Where to find it: IBM Knowledge Center, DB2 for z/OS > Administration > Performance > Locking

DB2 for z/OS Performance Monitoring and Tuning Guide — Chapter on Locking

  • What it covers: Performance-oriented guidance on lock tuning, LOCKMAX/LOCKSIZE trade-offs, lock avoidance, and currently committed semantics.
  • Why read it: Bridges the gap between "how locking works" (the admin guide) and "how to make it work well" (this guide). Includes worked examples of lock tuning in high-volume environments.
  • Where to find it: IBM Knowledge Center, DB2 for z/OS > Performance > Monitoring and tuning

DB2 for z/OS Codes Manual — SQLCODE -911, -913

  • What it covers: Complete documentation of deadlock and timeout return codes, reason codes, and diagnostic information.
  • Why read it: When you get a -911 in production, the reason code tells you whether it's a deadlock, timeout, or something else. This manual decodes every reason code.
  • Where to find it: IBM Knowledge Center, DB2 for z/OS > Codes

DB2 for z/OS Diagnosis Guide — IFCID 0172

  • What it covers: Format and interpretation of IFCID 0172 (deadlock) trace records, IFCID 0196 (escalation), and IFCID 0044 (lock suspension).
  • Why read it: You can't diagnose production deadlocks without understanding these trace records. The format is dense — study it before you need it at 2am.
  • Where to find it: IBM Knowledge Center, DB2 for z/OS > Diagnosis

Books

DB2 for z/OS: The Complete Reference by Mullins

  • The standard desk reference for DB2 on z/OS. Chapters on locking and concurrency are thorough and practitioner-oriented. Mullins explains lock behavior with the precision of someone who has debugged hundreds of production locking problems.

DB2 Developer's Guide by Mullins

  • More developer-focused than the reference above. Excellent coverage of isolation level selection, cursor design for concurrency, and common locking mistakes in application programs. Includes code examples in COBOL and SQL.

DB2 Performance for the COBOL Programmer by Lawson

  • Out of print but findable. Written specifically for the COBOL/DB2 intersection. The chapter on locking is practical to the point of being prescriptive: "Do this. Don't do that. Here's why." Covers commit strategies, cursor design, and deadlock prevention with COBOL examples throughout.

Transaction Processing: Concepts and Techniques by Gray and Reuter

  • The theoretical foundation for everything in this chapter. Gray and Reuter define the lock hierarchy, compatibility matrices, two-phase locking, and deadlock detection algorithms that DB2 (and every other RDBMS) implements. Dense academic text but essential for understanding why DB2's locking works the way it does.

IBM Redbooks

DB2 for z/OS Locking — The Complete Guide (SG24-xxxx)

  • IBM Redbook specifically on DB2 locking. Includes detailed examples of lock traces, escalation analysis, and performance tuning. The lab exercises walk through real locking scenarios with annotated trace output.

DB2 12 for z/OS Technical Overview (SG24-8383)

  • Covers currently committed semantics and other DB2 12 locking enhancements in detail. Essential for understanding the newest concurrency improvements available on z/OS.

DB2 for z/OS and Parallel Sysplex: Best Practices (SG24-xxxx)

  • Critical reading if your environment uses data sharing. Covers global lock management, Coupling Facility lock structures, inter-system lock contention, and the additional performance considerations for locking in a Parallel Sysplex.

Technical Articles and Papers

"Lock Avoidance in DB2 for z/OS" — IBM Technical Paper

  • Deep dive into the claim/drain mechanism and lock avoidance internals. Explains exactly when DB2 can skip lock acquisition and what conditions force a lock request. Includes performance data showing the impact of lock avoidance on high-throughput systems.

"Currently Committed Semantics in DB2 12" — IBM developerWorks

  • Explains the implementation of currently committed, including how DB2 retrieves the committed version from the log, the performance characteristics, and the configuration options. Includes before/after performance measurements from customer environments.

"Deadlock Analysis in DB2 for z/OS" — IDUG Technical Paper

  • Practical guide to reading and interpreting IFCID 0172 records. Includes a step-by-step methodology for deadlock analysis and prevention. Written by a practicing DBA with production war stories.

Tools

IBM OMEGAMON for DB2

  • Real-time lock monitoring, deadlock detection, lock escalation alerts. The primary tool for diagnosing locking problems in production. If your shop has it, learn to use the lock analysis screens — they can show you lock holders, waiters, and the lock compatibility matrix in real time.

BMC DB2 MainView

  • Alternative to OMEGAMON. Provides similar lock monitoring capabilities with a different interface. The lock conflict display is particularly well-designed for identifying hot pages and contention patterns.

IBM Data Studio

  • Free tool that includes a lock analysis feature. Less capable than OMEGAMON or MainView for real-time analysis but useful for offline analysis of lock trace data.

DB2 DISPLAY DATABASE Command

  • Built-in DB2 command that shows current lock holders on a database or tablespace. No additional software needed. Limited in scope but always available: -DISPLAY DATABASE(DBACCT) SPACENAM(TSACCTM) LOCKS
  • Chapter 1 (Parallel Sysplex): Data sharing lock management — global locks, Coupling Facility lock structures, IFCID 0261.
  • Chapter 6 (Optimizer): How access path selection affects lock behavior — tablespace scan vs. index access lock counts.
  • Chapter 7 (SQL Coding): SQL statement design for minimal lock contention — predicate design, column selection, join order.
  • Chapter 9 (Program Preparation): BIND options that control locking — ISOLATION, CURRENTDATA, CONCURRENTACCESSRESOLUTION.
  • Chapter 12 (Batch Design Patterns): Comprehensive batch program design including commit strategies, restart/recovery, and concurrent processing.

Practice Environments

IBM Z Trial

  • Free 3-day access to a z/OS system with DB2. Sufficient to practice creating tablespaces with different LOCKSIZE/LOCKMAX settings and observing lock behavior from COBOL programs. Request at ibm.com/z/trials.

Hercules/zPDT

  • Emulated mainframe environments that can run DB2 Express. Useful for building a personal lab where you can experiment with lock escalation and deadlock scenarios without impacting production. Setup is non-trivial — expect 2-3 days for initial configuration.

DB2 LUW (Linux/Unix/Windows)

  • DB2 on distributed platforms has a similar (but not identical) locking model. You can practice isolation levels, deadlock detection, and lock monitoring on a laptop. Be aware of differences: DB2 LUW uses row-level locking by default and has a different escalation mechanism. The concepts transfer; the specifics don't always match.