Includes detailed information on catalog and directory structures as they relate to problem diagnosis. Useful when you need to understand error conditions that reference catalog or directory objects. → Chapter 21: Further Reading and Resources
"db2pd — A Practical Guide"
IBM Developer. Comprehensive walkthrough of db2pd options with real-world examples and output interpretation. - **"Understanding MON_GET Table Functions"** — IBM Developer. Tutorial on transitioning from snapshot monitors to MON_GET functions with side-by-side comparisons. - **"First Failure Data Ca → Chapter 20 Further Reading
"On Writing Well" by William Zinsser
The classic guide to clear, concise nonfiction writing. Applicable to blog posts, technical articles, documentation, and reports. → Chapter 37 Further Reading
IBM has published multiple Redbooks on Oracle-to-DB2 migration. Search for the most current edition. These guides provide detailed data type mappings, SQL translation rules, and stored procedure conversion guidance. → Chapter 16: Further Reading and Resources
"Presenting to Win" by Jerry Weissman
Techniques for presenting technical information to non-technical audiences. Directly applicable to the executive summary skills discussed in Section 36.11. → Chapter 36 Further Reading
IBM developerWorks article covering coding standards, error handling patterns, and performance considerations for SQL PL development. → Chapter 34: Further Reading
"The Art of Indexing" by Craig Mullins
A series of articles on index design strategies for DB2, covering B-tree internals, clustering, partitioned indexes, and index-only access paths. Available through the IDUG (International DB2 Users Group) website. → Chapter 11: Further Reading
"The Art of SQL Tuning in DB2 for z/OS"
IDUG Technical Conference proceedings Annual conference presentations from IBM and customer DBAs sharing real-world tuning case studies. → Chapter 24 Further Reading: SQL Tuning
"The Case Against Hiperpool on Modern z/OS"
IBM Support Technical Exchange IBM's own analysis of why hiperpool should be disabled on modern z/OS systems, with performance data comparing hiperpool configurations vs. enlarged virtual pools on z14 and z15 hardware. → Chapter 25 Further Reading: Buffer Pool and Memory Tuning
"The Future of the DBA"
Various articles and conference presentations on this topic are available through IDUG, IBM Developer, and industry publications. Search for recent presentations (within the last 2 years) for the most current perspectives. → Chapter 37 Further Reading
"The Pyramid Principle" by Barbara Minto
A framework for structuring business communication that starts with the conclusion and supports it with evidence. The executive summary format in this chapter follows this principle. → Chapter 36 Further Reading
"Understanding DB2 Memory Allocation on Linux"
IBM developerWorks (archived) Detailed article on how DB2 LUW interacts with Linux memory management, including the impact of large pages, memory overcommit settings, and transparent huge pages. → Chapter 25 Further Reading: Buffer Pool and Memory Tuning
"Understanding DB2 Optimizer Behavior"
IBM developerWorks article series covering filter factors, join methods, and access path selection with worked examples. - **"DB2 for z/OS: Best Practices for RUNSTATS"** — IBM technical paper on when and how to collect statistics, including real-time statistics and profile-based RUNSTATS. - **"Opti → Chapter 22: Further Reading
"User-Defined Functions: A Performance Guide"
Analysis of UDF overhead, when to use DETERMINISTIC vs. NOT DETERMINISTIC, and the performance impact of UDFs in WHERE clauses. → Chapter 34: Further Reading
Marcus declares a P1 incident and contacts the operations center. The online banking application is configured to show a maintenance message when the backend is unavailable. The ops team enables the maintenance page. → Case Study 2: Point-in-Time Recovery — Undoing a Bad DELETE
managers, loan officers, tellers, and relationship managers - **8 account types** covering checking, savings, money market, certificates, and loans - **20 customers** -- a mix of individuals and businesses with varied profiles - **35 accounts** -- each customer has 1-3 accounts - **~95 transactions* → Chapter 4: Setting Up Your DB2 Environment -- Installation, Configuration, and Your First Database
150 million tax filings per year
a number that includes individual income tax returns, corporate tax filings, value-added tax (VAT) submissions, and quarterly estimated payments. Processing occurs year-round, but 65% of individual filings arrive during a six-week peak season from March 15 to April 30. → Case Study 2: Government Tax Processing — The Federal Tax Authority of Valdoria
their leading columns were a subset of another index on the same table. For example, the POLICY table had indexes on both `(AGENT_ID)` and `(AGENT_ID, EFFECTIVE_DATE)`. The single-column index served no purpose that the composite index could not. - **8 indexes were on low-cardinality columns** (STAT → Case Study 1: Index Strategy Overhaul — From 47 Indexes to 12
24/7 availability
no planned or unplanned outages - **High throughput** — 15,000 CICS transactions per second at peak - **Batch window** — nightly batch processes including end-of-day settlement, interest calculation, and regulatory reporting - **Disaster recovery** — site-level redundancy (covered in Chapter 30) - * → Chapter 28: Data Sharing and Parallel Sysplex — Multi-Member DB2 on z/OS
3. Account governance:
RPTUSER account was replaced with individual named accounts for each analyst - Each account was mapped to the WL_REPORTING workload - Connection from non-approved IP addresses was blocked via CONNECT restrictions → Case Study 2: Midnight Crisis — Diagnosing a Sudden Slowdown
37a. Role Structure:
**TELLER:** SELECT, INSERT, UPDATE on accounts and transactions (restricted to own branch via RCAC). No access to loans or audit_log. - **BRANCH_MANAGER:** SELECT, INSERT, UPDATE on accounts, transactions, and loans (restricted to own branch via RCAC). SELECT on summary views for all branches. No DE → Comprehensive Final Exam --- All Parts (Chapters 1--40)
4. Query governance:
The analytics team was provided a read replica for heavy queries instead of the production database - Queries exceeding 10 million rows read were automatically stopped on production - The data analyst team received training on query impact awareness → Case Study 2: Midnight Crisis — Diagnosing a Sudden Slowdown
5. Enhanced monitoring:
A new alert was added for any single application reading more than 5 million rows in a 5-minute window - Buffer pool hit ratio monitoring was enhanced with a rate-of-change alert: if the hit ratio dropped more than 5 percentage points in 5 minutes, an immediate alert was triggered regardless of abso → Case Study 2: Midnight Crisis — Diagnosing a Sudden Slowdown
[z/OS]
This section covers DB2 for z/OS (the mainframe platform). It will reference JCL, TSO/ISPF, DSNZPARM, RACF, and other z/OS-specific technologies. - **[LUW]** -- This section covers DB2 for LUW (Linux, UNIX, Windows). It will reference the command line processor, db2pd, database manager configuration → How to Use This Book
`WLM ENVIRONMENT`
specifies the WLM application environment where the procedure runs. This determines the address space characteristics (region size, language environment options). - **`COLLID`** — the package collection ID for static SQL within the COBOL program. - **`PROGRAM TYPE SUB`** — indicates a subroutine (as → Chapter 34: Stored Procedures, UDFs, and Application Logic in the Database
Most log volume. Each of the 1 million INSERTs generates a log record AND a commit record. The commit records alone are significant overhead. Total: approximately 1 million insert log records + 1 million commit log records. → Chapter 9 Quiz: Data Modification
Missing or incorrect ON clause produces m x n rows 2. **Wrong outer join direction** — Preserving the wrong table (check which side is LEFT vs. RIGHT) 3. **WHERE clause on outer table** — Nullifies the outer join, silently converting it to inner 4. **Row multiplication in aggregates** — Joining one- → Chapter 6: Key Takeaways
route transactions that access the same data to the same member, keeping the data in that member's local buffer pool and the pageset GBP-independent 2. **Large local buffer pools** — a page read from the local buffer pool (if valid) avoids a GBP read 3. **Minimize cross-member interest in hot pagese → Chapter 28: Data Sharing and Parallel Sysplex — Multi-Member DB2 on z/OS
CPU increased 4.7x (450 to 2,100 sec) - Getpages increased 4.8x (120M to 580M) - Sync I/O increased 5.2x (25M to 130M) - Rows processed increased 4.25x (2M to 8.5M) - Prefetch barely changed (500K to 600K) - **COMMIT count: still 1** — the job commits only at the end → Case Study 02: Batch Window Crisis — 8 Hours to 12 Hours
**SYNC:** The primary waits for the standby to write the log to disk before returning COMMIT to the application. Zero data loss guaranteed. Highest latency impact. - **NEARSYNC:** The primary waits for the standby to receive the log in memory (but not write to disk). Near-zero data loss (only data i → Answers to Selected Exercises
If a customer changes their name (marriage, legal change), every historical transaction must be updated — or the system will show inconsistent names across different views. - Storage is wasted: with 500 million transactions per year, even 50 bytes per name adds ~25 GB of redundant data annually. → Chapter 13 Quiz: Logical Database Design
Arguments for natural keys:
They carry meaning. When you see SSN `123-45-6789` in a foreign key column, you know what it refers to without a JOIN. - They can prevent the need for some JOINs (if the natural key is already in the referencing table, you may not need to look up the referenced table). - They enforce real-world uniq → Chapter 2: The Relational Model — How DB2 Thinks About Data
Arguments for storing it:
Statement generation queries avoid the JOIN chain TRANSACTION -> ACCOUNT -> ACCOUNT_HOLDER -> CUSTOMER, improving performance for a high-frequency operation. → Chapter 13 Quiz: Logical Database Design
Arguments for surrogate keys:
They never change. An SSN can be corrected (data entry error), an email address can change, a product SKU can be reassigned. A surrogate key is immutable. - They are compact. A 4-byte INTEGER is smaller and faster to index and join than an 11-byte CHAR SSN or a 254-byte VARCHAR email. - They are mea → Chapter 2: The Relational Model — How DB2 Thinks About Data
Used when the business or regulators require tracking of who changed what data and when. Appropriate for any entity with financial, medical, or personally identifiable information. Implemented via a centralized AUDIT_LOG, shadow tables, or both. → Chapter 13 Quiz: Logical Database Design
Is the database up? Can applications connect? Are subsystem components active? 2. **Performance** — Are response times within acceptable bounds? Are buffer pools effective? Are sorts spilling to disk? 3. **Capacity** — How much disk space remains? How full are the logs? Are table spaces approaching → Chapter 20: Monitoring and Problem Determination — Knowing What's Wrong Before the Phone Rings
B
B)
`db2fmp` is the Fenced Mode Process. It runs user-written code (stored procedures, UDFs) in a separate process so that a crash in user code does not bring down the DB2 engine. → Chapter 3 Quiz: DB2 Architecture
B) DBM1
The Database Services address space (DBM1) handles SQL compilation, optimization, buffer pool management, and all data access operations. MSTR handles logging and system services. DIST handles remote connections. IRLM handles locking. → Chapter 3 Quiz: DB2 Architecture
B) Multi-row INSERT with batched commits
Same row-level logging as C, but with 100 commit records (1,000 rows * 10 statements = 10,000 rows per commit, 100 commits for 1 million rows). Logging is essentially the same as C but with slightly more commit overhead. In practice, very similar to C. → Chapter 9 Quiz: Data Modification
B, C, and D
The EDM pool caches compiled plans/packages, database descriptors, and the dynamic statement cache. Data pages (A) and index pages (E) are cached in buffer pools, not the EDM pool. → Chapter 3 Quiz: DB2 Architecture
the surviving member undoes all uncommitted transactions by reading the failed member's log 4. **Retained locks are released** — as each transaction is rolled back, its retained locks are freed 5. **Normal access resumes** — other members can now access the previously locked resources → Chapter 28: Data Sharing and Parallel Sysplex — Multi-Member DB2 on z/OS
an estimate of how much performance would improve if the consumer received more memory. For buffer pools, this is based on the predicted reduction in physical I/O. For sort heap, it is based on the predicted reduction in sort overflows. 3. It transfers memory from low-benefit consumers to high-benef → Chapter 25: Buffer Pool and Memory Tuning — Giving DB2 the Right Resources
Best Practices: DB2 for z/OS REORG
IBM Support technote covering SHRLEVEL selection, FASTSWITCH configuration, mapping table sizing, and common REORG problems. - **Best Practices: DB2 for z/OS RUNSTATS** — Guidance on when to collect KEYCARD, FREQVAL, HISTOGRAM, and column group statistics, with decision trees for choosing the right → Chapter 17: Further Reading
IBM white paper explaining columnar storage, actionable compression, and SIMD processing in Db2 BLU. https://www.ibm.com/docs/en/db2/11.5?topic=features-blu-acceleration → Chapter 31 Further Reading
HADR is much simpler and cheaper - **The workload is modest** — a single server with HADR standby may suffice - **Geographic DR is the primary requirement** — pureScale requires low-latency interconnects (same data center) → Chapter 29: HADR and Replication — High Availability for DB2 LUW
Buffer pool right-sizing
Priya reviewed all buffer pool allocations and increased the DAILYBAL_TS pool from 50,000 to 100,000 pages permanently (not the emergency 200,000, but a sustainable increase based on the table's access patterns). → Case Study 2: Emergency REORG — When Fragmentation Hits 90%
Buffer pool was resized smaller
someone reduced the BP_CUST pool size (accidentally or intentionally). 2. **New workload or query pattern** — a new report or batch job is performing scans on the CUSTOMER table, cycling through pages faster than the LRU can protect the working set. 3. **Operating system memory pressure** — another → Chapter 3 Exercises: DB2 Architecture
Buffer pools for critical OLTP tables and indexes
biggest impact on response time 2. **Package cache / EDM pool** — reduces CPU overhead for dynamic SQL 3. **Sort memory** — eliminates sort overflows 4. **Separate batch from OLTP** — prevents cross-workload interference 5. **Lock list** — prevents lock escalation (which causes deadlocks and timeout → Chapter 25: Buffer Pool and Memory Tuning — Giving DB2 the Right Resources
The primary reason is workload isolation. A batch scan on one table can push out frequently accessed pages for another workload (buffer pool pollution). Separate pools prevent this. While different page sizes also require separate pools (D), that is a technical requirement, not the primary design mo → Chapter 3 Quiz: DB2 Architecture
BPHR = (1 - 150,000 / 10,000,000) * 100 = (1 - 0.015) * 100 = 98.5%. This means 98.5% of page requests were satisfied from the buffer pool without disk I/O. → Chapter 3 Quiz: DB2 Architecture
C) Access path selection
The optimizer evaluates different access strategies, estimates their costs using catalog statistics, and chooses the least expensive plan. This happens after parsing (A) and validation (B), and before execution (D). → Chapter 3 Quiz: DB2 Architecture
C) DB2PDIST
The DDF (Distributed Data Facility) address space handles all remote connections, including JDBC over TCP/IP. The naming convention is the four-character subsystem ID followed by the address space type. → Chapter 3 Quiz: DB2 Architecture
C) INSERT...SELECT
Full logging of all 1 million rows, but only one commit record. Still logs every row's after-image. Total: approximately 1 million insert log records + 1 commit record. → Chapter 9 Quiz: Data Modification
C) MSTR
The System Services address space is the first started and the last stopped. It must be running before the other DB2 address spaces can initialize. → Chapter 3 Quiz: DB2 Architecture
C) SYSCAT.TABLES
The `CARD` column in SYSCAT.TABLES contains the cardinality (row count) as recorded by the last RUNSTATS execution. On z/OS, the equivalent is the `CARDF` column in SYSIBM.SYSTABLES. → Chapter 3 Quiz: DB2 Architecture
C) Universal Table Space (UTS)
UTS is the modern standard, available in partition-by-range (PBR) and partition-by-growth (PBG) variants. Simple tablespaces are deprecated. Segmented tablespaces are legacy. Classic partitioned tablespaces are superseded by PBR UTS. → Chapter 3 Quiz: DB2 Architecture
unlike adding a nullable column (instant) or widening a VARCHAR (instant), changing the physical width of a column requires rewriting every row. 4. **Has the highest risk** — if the REORG fails partway through, recovery may require restoring from backup. → Chapter 16 Quiz: Schema Evolution and Data Migration
every customer matched with every account — which is almost never what you want. (A Cartesian product of 2 million customers and 5 million accounts is 10 trillion rows. Your query will not finish.) → Chapter 2: The Relational Model — How DB2 Thinks About Data
all lock requests go through the CF - **Page coherency** — the CF ensures that all members see consistent data - **Fast inter-member communication** — using high-speed interconnects (InfiniBand or RDMA over Converged Ethernet) → Chapter 29: HADR and Replication — High Availability for DB2 LUW
now part of **IBM InfoSphere Data Replication** — is a log-based replication technology that captures row-level changes from DB2 and delivers them to diverse targets in real-time. While Q Replication is specifically designed for DB2-to-DB2 replication via MQ, CDC is broader: → Chapter 29: HADR and Replication — High Availability for DB2 LUW
Understanding transaction isolation, locking, and deadlock prevention is essential for writing correct stored procedures. The deadlock prevention pattern used in PROC_TRANSFER_FUNDS originates here. → Chapter 34: Further Reading
Chapter 12: Views, Triggers, and Stored Procedures
Tablespace design directly affects utility behavior and performance. Revisit extent sizing, free space percentages, and buffer pool allocation in the context of REORG efficiency. - **Chapter 15 (Indexing Strategies)** — Index design and clustering choices determine how quickly data fragments and how → Chapter 17: Further Reading
Chapter 14: Indexing Strategies
Clustering indexes and their interaction with partitioned tables. - **Chapter 17: Query Optimization** — EXPLAIN output interpretation, including partition elimination indicators. - **Chapter 22: Concurrency and Locking** — Lock escalation in partitioned table spaces. - **Chapter 28: Backup and Reco → Chapter 30 Further Reading
Chapter 15 (Query Optimization)
Understanding access paths is essential for interpreting monitoring data. A buffer pool hit ratio decline often traces back to an optimizer decision. - **Chapter 17 (Utilities)** — Monitoring tells you when utilities are needed. RUNSTATS, REORG, and COPY operations are driven by monitoring data. - * → Chapter 20 Further Reading
Chapter 15: Buffer Pool Tuning
Detailed guidance on sizing and configuring buffer pools, directly applicable to I/O-bound diagnosis. - **Chapter 17: Indexing Strategies** — Index design techniques that address CPU-bound problems caused by table scans and inefficient access paths. - **Chapter 22: EXPLAIN and Access Paths** — How t → Chapter 27: Further Reading — Performance Diagnosis Methodology
Chapter 17: RUNSTATS and Catalog Statistics
The quality of EXPLAIN output depends directly on the quality of your statistics. Stale statistics produce misleading access plans. → Chapter 23: Further Reading
Chapter 18: Concurrency and Locking
Foundation for understanding L-locks in data sharing - **Chapter 26: DB2 on z/OS — The Mainframe Environment** — Prerequisites for understanding the z/OS platform - **Chapter 27: z/OS Performance and Optimization** — Performance tuning that complements data sharing tuning - **Chapter 30: Backup, Rec → Chapter 28 Further Reading
Chapter 19: Security and Authorization
DB2's authorization model underpins API security. RACF integration with z/OS Connect, EXECUTE privileges for stored procedures, and role-based access control all originate from Chapter 19's security framework. → Chapter 35: Further Reading
Chapter 22: Index Design and Management
Index design is the primary tool for influencing access plans. Understanding EXPLAIN output tells you which indexes are needed and how they are being used. → Chapter 23: Further Reading
Chapter 22: Query Optimization
UDFs interact with the DB2 optimizer in important ways. Understanding access paths helps you write UDFs that do not defeat index access. → Chapter 34: Further Reading
Chapter 23: EXPLAIN Tools
Learn to read the optimizer's decisions using EXPLAIN output, Visual Explain, and the PLAN_TABLE. - **Chapter 24: SQL Tuning** — Apply your understanding of the optimizer to systematically improve query performance. - **Chapter 25: Buffer Pool Tuning** — Understand how buffer pool configuration affe → Chapter 22: Further Reading
Chapter 24: Advanced Query Optimization
Builds on EXPLAIN skills with query rewriting techniques, optimization profiles, and workload-level performance management. → Chapter 23: Further Reading
Chapter 25: Monitoring and Diagnosing Performance
Covers runtime performance monitoring that complements EXPLAIN's compile-time analysis. → Chapter 23: Further Reading
Chapter 2: The Relational Model
Formal definitions of selection, projection, and the relational algebra operations that underlie SELECT queries. - **Chapter 4: Setting Up Your DB2 Environment** — Instructions for creating and loading the Meridian National Bank database used in all Chapter 5 examples. - **Chapter 6: Joining Tables* → Chapter 5 Further Reading
Chapter 3: DB2 Architecture
Buffer pools, memory management, and storage manager internals that underpin physical design decisions. - **Chapter 11: Data Types and Domains** — Data type selection affects row size, which drives page size and storage calculations. - **Chapter 13: Logical Database Design** — The logical model that → Chapter 14: Further Reading
Chapter 4: Data Types and Table Design
Schema design considerations for cross-platform portability (z/OS to cloud). - **Chapter 18: Security and Authorization** — GRANT/REVOKE, roles, and RCAC policies that apply in both on-premises and cloud environments. - **Chapter 29: HADR and High Availability** — The foundation for understanding ma → Chapter 31 Further Reading
Chapter 5: SELECT and WHERE
Foundation for all subquery placement. - **Chapter 6: Joins** — Semi-joins and anti-joins are the execution model for EXISTS and NOT EXISTS. - **Chapter 7: Aggregation and GROUP BY** — CTEs frequently contain aggregations; understanding GROUP BY is prerequisite. - **Chapter 9: INSERT, UPDATE, DELETE → Chapter 8: Further Reading
Foundation for understanding COMMIT, ROLLBACK, and transaction boundaries. - **Chapter 15: Buffer Pool Tuning** — Lock memory (LOCKLIST) competes with buffer pool memory for system resources. - **Chapter 17: Indexing Strategies** — Good indexes reduce the number of rows examined, directly reducing l → Chapter 26: Further Reading — Locking, Concurrency, and Isolation Levels
Chapter title and learning objectives
what you will be able to do after completing this chapter - **Prerequisites** -- which earlier chapters you should have read - **Meridian Bank context** -- how this chapter's topic connects to the progressive project → How to Use This Book
A formal request for payment filed against a specific policy. | Attribute | Type | Notes | |-----------|------|-------| | CLAIM_ID | Integer (PK) | | | CLAIM_NUMBER | Varchar(20) (unique) | Customer-facing | | POLICY_ID | FK to POLICY | References the policy system | | LOSS_EVENT_ID | FK to LOSS_EVE → Case Study 2: Insurance Claims Schema Design
CLAIM_COVERAGE
The intersection between a claim and a policy coverage. This was the most consequential design decision. | Attribute | Type | Notes | |-----------|------|-------| | CLAIM_COVERAGE_ID | Integer (PK) | | | CLAIM_ID | FK to CLAIM | | | COVERAGE_CODE | FK to COVERAGE_TYPE | | | RESERVE_AMOUNT | Decimal( → Case Study 2: Insurance Claims Schema Design
CLAIM_DOCUMENT
Metadata for photos, reports, estimates. | Attribute | Type | Notes | |-----------|------|-------| | DOCUMENT_ID | Integer (PK) | | | CLAIM_ID | FK to CLAIM | | | DOCUMENT_TYPE | Code | PHOTO, POLICE_REPORT, MEDICAL_RECORD, ESTIMATE, INVOICE | | FILE_NAME | Varchar(200) | | | STORAGE_REFERENCE_ID | → Case Study 2: Insurance Claims Schema Design
CLAIM_PARTY
Junction table: parties play roles on claims. | Attribute | Type | Notes | |-----------|------|-------| | CLAIM_PARTY_ID | Integer (PK) | | | CLAIM_ID | FK to CLAIM | | | PARTY_ID | FK to PARTY | | | ROLE_CODE | Code | INSURED, CLAIMANT, WITNESS, ATTORNEY, VENDOR, MEDICAL_PROVIDER | | IS_PRIMARY | B → Case Study 2: Insurance Claims Schema Design
CLAIM_PAYMENT
Every payment issued or recovery received. | Attribute | Type | Notes | |-----------|------|-------| | PAYMENT_ID | Integer (PK) | | | CLAIM_COVERAGE_ID | FK to CLAIM_COVERAGE | Payments are coverage-level | | PAYEE_PARTY_ID | FK to PARTY | | | PAYMENT_TYPE | Code | INDEMNITY, EXPENSE, SUBROGATION_R → Case Study 2: Insurance Claims Schema Design
CLAIM_RESERVE_HISTORY
Temporal record of every reserve change. | Attribute | Type | Notes | |-----------|------|-------| | RESERVE_HISTORY_ID | Integer (PK) | | | CLAIM_COVERAGE_ID | FK to CLAIM_COVERAGE | | | PREVIOUS_AMOUNT | Decimal(15,2) | | | NEW_AMOUNT | Decimal(15,2) | | | CHANGE_DATE | Timestamp | | | CHANGED_BY → Case Study 2: Insurance Claims Schema Design
CLAIM_STATUS_HISTORY
Every status transition with accountability. | Attribute | Type | Notes | |-----------|------|-------| | STATUS_HISTORY_ID | Integer (PK) | | | CLAIM_ID | FK to CLAIM | | | PREVIOUS_STATUS | Code | | | NEW_STATUS | Code | | | TRANSITION_DATE | Timestamp | | | CHANGED_BY | FK to EMPLOYEE | | | REASON → Case Study 2: Insurance Claims Schema Design
Class castout threshold
when the percentage of dirty pages in the GBP for a given class reaches a threshold (default 10%) - **Group castout threshold** — when the overall GBP dirty page percentage reaches a threshold (default 35%) - **Castout engine trigger** — DB2 proactively schedules castout to avoid threshold breaches → Chapter 28: Data Sharing and Parallel Sysplex — Multi-Member DB2 on z/OS
Claim header: claim number, policy number, date of loss, status, adjuster code 2. **CLMPTY** — Parties: up to 8 parties per claim stored as repeating groups (PARTY1_NAME, PARTY1_ROLE, PARTY2_NAME, PARTY2_ROLE, ...) 3. **CLMPAY** — Payments: up to 30 payment slots per claim 4. **CLMNOT** — Notes: fre → Case Study 2: Insurance Claims Schema Design
Column interpretation:
**ST (Status)**: TR = active thread, QW = queued waiting, QD = queued disabled - **A (Active)**: * = currently executing in DB2, N = not currently executing - **REQ-CT**: Number of DB2 requests made — a very high count on an active thread suggests a long-running operation - **PLAN**: Bound plan name → Chapter 20: Monitoring and Problem Determination — Knowing What's Wrong Before the Phone Rings
"Is DB2 the same as Db2?" Yes. IBM rebranded to "Db2" in 2017, but documentation, error messages, and real-world usage mix both capitalizations freely. - "Why learn DB2 when everyone uses PostgreSQL/MySQL?" Emphasize DB2's dominance in enterprise environments, especially banking, insurance, and gove → Teaching Notes for All Chapters
From A: code blocks (SQL, JCL, COBOL, Java, Python), mathematical formulations (cost estimation, cardinality, I/O calculations), algorithm analysis (B+ tree search, sort merge join), performance benchmarks, debugging walkthroughs, unit tests - From C: action checklists (DBA daily/weekly/monthly runb → IBM DB2: From First Query to Enterprise Architecture
CONTINUE
after the handler executes, control returns to the statement after the one that caused the condition. - **EXIT** — after the handler executes, control exits the compound statement where the handler was declared. - **UNDO** — like EXIT, but also rolls back all changes made in the compound statement ( → Chapter 34: Stored Procedures, UDFs, and Application Logic in the Database
continuous availability
there is no failover because multiple members are active simultaneously, sharing the same data. It is the LUW equivalent of z/OS data sharing (Chapter 28), though the implementation differs significantly. → Chapter 29: HADR and Replication — High Availability for DB2 LUW
Continuous availability is required
the additional overhead is the cost of zero downtime - **Workloads can be partitioned** — different members handle different workload types, minimizing cross-member data sharing - **Horizontal scalability is needed** — adding a member adds capacity - **Planned maintenance must be non-disruptive** — → Chapter 28: Data Sharing and Parallel Sysplex — Multi-Member DB2 on z/OS
controlled denormalization
they introduce data redundancy (the aggregated data exists both in the base tables and the MQT), but DB2 can maintain them automatically and the optimizer can route queries to them transparently. This provides the performance benefit of denormalization with less risk of data inconsistency than manua → Chapter 2 Quiz: The Relational Model
dedicated processors and memory, typically 10-20% of the total system capacity - **CF links** — high-speed connections between every CPC and every CF - **Operational complexity** — managing multiple DB2 members, monitoring CF health, tuning GBPs and lock structures - **Specialized skills** — data sh → Chapter 28: Data Sharing and Parallel Sysplex — Multi-Member DB2 on z/OS
CPU cost calculation:
The server has 32 cores at approximately $150,000/year total compute cost. - 5% additional CPU = $7,500/year. - With headroom: the system peaks at 65% CPU, well below the 80% threshold where additional CPU might be needed. → Case Study 2: Compression ROI Analysis — Saving $200K in Storage
separate pools for data, indexes, temporary work, and transaction data 4. **Create tablespaces** -- separate tablespaces for each buffer pool, plus LOB and temporary tablespaces 5. **Create the MERIDIAN schema** 6. **Create tables** -- BRANCHES, CUSTOMERS, ACCOUNT_TYPES, ACCOUNTS, TRANSACTIONS, EMPL → Chapter 4: Setting Up Your DB2 Environment -- Installation, Configuration, and Your First Database
a person or business that holds accounts - **ACCOUNT** — a financial product held by a customer (checking, savings, loan, CD) - **BRANCH** — a physical location where the bank operates - **EMPLOYEE** — a person who works for the bank - **TRANSACTION** — a financial event that affects an account bala → Chapter 13: Logical Database Design — From Business Requirements to Entity-Relationship Models
The Internal Resource Lock Manager handles all lock management including deadlock detection. A spike in deadlocks increases IRLM workload. However, the performance impact would also be felt in DBM1 (where the SQL waits for locks) and potentially in the applications. → Chapter 3 Quiz: DB2 Architecture
D) LOAD utility
Minimal logging. LOAD writes data pages directly and logs only control information (which pages were modified, not the individual row data). Log volume is typically 1-5% of the data volume. → Chapter 9 Quiz: Data Modification
duplicate, inconsistent, or contradictory data because relationships were not properly modeled. 2. **Performance degradation** — queries require complex workarounds (self-joins, string parsing, UNION ALL across multiple tables) because the schema does not match the query patterns. 3. **Maintenance b → Chapter 13 Quiz: Logical Database Design
Data loss risk (RPO)
can committed transactions be lost if the primary fails? - **Transaction latency** — does the primary wait for the standby during commit? - **Availability behavior** — what happens if the standby becomes unreachable? → Chapter 29: HADR and Replication — High Availability for DB2 LUW
tracks the status of databases, tablespaces, and index spaces across members (e.g., which objects are in COPY PENDING, CHECK PENDING, etc.) - **Function registration** — members register their capabilities - **CASTOUT ownership** — which member is responsible for casting out (writing) dirty pages fr → Chapter 28: Data Sharing and Parallel Sysplex — Multi-Member DB2 on z/OS
must fire regardless of which application modifies data | | Data validation that must be absolute | **Database constraint or trigger** -- application-level validation can be bypassed | | Multi-step transaction with high latency sensitivity | **Stored procedure** -- minimize round trips | | Complex b → Chapter 12: Views, Triggers, and Stored Procedures — Encapsulating Logic in the Database
Covers the DBA lifecycle including utility management, backup strategy, and recovery procedures with hands-on examples. - **Optimizing DB2 Queries with IBM Data Studio** — While focused on query tuning, includes detailed coverage of how RUNSTATS options (KEYCARD, FREQVAL, HISTOGRAM) affect optimizer → Chapter 17: Further Reading
Search for "ALTER TABLE" in the Db2 11.5 for LUW SQL Reference. Covers all supported ALTER operations on LUW, including those specific to LUW (such as RENAME COLUMN). → Chapter 16: Further Reading and Resources
Search for "REORG TABLE" in the Db2 11.5 for LUW Command Reference. Documents classic and inplace REORG, ALLOW options, and monitoring. → Chapter 16: Further Reading and Resources
Db2 11.5 for LUW: Upgrading to Db2 11.5
Search for "Upgrading" in the Db2 11.5 Installation and Upgrade documentation. Covers db2iupgrade, UPGRADE DATABASE, and post-upgrade tasks. → Chapter 16: Further Reading and Resources
Search for "ALTER TABLE" in the Db2 12 for z/OS SQL Reference. This is the definitive reference for what ALTER operations are supported, which trigger pending states, and the exact syntax. Pay particular attention to the "Notes" section of each ALTER variant, which documents side effects. → Chapter 16: Further Reading and Resources
Db2 12 for z/OS: Managing Pending States
Search for "pending definition changes" or "REORG-pending" in the Db2 12 for z/OS Administration Guide. Documents all pending states, how they are triggered, how to detect them, and how to resolve them. → Chapter 16: Further Reading and Resources
Db2 12 for z/OS: Migration from Db2 11 to Db2 12
Search for "Migrating to Db2 12" in the Db2 12 for z/OS documentation. Covers Compatibility Mode, Enabling New Function Mode stages, New Function Mode, fallback procedures, and the complete migration checklist. → Chapter 16: Further Reading and Resources
Db2 12 for z/OS: Utility Guide — REORG TABLESPACE
Search for "REORG TABLESPACE" in the Db2 12 for z/OS Utility Guide and Reference. Covers SHRLEVEL options, mapping tables, drain behavior, and online REORG mechanics in detail. → Chapter 16: Further Reading and Resources
DB2 13 for z/OS Administration Guide
Chapters on tablespace design, index design, and storage management provide operational context that the SQL Reference lacks. Pay particular attention to the sections on Universal Table Spaces. → Chapter 11: Further Reading
Understanding REORG, CHECK DATA, REBUILD INDEX, and COPY is essential for managing the DDL lifecycle. These utilities resolve pending states created by ALTER TABLE operations. → Chapter 11: Further Reading
DB2 13 for z/OS Utility Guide and Reference
Detailed reference for the COPY, RECOVER, QUIESCE, REPORT RECOVERY, REBUILD INDEX, and CHECK DATA utilities. Essential for writing production-grade recovery JCL. - IBM Documentation: https://www.ibm.com/docs/en/db2-for-zos → Chapter 18 Further Reading: Backup, Recovery, and Logging
DB2 13 for z/OS: Administration Guide
Chapter on tablespace design, partitioning, and compression. IBM Knowledge Center. - Sections on Universal Table Spaces, DSSIZE, MAXPARTITIONS, SEGSIZE. - URL: https://www.ibm.com/docs/en/db2-for-zos/13 → Chapter 14: Further Reading
DB2 13 for z/OS: Managing Performance
Chapter on partitioning strategies and partition elimination. IBM Knowledge Center. https://www.ibm.com/docs/en/db2-for-zos/13 → Chapter 30 Further Reading
DB2 13 for z/OS: SQL Reference
CREATE TABLESPACE syntax, ALTER TABLESPACE, partition management. - Complete syntax diagrams for all tablespace-related DDL. → Chapter 14: Further Reading
DB2 13 for z/OS: Utility Guide and Reference
REORG TABLESPACE, RUNSTATS, COPY, DSN1COMP. - Covers partition-level utility execution and compression dictionary management. → Chapter 14: Further Reading
DB2 9 for z/OS Performance Topics
SG24-7473. Covers monitoring improvements in DB2 9, including enhanced DISPLAY commands and new IFCIDs. - **DB2 10 for z/OS Performance Topics** — SG24-7942. New monitoring capabilities in DB2 10, including improved accounting and statistics records. - **DB2 11 for z/OS Performance Topics** — SG24-8 → Chapter 20 Further Reading
DB2 Application Programming and SQL Guide
Covers external stored procedures in Java, C, and COBOL, including parameter styles, JVM configuration, and WLM setup for z/OS. → Chapter 34: Further Reading
Official Db2 container image with deployment instructions, environment variables, and configuration options. https://hub.docker.com/r/ibmcom/db2 → Chapter 31 Further Reading
Db2 Federated Systems Documentation
Complete reference for wrappers, server definitions, user mappings, nicknames, and federated query optimization. https://www.ibm.com/docs/en/db2/11.5?topic=federation → Chapter 31 Further Reading
Db2 for LUW — SQL Reference: System Catalog Views
Search for "Db2 LUW SYSCAT views" on IBM Documentation. The system catalog views section provides complete documentation for every SYSCAT view and its columns, along with the corresponding SYSSTAT updatable views. → Chapter 21: Further Reading and Resources
DB2 for LUW: EXPLAIN facility
Search for "db2expln" and "db2exfmt" in the Db2 11.5 documentation. These tools format access plan output that shows whether index-only access, matching scans, and other index-related optimizations are being applied. → Chapter 15: Further Reading and Resources
DB2 for LUW: Indexes
In the Db2 11.5 documentation, search for "Index design guidelines" and "CREATE INDEX statement." The administration guide covers index types (regular, expression-based, spatial, XML path), the INCLUDE clause, MDC, and index compression. → Chapter 15: Further Reading and Resources
DB2 for z/OS
the mainframe original, running the world's banks, insurers, and government agencies - **DB2 for LUW** (Linux, UNIX, Windows) -- the distributed version, powering mid-market enterprises, cloud deployments, and modern application stacks → IBM DB2: From First Query to Enterprise Architecture
DB2 for z/OS Administration Guide
Chapter on Monitoring and Tuning. IBM Knowledge Center. Covers DISPLAY commands, trace configuration, and SMF record interpretation in exhaustive detail. - **DB2 for z/OS Messages and Codes** — Reference for all DSNV, DSNB, DSNJ, and DSNT message prefixes encountered in DISPLAY command output and di → Chapter 20 Further Reading
DB2 for z/OS Utility Guide and Reference
The definitive reference for all z/OS utility syntax, parameters, and behavior. Covers REORG, RUNSTATS, COPY, RECOVER, LOAD, UNLOAD, and every other z/OS utility in exhaustive detail. Available on IBM Documentation (search for the specific DB2 version, e.g., "DB2 13 for z/OS Utility Guide"). - **DB2 → Chapter 17: Further Reading
Db2 for z/OS — SQL Reference: Catalog Tables
Search for "Db2 12 for z/OS catalog tables" on IBM Documentation (ibm.com/docs). The catalog tables section of the SQL Reference manual provides complete column-level documentation for every SYSIBM table. This is the definitive reference when you need to know what a specific catalog column contains. → Chapter 21: Further Reading and Resources
Db2 for z/OS — SQL Reference: DB2 Directory
The same SQL Reference manual includes a section on the DB2 directory (DSNDB01), describing the DBD, SPT01, SCT02, SYSLGRNX, and SYSUTILX structures. Search for "DB2 directory" within the Db2 12 for z/OS documentation. → Chapter 21: Further Reading and Resources
Db2 for z/OS — Utility Guide: RUNSTATS
The Utility Guide documents the RUNSTATS utility, including which catalog columns it updates, the various options for collecting different levels of statistics, and best practices for execution frequency. → Chapter 21: Further Reading and Resources
DB2 for z/OS: Accessing Distributed Data
Configuration for DRDA connections between z/OS and distributed platforms, including cloud endpoints. https://www.ibm.com/docs/en/db2-for-zos/13 → Chapter 31 Further Reading
DB2 for z/OS: EXPLAIN
Search for "Interpreting EXPLAIN output" in the Db2 for z/OS performance documentation. Understanding EXPLAIN is essential for verifying that your indexes are being used as intended. → Chapter 15: Further Reading and Resources
DB2 for z/OS: Indexes
In the Db2 12 for z/OS documentation, search for "Creating indexes" and "Index design guidelines." The administration guide covers clustering indexes, partitioned indexes (DPSI/NPSI), index compression, and PCTFREE settings. The SQL reference provides the complete CREATE INDEX syntax. → Chapter 15: Further Reading and Resources
DB2 for z/OS: Indexing Best Practices
IBM technical paper covering DPSI vs. NPSI trade-offs, partition key inclusion in indexes, and clustering considerations. → Chapter 30 Further Reading
DB2 is an ecosystem, not just a database
SQL, utilities, optimizer, storage, security, and monitoring must be understood as a whole 2. **The optimizer is your partner** -- understanding how it thinks lets you write SQL and design schemas that enable optimal performance 3. **Performance is not an afterthought** -- design and configuration d → IBM DB2: From First Query to Enterprise Architecture
DB2 LUW Database Administration Guide
Comprehensive coverage of REORG, RUNSTATS, BACKUP, RESTORE, ROLLFORWARD, and maintenance planning for LUW environments. - **DB2 LUW Command Reference** — Syntax details for all CLP commands including BACKUP DATABASE, RESTORE DATABASE, ROLLFORWARD DATABASE, and REORG. → Chapter 17: Further Reading
DB2 LUW Monitoring Guide
IBM Knowledge Center. Comprehensive coverage of snapshot monitors, MON_GET table functions, event monitors, and db2pd. - **DB2 LUW Troubleshooting and Tuning Database Performance** — IBM Knowledge Center. Covers db2diag.log analysis, FFDC, db2support, and systematic problem determination. - **DB2 LU → Chapter 20 Further Reading
DB2 member abnormal termination
the DB2 subsystem crashes (e.g., due to a software error) 2. **z/OS system failure** — the entire LPAR crashes 3. **CPC failure** — the physical machine goes down, affecting all LPARs on it 4. **Coupling facility failure** — the CF crashes (the most catastrophic scenario for data sharing) → Chapter 28: Data Sharing and Parallel Sysplex — Multi-Member DB2 on z/OS
DB2 Performance Expert for Multiplatforms
IBM Knowledge Center Documentation for IBM's performance monitoring tool, which includes visual access plan analysis, historical plan comparison, and automated regression detection. → Chapter 23: Further Reading
DB2 Performance Expert for Multiplatforms V3.2
IBM Redbook SG24-7357. Practical guide to using Performance Expert for monitoring and tuning, with real-world scenarios. - **DB2 Monitoring and Tuning** by Philip K. Gunning — Focuses on systematic approaches to performance monitoring with practical examples on both platforms. - **DB2 Developer's Gu → Chapter 20 Further Reading
Db2 REST Services
IBM documentation for enabling and creating REST services on Db2 for LUW, including service registration, authentication, and HTTPS configuration. - https://www.ibm.com/docs/en/db2/11.5?topic=services-rest → Chapter 35: Further Reading
DB2 SQL PL Reference
IBM Knowledge Center: "SQL Procedural Languages: Application Enablement and Support." The definitive reference for SQL PL syntax, including compound statements, handlers, cursors, and dynamic SQL. - LUW: https://www.ibm.com/docs/en/db2/11.5?topic=procedures-sql - z/OS: https://www.ibm.com/docs/en/db → Chapter 34: Further Reading
DB2 SQL Reference
CREATE PROCEDURE, CREATE FUNCTION, CREATE TYPE statements with full syntax diagrams and examples. → Chapter 34: Further Reading
IBM's built-in utility for extracting DDL and statistics from a DB2 for LUW database. db2look reads the SYSCAT views and generates CREATE statements, RUNSTATS output, and table space definitions. Understanding the catalog as taught in this chapter helps you understand and customize db2look output. → Chapter 21: Further Reading and Resources
db2move Utility Reference
Syntax and options for bulk data movement between Db2 instances. https://www.ibm.com/docs/en/db2/11.5?topic=commands-db2move → Chapter 31 Further Reading
DBD (Database Descriptor)
For every database, DB2 maintains an internal descriptor that maps the logical structure (tables, indexes, tablespaces) to the physical structure (VSAM data sets, page ranges, partition boundaries). When DB2 opens a tablespace, it consults the DBD to locate the underlying data sets. The DBD is the r → Chapter 21: Catalog and Directory — Understanding DB2's Data About Your Data
DBeaver
Open-source database tool that supports DB2 stored procedure editing and execution. Lacks the debugging capabilities of Data Studio but provides a lighter-weight alternative. → Chapter 34: Further Reading
DBeaver Power Tips for DB2:
Use Ctrl+Enter to execute the current statement (not the entire script). - Right-click a table name and select "Read Data in SQL console" for a quick SELECT *. - Use the ER Diagram feature (right-click a schema > "View Diagram") to visualize table relationships. - Configure the "SQL Formatter" under → Chapter 4: Setting Up Your DB2 Environment -- Installation, Configuration, and Your First Database
dbForge Studio for DB2
Commercial IDE with advanced debugging, code generation, and testing features for DB2 stored procedures. → Chapter 34: Further Reading
Debezium Documentation
Complete guide to the Debezium DB2 connector, including setup, configuration options, event format, and troubleshooting. - https://debezium.io/documentation/reference/connectors/db2.html → Chapter 35: Further Reading
Use a half-open interval: EFFECTIVE_DATE is inclusive, END_DATE is exclusive. This prevents gaps and overlaps. - The current record has END_DATE = NULL. - When a new rate takes effect, update the previous record's END_DATE and insert a new record. - Consider adding a CHECK constraint or trigger to p → Chapter 13: Logical Database Design — From Business Requirements to Entity-Relationship Models
Diagnose connection issues
firewall rules, authentication failures, driver version mismatches. 3. **Tune connection pools** — advising developers on pool sizes, validation queries, timeout settings. 4. **Identify SQL injection risks** in code reviews. 5. **Correlate application activity** to DB2 monitor data — matching applic → Chapter 33: JDBC, ODBC, and Python Access — Connecting Modern Applications to DB2
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. → Chapter 1 Exercises: What Is IBM DB2?
15 cursor declarations and 15 OPEN/FETCH/CLOSE code paths make maintenance difficult - Cannot support new combinations without code changes - Combinatorial explosion: 5 filter criteria with optional presence means 2^5 = 32 possible combinations. The current 15 queries do not even cover all of them. → Case Study 32.2: Static vs Dynamic SQL — A Performance Decision
DISTINCT
DB2 must deduplicate before the outer query can process results. - **GROUP BY with aggregation** — Aggregates must complete before outer predicates can apply. - **UNION** (but not UNION ALL) — Deduplication creates a fence. - **OLAP/window functions** — Must compute over the complete partition befor → Chapter 8: Subqueries and Common Table Expressions — Queries Within Queries
Utilities for copying and printing VSAM data sets, including catalog and directory data sets. Used in advanced recovery scenarios when catalog data needs to be examined at the physical level. → Chapter 21: Further Reading and Resources
DSNACCOR (z/OS)
The DB2 Autonomic Computing sample stored procedure that uses Real-Time Statistics to generate utility recommendations. IBM provides DSNACCOR as a sample that you can customize — it implements many of the same patterns described in Section 21.10 and the Lighthouse case study. → Chapter 21: Further Reading and Resources
Dynamic SQL is built and prepared at runtime
necessary when the SQL text varies (different tables, different WHERE columns). Use PREPARE once and EXECUTE many times when possible. Always use parameter markers to maximize dynamic statement cache hits. → Chapter 32 Key Takeaways
Dynamic SQL:
**CACHEDYN**: When set to YES, DB2 caches prepared dynamic SQL statements in a global cache. This is critical for performance in environments with repetitive dynamic SQL (which is most modern applications). - **MAXKEEPD**: The maximum number of statements in the dynamic statement cache. Size this ba → Chapter 4: Setting Up Your DB2 Environment -- Installation, Configuration, and Your First Database
Near-zero logging for the INSERT itself. Only the COMMIT is logged plus metadata about the table's state. However, the table is unrecoverable if the transaction fails. → Chapter 9 Quiz: Data Modification
Used when an attribute changes over time and the business needs to know the value at any historical point. Appropriate for interest rates, fee schedules, addresses, and job assignments. Implemented with EFFECTIVE_DATE and END_DATE columns using half-open intervals. → Chapter 13 Quiz: Logical Database Design
for a major bank, one hour of unplanned downtime can cost millions of dollars in lost revenue, regulatory penalties, and reputational damage - **Deferred hardware upgrades** — adding a member is often cheaper than upgrading to a larger CPC - **Workload flexibility** — batch and online workloads can → Chapter 28: Data Sharing and Parallel Sysplex — Multi-Member DB2 on z/OS
maximum GBP-dependent activity - **High update rates on shared data** — constant P-lock negotiations and XI signals - **Small, frequent transactions with high lock counts** — lock structure becomes a bottleneck - **Undersized coupling facility** — CF becomes a chokepoint → Chapter 28: Data Sharing and Parallel Sysplex — Multi-Member DB2 on z/OS
Examples at Meridian Bank:
One BRANCH has many EMPLOYEES; each EMPLOYEE works at one BRANCH. - One CUSTOMER has many ACCOUNTS; each ACCOUNT belongs to one CUSTOMER (for now — we will revisit joint accounts later). - One ACCOUNT has many TRANSACTIONS; each TRANSACTION belongs to one ACCOUNT. - One LOAN_OFFICER (EMPLOYEE) manag → Chapter 13: Logical Database Design — From Business Requirements to Entity-Relationship Models
Each call has fixed overhead for thread management, authorization checking, and result formatting 2. **Better buffer pool utilization** — DB2 can prefetch more efficiently when it knows you want multiple rows 3. **Reduced lock management overhead** — Lock requests can be batched 4. **Less context sw → Chapter 32: Embedded SQL for COBOL and C — Static SQL, Cursors, and the Bind Process
Ensure Docker Desktop has at least 4 GB of memory allocated (Settings > Resources > Memory). - Always include `--privileged=true` in the docker run command. - Check logs: `docker logs db2server` for specific error messages. - On macOS/Windows, ensure file sharing is enabled for the volume mount path → Appendix C: Environment Setup Guide
needs transaction changes within 2 seconds of commit 2. **Data warehouse loader** — needs all table changes, can tolerate 30-second latency 3. **Customer notification service** — needs ACCOUNTS and TRANSACTIONS changes, 5-second latency → Chapter 29 Exercises: HADR and Replication
FRAUD_INVESTIGATION
Parallel investigation process for flagged claims. | Attribute | Type | Notes | |-----------|------|-------| | INVESTIGATION_ID | Integer (PK) | | | CLAIM_ID | FK to CLAIM | | | OPENED_DATE | Date | | | INVESTIGATOR_ID | FK to EMPLOYEE | | | FRAUD_TYPE | Code | STAGED_ACCIDENT, INFLATED_CLAIM, ARSON → Case Study 2: Insurance Claims Schema Design
Free and Rebind strategies:
**REBIND** — Optimizes using current statistics. If an error occurs (e.g., a table was dropped), the original package remains. - **FREE PACKAGE / BIND PACKAGE** — Removes the old package and creates a new one from the DBRM. If BIND fails, the old package is gone. → Chapter 32: Embedded SQL for COBOL and C — Static SQL, Cursors, and the Bind Process
Identify the source table(s) 2. **WHERE** — Filter individual rows 3. **GROUP BY** — Form groups (covered in Chapter 7) 4. **HAVING** — Filter groups (covered in Chapter 7) 5. **SELECT** — Evaluate expressions and apply DISTINCT 6. **ORDER BY** — Sort the result set 7. **FETCH FIRST** — Limit the nu → Chapter 5: SQL Fundamentals — SELECT, WHERE, ORDER BY, and the Art of Asking Questions
multiple members are accessing the pageset concurrently for read/write. Pages must flow through the GBP for coherency. - **GBP-independent** — only one member is accessing the pageset, or all access is read-only. No GBP writes are needed. → Chapter 28: Data Sharing and Parallel Sysplex — Multi-Member DB2 on z/OS
The specification and best practices for GraphQL API design. - https://graphql.org/learn/ → Chapter 35: Further Reading
Group name
up to 8 characters, e.g., `DSNDBGP` - **Group attach name** — the name that applications use to connect, e.g., `DBANKGRP` - **Member names** — each DB2 subsystem in the group has a unique 4-character SSID (subsystem ID), e.g., `DB1A`, `DB1B`, `DB1C` → Chapter 28: Data Sharing and Parallel Sysplex — Multi-Member DB2 on z/OS
messages are persistent and transactional - **Network independence** — source and target can be on different networks, data centers, or continents - **Buffering** — if the target is temporarily unavailable, messages queue up in MQ → Chapter 29: HADR and Replication — High Availability for DB2 LUW
H
Hardcoded dates
She replaced static dates with dynamic date arithmetic so reports stay accurate over time. 2. **NULL handling** — She used COALESCE for display formatting and IS NULL in WHERE clauses to avoid silently excluding rows. 3. **Business vs. individual customers** — She discovered that the data model stor → Case Study 1: Customer Query Patterns at Meridian Bank
full table scan, index scan, index-only access, or something else - **Which indexes** are used, and how many key columns match the predicates - **How tables are joined** — nested loop, merge scan, or hash join - **Where sorts occur** and why - **Estimated costs** — how expensive the optimizer thinks → Chapter 23: Reading and Interpreting EXPLAIN — The Single Most Important DBA Skill
Follow the LUW path. Start with Section 4.2. - **I have mainframe access through my employer** -- Follow the z/OS path. Start with Section 4.4, then come back to read the LUW sections for breadth. - **I have both** -- Lucky you. Follow both paths. Use LUW for daily practice and z/OS for the exercise → Chapter 4: Setting Up Your DB2 Environment -- Installation, Configuration, and Your First Database
IBM Certification C1000-047 (DB2 11.1 DBA for LUW)
Advanced DDL topics including tablespace design, index strategies, online schema changes, and storage management. → Chapter 11: Further Reading
IBM Certified Database Administrator - DB2 LUW
REORG, RUNSTATS, BACKUP, RESTORE, ROLLFORWARD, LOAD, and EXPORT are all covered in the exam objectives. - **IBM Certified Advanced Database Administrator** — Advanced utility topics including SHRLEVEL CHANGE mechanics, FlashCopy integration, utility chaining with LISTDEF/TEMPLATE, and recovery from → Chapter 17: Further Reading
IBM Cloud Blog: Database Category
Regular articles on Db2 on Cloud features, performance tips, and customer stories. https://www.ibm.com/cloud/blog → Chapter 31 Further Reading
IBM Cloud Direct Link
Dedicated network connectivity between on-premises data centers and IBM Cloud. https://cloud.ibm.com/docs/dl → Chapter 31 Further Reading
IBM Cloud VPC Documentation
Configuring Virtual Private Clouds, subnets, security groups, and private endpoints for Db2. https://cloud.ibm.com/docs/vpc → Chapter 31 Further Reading
IBM Data and AI Community
IBM's community forums for Db2 users. Includes discussion boards, technical articles, and access to IBM product managers and developers. Search for "IBM Community Db2" to find the current URL. → Chapter 1: Further Reading and Resources
IBM Data Community Forums
Active forums where DB2 DBAs discuss utility strategies, troubleshoot problems, and share maintenance scripts. Search for utility-specific topics for real-world solutions. - **IDUG (International DB2 Users Group)** — Technical conferences and online content library with presentations on utility best → Chapter 17: Further Reading
IBM Data Replication: CDC for Db2
Technical guide for configuring Change Data Capture replication from DB2 z/OS and Db2 LUW to cloud targets. → Chapter 31 Further Reading
IBM Data Server Manager
Web-based monitoring and administration tool for DB2 LUW. Provides real-time monitoring dashboards, alert management, and historical data analysis without requiring custom SQL scripts. - **IBM OMEGAMON for DB2 Performance Expert on z/OS** — Enterprise-grade monitoring with real-time dashboards, thre → Chapter 20 Further Reading
IBM Data Studio
Free IDE for DB2 development that includes a visual stored procedure debugger, SQL PL editor with syntax highlighting, and deployment tools. Essential for serious SQL PL development. → Chapter 34: Further Reading
IBM Database Conversion Workbench (DCW)
Free tool available from IBM for Oracle-to-DB2 schema and stored procedure conversion. Search for "Database Conversion Workbench" on ibm.com to find the current download location and documentation. The tool includes conversion reports that are invaluable for migration planning. → Chapter 16: Further Reading and Resources
IBM Database Migration Guide
Strategies and tools for migrating to Db2 on Cloud from on-premises environments. https://cloud.ibm.com/docs/Db2onCloud?topic=Db2onCloud-migration → Chapter 31 Further Reading
IBM DB2 13 for z/OS Documentation
SQL Reference: https://www.ibm.com/docs/en/db2-for-zos/13 - Covers all data types, CREATE TABLE syntax, constraint definitions, and catalog tables specific to the z/OS platform. - Pay particular attention to the "DB2 SQL Reference" volume for data type details and constraint syntax. → Chapter 2: Further Reading
IBM Db2 for Linux, UNIX, and Windows Documentation
Search for "Db2 LUW" on IBM Documentation. The Db2 11.5 documentation set covers installation, SQL reference, administration, and application development. → Chapter 1: Further Reading and Resources
IBM Db2 for LUW 11.5 Knowledge Center
docs.ibm.com — The complete reference for all SQL, administration commands, configuration parameters, and monitoring interfaces used in this chapter. → Chapter 36 Further Reading
IBM Db2 for z/OS 13 Knowledge Center
The complete reference for z/OS-specific utilities, commands, and subsystem configuration. → Chapter 36 Further Reading
IBM DB2 for z/OS Community
Forum discussions on partition key selection, DPSI vs. NPSI trade-offs, and rolling window automation. https://community.ibm.com/community/user/datamanagement → Chapter 30 Further Reading
IBM Db2 for z/OS Documentation
Search for "Db2 for z/OS" on IBM Documentation (ibm.com/docs). Look for the "Db2 12 for z/OS" collection, which includes product overviews, SQL reference, administration guides, and utility references. → Chapter 1: Further Reading and Resources
IBM Db2 Knowledge Center: "Security"
Covers authentication, authorization, LBAC, RCAC, audit policies, and encryption configuration. The reference for all security features demonstrated in Section 36.6. → Chapter 36 Further Reading
IBM Db2 on Cloud Documentation
Complete reference for provisioning, connecting, managing, and scaling Db2 on Cloud instances. https://cloud.ibm.com/docs/Db2onCloud → Chapter 31 Further Reading
IBM Db2 on Cloud: Getting Started Tutorial
Step-by-step guide to provisioning and connecting to your first Db2 on Cloud instance. https://cloud.ibm.com/docs/Db2onCloud?topic=Db2onCloud-getting-started → Chapter 31 Further Reading
IBM Db2 on Cloud: High Availability
Details on the multi-zone HA configuration, automatic failover, and RPO/RTO characteristics. https://cloud.ibm.com/docs/Db2onCloud?topic=Db2onCloud-ha → Chapter 31 Further Reading
IBM Db2 Operator for Kubernetes
Documentation for the Db2uCluster custom resource and operator-based deployment on OpenShift and Kubernetes. https://www.ibm.com/docs/en/db2/11.5?topic=deployments-db2-kubernetes → Chapter 31 Further Reading
IBM DB2 SQL Reference
For the specific SQL syntax of all queries demonstrated in this chapter, including MON_GET functions, SYSCAT views, and administrative routines. → Chapter 36 Further Reading
IBM Db2 Warehouse on Cloud Documentation
Reference for the analytics-optimized managed service, including BLU Acceleration, data loading, and external tables. https://cloud.ibm.com/docs/Db2whc → Chapter 31 Further Reading
IBM developerWorks (now IBM Developer)
Tutorials and sample code for DB2 DDL on both platforms. Search for "DB2 CREATE TABLE best practices" and "DB2 tablespace design." → Chapter 11: Further Reading
FIPS 140-2 Level 4 certified HSM for the highest level of key management security. https://cloud.ibm.com/docs/hs-crypto → Chapter 31 Further Reading
IBM InfoSphere Data Replication
Documentation for configuring CDC (Change Data Capture) on DB2 for z/OS and LUW, including log-based capture, apply agents, and Kafka integration. → Chapter 35: Further Reading
IBM Key Protect
Key management service for encryption at rest. BYOK configuration and key rotation. https://cloud.ibm.com/docs/key-protect → Chapter 31 Further Reading
IBM Redbook: "IBM DB2 Certification Guide"
IBM has published certification study guides as Redbooks for various DB2 versions. Search the IBM Redbooks library (redbooks.ibm.com) for the latest edition corresponding to your target certification. → Chapter 37 Further Reading
IBM Redbooks
IBM Redbooks (redbooks.ibm.com) publish in-depth technical guides written by IBM specialists and customers. Particularly relevant titles include: - "DB2 for z/OS: Data Sharing in a Nutshell" (various editions) - "DB2 12 for z/OS Technical Overview" (SG24-8482 or later) - "Db2 11.1 Certification Stud → Chapter 1: Further Reading and Resources
IBM Research: AI for Database Management
IBM publishes research on AI-assisted database administration, including automatic indexing, workload prediction, and anomaly detection. Available through the IBM Research website. → Chapter 37 Further Reading
IBM's technical conferences include sessions on DB2 version migration, with specific guidance on Compatibility Mode and New Function Mode transitions. → Chapter 16: Further Reading and Resources
the standby must be a restored image of the primary (same structure, same data) 2. **Compatible DB2 versions** — primary and standby should run the same DB2 version (minor version differences are tolerated during rolling upgrades) 3. **Network connectivity** — TCP/IP connectivity between primary and → Chapter 29: HADR and Replication — High Availability for DB2 LUW
IDUG (International DB2 Users Group)
https://www.idug.org — The largest community of DB2 professionals. Technical library, conference presentations, and discussion forums cover real-world DDL challenges and solutions. → Chapter 11: Further Reading
IDUG Annual Conferences
Migration and schema evolution are perennial topics at IDUG conferences. Look for presentations tagged with "migration," "ALTER TABLE," "REORG," or "online schema change." → Chapter 16: Further Reading and Resources
IDUG Conference Presentations
IDUG makes many of its conference presentations available to members. These are often the most practical, real-world content available on DB2 topics. → Chapter 1: Further Reading and Resources
IEEE 754-2008 (Floating-Point Arithmetic)
The standard that defines DECFLOAT behavior. Relevant if you use DECFLOAT(16) or DECFLOAT(34) for financial interchange data. → Chapter 11: Further Reading
If you are new to databases entirely
perhaps a COBOL programmer who has been handed DBA responsibilities, or a computer science student who wants to learn a production database system -- start at Chapter 1 and read sequentially. Parts I and II (Foundations and SQL Mastery) are written with you in mind. The tone is patient, the examples → Preface
immutable
they represent completed financial events and must not be modified (a regulatory requirement). Pending transactions (in PENDING_TRANSACTION) are **mutable** — they change status, can be cancelled, and eventually either post (becoming a TRANSACTION row) or expire. Separating them enforces the immutab → Chapter 13 Quiz: Logical Database Design
Important behaviors:
Skipped rows are silently omitted from the result set. There is no indication of how many rows were skipped. - Aggregate functions (COUNT, SUM) produce results that exclude skipped rows. This is by design but can be confusing. - SKIP LOCKED DATA interacts with isolation levels: under RR, its use is → Chapter 26: Locking, Concurrency, and Isolation Levels — Balancing Throughput and Consistency
foreign keys that are declared but not enforced (`NOT ENFORCED`). These are used when the application guarantees integrity and you want the optimizer to have the constraint information for query optimization without the overhead of enforcement. Use with extreme caution. → Chapter 2: The Relational Model — How DB2 Thinks About Data
The result set is a snapshot; changes are not visible - **SENSITIVE STATIC** — The result set size is fixed, but updates to existing rows are visible - **SENSITIVE DYNAMIC** — Inserts, updates, and deletes by others are visible - **ASENSITIVE** (default) — DB2 decides; behavior may vary → Chapter 32: Embedded SQL for COBOL and C — Static SQL, Cursors, and the Bind Process
Step 1: Access ACCOUNTS via IX_ACCT_TYPE (an index on ACCT_TYPE). MATCHCOLS = 1 — but wait, the predicate is on CUST_ID, not ACCT_TYPE. Why is the optimizer using this index? - Step 2: The subquery has been transformed into a join (METHOD = 1, not a separate QBLOCKNO). TRANSACTIONS is accessed via I → Case Study 2: Access Path Regression After REBIND
Interpretation of the old plan:
Step 1: Access ACCOUNTS via IX_ACCT_CUST with MATCHCOLS = 1 on CUST_ID. For a typical customer with 3-5 accounts, this returns a handful of rows. - Step 2: The correlated subquery accesses TRANSACTIONS via IX_TRANS_ACCT(ACCT_ID, TRANS_DATE) with MATCHCOLS = 1. Because the subquery needs only MAX(TRA → Case Study 2: Access Path Regression After REBIND
Interpreting db2pd buffer pool output:
**HitRatio**: Should be above 95% for OLTP workloads. Below 80% requires immediate investigation. - **nPagesDirty**: If approaching nPages, the page cleaners cannot keep up — increase NUM_IOCLEANERS or buffer pool size. - **Prefetch Wait Time**: If non-zero and growing, I/O subsystem is saturated. - → Chapter 20: Monitoring and Problem Determination — Knowing What's Wrong Before the Phone Rings
Interpreting the accounting report:
**Class 1 minus Class 2** = time spent outside DB2 (application processing, network) - **Class 2 minus Class 2 CPU** = in-DB2 wait time - **High lock wait** relative to total elapsed time = lock contention problem - **Sort overflows > 0** = sort heap too small or query generating excessive sorts - * → Chapter 20: Monitoring and Problem Determination — Knowing What's Wrong Before the Phone Rings
IS (Intent Share)
the member may read pages from this pageset - **IX (Intent Exclusive)** — the member may read or update pages - **S (Share)** — the member has read the pageset; no one is updating it - **SIX (Share with Intent Exclusive)** — combination of S and IX - **X (Exclusive)** — the member is the only one ac → Chapter 28: Data Sharing and Parallel Sysplex — Multi-Member DB2 on z/OS
ISO 3166-1 (Country Codes)
Referenced in data type selection for language, country, and currency columns. Using ISO-standard code lengths (CHAR(2), CHAR(3)) ensures compatibility with international data. → Chapter 11: Further Reading
ISO/IEC 9075:2023 (SQL Standard)
The international standard that defines the SQL language, including DDL syntax for CREATE TABLE, constraints, and data types. DB2 implements the standard with extensions. Understanding where DB2 conforms to the standard and where it extends it helps when working with cross-platform DDL. → Chapter 11: Further Reading
Isolate the new workload
if a new batch query is scanning CUSTOMER, move it to a different buffer pool or schedule it outside business hours. If it must run concurrently, increase the buffer pool to accommodate both workloads. 3. **Address OS memory pressure** — identify and resolve the competing memory consumer. Consider p → Chapter 3 Exercises: DB2 Architecture
Old-style comma join (not a performance issue per se, but obscures intent) - `YEAR()` and `MONTH()` on `last_interest_date` are non-sargable - ORDER BY requires a sort since no supporting index exists - No EXPLAIN has ever been run; no one knows the actual access path → Case Study 1: Batch Window Optimization
the mechanism for combining data from multiple tables in a single query. You will learn INNER JOIN, LEFT/RIGHT OUTER JOIN, FULL OUTER JOIN, CROSS JOIN, and self-joins, all applied to the Meridian Bank database. The ability to join tables transforms SQL from a simple lookup tool into a language for a → Chapter 5: SQL Fundamentals — SELECT, WHERE, ORDER BY, and the Art of Asking Questions
joint accounts
a single account held by multiple customers. With a simple FK (CUSTOMER_ID in ACCOUNT), each account can belong to only one customer. Joint checking accounts, authorized signers, beneficiary designations, and trust arrangements all require multiple customers to be associated with a single account. → Chapter 13 Quiz: Logical Database Design
The DB2 product family is not a single product: DB2 for LUW, DB2 for z/OS, and Db2 on Cloud serve different markets and have different architectures. Students should understand this distinction from Day 1 to avoid confusion later. - The historical context matters: DB2's roots in System R and the rel → Teaching Notes for All Chapters
Key design decisions:
**Restart capability:** The batch_control table tracks the last successfully processed account_id. On restart, processing resumes from where it left off. - **Commit frequency:** Every 5,000 rows (configurable) to manage log space and locks. - **Ordered processing:** Accounts are processed in account → Chapter 9 Quiz: Data Modification
**CACHE n** -- DB2 pre-allocates n values in memory for performance. This is critical for high-insert environments. Without caching, every sequence value requires a catalog update. With CACHE 50, DB2 only updates the catalog every 50th value. - **NO CYCLE** -- The sequence does not wrap around when → Chapter 9: Data Modification: INSERT, UPDATE, DELETE, MERGE, and Transaction Control
Key Tables:
`INVENTORY` — 500,000 SKUs, updated on every order - `ORDERS` — 100 million rows, append-only during order placement - `ORDER_ITEMS` — 300 million rows, append-only - `PAYMENTS` — 80 million rows - **Isolation Level:** CS (default) for all application code - **LOCKTIMEOUT:** 30 seconds - **DLCHKTIME → Case Study 01: Deadlock Investigation at an Online Retailer
Key Takeaways
the essential points, distilled. Useful for review and exam preparation. - **Exercises** -- hands-on problems at multiple difficulty levels (Foundational, Intermediate, Advanced, DB2 Clinic) - **Quiz** -- knowledge-check questions in certification exam style - **Case Studies** -- two per chapter, pr → How to Use This Book
Kubernetes official documentation on StatefulSets, persistent volume claims, and ordered pod management. https://kubernetes.io/docs/concepts/workloads/controllers/statefulsets/ → Chapter 31 Further Reading
no triggers, no additional writes to the source database - **Transactional consistency** — changes are captured at commit boundaries - **Minimal source overhead** — the capture program reads the log asynchronously → Chapter 29: HADR and Replication — High Availability for DB2 LUW
LOGARCHMETH1
Primary archive method. - `OFF` — No archiving (circular logging) - `LOGRETAIN` — Keep log files in the active log directory - `DISK:/path/` — Archive to a specific disk directory - `TSM:management_class` — Archive to IBM Spectrum Protect (Tivoli Storage Manager) - `VENDOR:/path/to/library` — Archiv → Chapter 18: Backup, Recovery, and Logging — Protecting Data Against Every Failure Mode
**LOGLOAD**: The number of log records written between checkpoints. More frequent checkpoints (smaller LOGLOAD) reduce restart time after a failure but increase overhead during normal operation. Less frequent checkpoints (larger LOGLOAD) improve throughput but increase restart time. - Active log dat → Chapter 4: Setting Up Your DB2 Environment -- Installation, Configuration, and Your First Database
Number of secondary log files, allocated on demand when primary logs are exhausted. - Default: 12 - Production: 20-100 - Setting to -1 means "allocate as many as needed" (risky — can fill a filesystem) - Secondary log allocation means you are under pressure — investigate the cause → Chapter 18: Backup, Recovery, and Logging — Protecting Data Against Every Failure Mode
The real-world event that caused the loss. | Attribute | Type | Notes | |-----------|------|-------| | LOSS_EVENT_ID | Integer (PK) | | | EVENT_DATE | Date | | | EVENT_TYPE | Code | COLLISION, WEATHER, FIRE, THEFT, VANDALISM, LIABILITY | | DESCRIPTION | Varchar(2000) | | | LOCATION_ADDRESS | Varchar → Case Study 2: Insurance Claims Schema Design
LOSS_TYPE
Categorization of losses - **COVERAGE_TYPE** — Definitions of coverage categories - **PAYMENT_TYPE** — Indemnity, expense, subrogation recovery, salvage - **DOCUMENT_TYPE** — Police report, medical record, estimate, photo - **NOTE_TYPE** — General, investigation, legal, medical, supervisory → Case Study 2: Insurance Claims Schema Design
lost
they are never assigned. When DB2 restarts, it continues generating values from the start of the next cache block. This creates gaps in the sequence. → Chapter 9 Quiz: Data Modification
Low-latency replication
changes are captured and applied in near-real-time - **Bidirectional replication** — two databases can both accept writes and replicate changes to each other - **Cross-platform replication** — DB2 LUW to DB2 z/OS, DB2 to Oracle, etc. - **Multi-target replication** — one source can replicate to multi → Chapter 29: HADR and Replication — High Availability for DB2 LUW
to read and write the same set of databases concurrently. Every member has full read/write access to every table, index, and tablespace in the shared data. There is no partitioning of ownership, no "this member owns table X while that member owns table Y." Every member can do everything, at any time → Chapter 28: Data Sharing and Parallel Sysplex — Multi-Member DB2 on z/OS
Memory (EDM Pool and Buffer Pools):
**EDMPOOL**: The Environmental Descriptor Manager pool size in kilobytes. This caches database descriptors (DBDs), packages, cursor tables, and authorization cache entries. An undersized EDM pool forces DB2 to reload these structures repeatedly from the directory, which is expensive. - Buffer pools → Chapter 4: Setting Up Your DB2 Environment -- Installation, Configuration, and Your First Database
MERGE INTO
the target table that will be modified. 2. **USING** -- the source dataset. This can be a table, a view, a subquery, or a VALUES clause. 3. **ON** -- the matching condition. This determines whether a source row "matches" a target row. 4. **WHEN MATCHED THEN UPDATE** -- what to do when the ON conditi → Chapter 9: Data Modification: INSERT, UPDATE, DELETE, MERGE, and Transaction Control
IBM's command-line migration toolkit for moving data and objects from various source databases to DB2. Complements DCW with data movement capabilities. → Chapter 16: Further Reading and Resources
multiple dimensions simultaneously
something a traditional clustering index cannot do (which is limited to one key). MDC organizes data into "blocks" (groups of consecutive pages) based on combinations of dimension column values. DB2 creates **dimension block indexes** that point to blocks rather than individual rows. This means a qu → Chapter 15 Quiz: Index Design
N
name
for example, `CUSTOMER`. - A **set of columns**, each with a name and data type. - Zero or more **constraints** (primary keys, foreign keys, check constraints, unique constraints). - **Rows** containing the actual data. → Chapter 2: The Relational Model — How DB2 Thinks About Data
the capture reads log files that DB2 has already written - **No schema changes required** — no trigger DDL, no audit columns - **Minimal latency** — changes are captured within milliseconds of the commit - **Complete change record** — the log contains before-images and after-images of every changed → Chapter 29: HADR and Replication — High Availability for DB2 LUW
performance problems were addressed reactively, often by adding hardware. - **No capacity planning process** — storage was added when tablespaces filled up, not before. - **Inconsistent security practices** — each DBA managed security differently, with no unified role-based access model. - **No comm → Case Study 37.2: Building a DB2 Center of Excellence
structuring data for integrity and performance. - **Physical Database Design** -- tablespaces, partitioning, compression, and storage optimization. - **Security** -- authentication, authorization, roles, and row-level security. - **Concurrency and Locking** -- isolation levels, lock escalation, and → Chapter 12: Views, Triggers, and Stored Procedures — Encapsulating Logic in the Database
The standard for defining REST APIs. z/OS Connect generates OpenAPI specs automatically; understanding the format helps customize and extend them. - https://spec.openapis.org/oas/latest.html → Chapter 35: Further Reading
Advantages: Critical OLTP stays on proven z/OS; new development uses modern cloud tooling; analytics and ML workloads run on cost-effective cloud infrastructure. - Disadvantages: Complexity of maintaining two platforms; data synchronization latency and consistency challenges; operational overhead of → Comprehensive Final Exam --- All Parts (Chapters 1--40)
The query requests sorted output, and no index provides the data in that order - **GROUP BY** — Aggregation may require a sort to group matching values together (although hash-based grouping can sometimes avoid this) - **DISTINCT** — Duplicate elimination may require sorting - **UNION** (not UNION A → Chapter 23: Reading and Interpreting EXPLAIN — The Single Most Important DBA Skill
P
page cleaners
background threads that write dirty pages to disk. The number of page cleaners is controlled by the `NUM_IOCLEANERS` configuration parameter. Page cleaners are triggered by several conditions: a buffer pool reaching its dirty page threshold, the log sequence number gap growing too large, or a page s → Chapter 25: Buffer Pool and Memory Tuning — Giving DB2 the Right Resources
unbalanced workloads create hot members while others idle - **Coupling facility overhead** — if the same data is accessed by multiple members, GBP activity increases - **Recovery exposure** — if all work goes to one member, that member's failure has maximum impact → Chapter 28: Data Sharing and Parallel Sysplex — Multi-Member DB2 on z/OS
separating the logical view of data from its physical storage, so any query could be expressed without regard to how the data was physically organized. → Chapter 2 Quiz: The Relational Model
A blog aggregator collecting posts from DB2 professionals worldwide. Search for DDL-related posts to see real production examples and lessons learned. → Chapter 11: Further Reading
PlanetDB2
Blog aggregator for DB2 content. Search for partitioning-related articles. http://www.planetdb2.com → Chapter 30 Further Reading
Assign: The 10-20 most frequently accessed transaction tables - Size: Large — this is where you spend the lion's share of your memory budget - Character: Random read/write, high concurrency, latency-sensitive - VPSEQT: Low (5-20%) to prevent sequential eviction → Chapter 25: Buffer Pool and Memory Tuning — Giving DB2 the Right Resources
Assign: Small, frequently read reference tables (branch codes, currency codes) - Size: Small but sufficient to hold entire tables in memory permanently - Character: Read-only or read-mostly, random access, zero physical I/O tolerance - PGSTEAL: NONE on z/OS; fixed large enough on LUW → Chapter 25: Buffer Pool and Memory Tuning — Giving DB2 the Right Resources
changes are applied in commit-consistent groups - **Handles conflicts** — in bidirectional mode, conflicts are detected and resolved - **Maintains referential integrity** — dependent changes are applied in the correct order → Chapter 29: HADR and Replication — High Availability for DB2 LUW
the active database serving all read/write traffic 2. **Standby database** — receives log records from the primary and replays them continuously 3. **Log shipping** — transaction log records are sent from primary to standby over TCP/IP 4. **Log replay** — the standby applies log records to keep its → Chapter 29: HADR and Replication — High Availability for DB2 LUW
Best practices from Apollo (the leading GraphQL platform) for designing production GraphQL APIs. - https://principledgraphql.com/ → Chapter 35: Further Reading
Commutative: σ_p(σ_q(R)) = σ_q(σ_p(R)) - Cascadable: σ_{p AND q}(R) = σ_p(σ_q(R)) - The optimizer exploits commutativity to push selective predicates down the plan tree, reducing intermediate result sizes early. → Appendix A: Mathematical Foundations for DB2 Practitioners
drain work from M1 by removing it from the WLM routing table 2. **Stop M1** — shut down DB2 on M1 cleanly 3. **Apply PTFs** — install the maintenance on M1's libraries 4. **Restart M1** — M1 rejoins the group and resumes processing 5. **Repeat** for M2, M3, etc. → Chapter 28: Data Sharing and Parallel Sysplex — Multi-Member DB2 on z/OS
R
random I/O
which can be orders of magnitude slower. When the cluster ratio drops below a threshold (commonly 80-90%), a REORG is recommended to restore physical order. → Chapter 15 Quiz: Index Design
RBA (Relative Byte Address)
a monotonically increasing byte offset into the conceptual log stream. On LUW, they are addressed by **LSN (Log Sequence Number)**. The concept is identical: every log record has a unique, ordered address that tells you exactly where it falls in the sequence of all changes ever made to the database. → Chapter 18: Backup, Recovery, and Logging — Protecting Data Against Every Failure Mode
READ
read a page from the GBP - **WRITE** — write a page to the GBP - **CASTOUT** — write a dirty GBP page to DASD and update the directory - **DELETE NAME** — remove a page from the GBP - **CROSS-INVALIDATE** — mark a page in another member's local buffer pool as invalid → Chapter 28: Data Sharing and Parallel Sysplex — Multi-Member DB2 on z/OS
Read Codd's 1970 paper
even if you only read the first few pages, it will deepen your appreciation for the relational model's elegance. 3. **Browse the IBM DB2 documentation for CREATE TABLE** on your target platform (z/OS or LUW) — see how the data types and constraints from this chapter map to the official syntax. 4. ** → Chapter 2: Further Reading
IBM Data Studio (free) for GUI-based administration and SQL development. - The Db2 command-line processor (CLP) and CLPPlus, which ship with Db2 Community Edition. - DBeaver Community Edition as a lightweight, cross-platform alternative. → Instructor Guide: Overview
IBM Db2 Knowledge Center (online documentation) - *DB2 SQL Cookbook* by Graeme Birchall (free PDF, complementary reference) - Access to IBM Z Xplore (free, for mainframe exploration in Weeks 14) → 15-Week University Course Syllabus
recovery runbook
a step-by-step document that any competent DBA can follow under pressure. Include: - Exact commands (not "restore the database" but the full command with all parameters) - Expected output at each step - Decision points (if X happens, do Y; if Z happens, do W) - Contact information for escalation - E → Chapter 18: Backup, Recovery, and Logging — Protecting Data Against Every Failure Mode
mathematical tables where each row is a **tuple** (an ordered collection of values) and each column is an **attribute** (a named domain of values). Provide a high-level language for manipulating these relations (what became SQL), and let the database management system figure out the most efficient w → Chapter 2: The Relational Model — How DB2 Thinks About Data
the same type of data (monthly sales) repeated as separate columns. It violates First Normal Form because the "month" is encoded in the column name rather than as data. Problems include: - Adding a 13th period (e.g., a fiscal adjustment period) requires ALTER TABLE. - Querying "which month had the h → Chapter 13 Quiz: Logical Database Design
it receives log records in real time but waits a configured interval (e.g., 1 hour) before replaying them. This protects against logical corruption: if someone accidentally drops a table, the delayed standby still has the table for up to the delay interval. → Chapter 29: HADR and Replication — High Availability for DB2 LUW
Report A (Monthly transaction summary by branch):
Likely bottleneck: Full table scan on the 500-million-row transactions table. The join to accounts and branches adds overhead. - Recommendation: Range-partition the transactions table by transaction_date (monthly partitions). This enables partition elimination so the monthly report reads only the re → Comprehensive Final Exam --- All Parts (Chapters 1--40)
Report B (Customer balance trend analysis):
Likely bottleneck: Window function computation over 120 million rows, likely requiring a large sort operation. - Recommendation: Create an MQT that pre-aggregates monthly balance snapshots per customer. The window function can then operate on the smaller MQT (12 rows per customer instead of the raw → Comprehensive Final Exam --- All Parts (Chapters 1--40)
Report C (Delinquent loan report):
Likely bottleneck: Finding the most recent payment per loan requires a sort or correlated subquery on 50 million payment rows. - Recommendation: Create an index on loan_payments(loan_id, payment_date DESC) to enable efficient retrieval of the most recent payment per loan. Consider a lateral join or → Comprehensive Final Exam --- All Parts (Chapters 1--40)
offload long-running reports from the primary - **Data validation** — verify data integrity without impacting production - **Development/testing** — read production data for testing purposes - **Disaster preparedness** — ensure the standby is functional by actually using it → Chapter 29: HADR and Replication — High Availability for DB2 LUW
Required:
*IBM DB2: From First Query to Enterprise Architecture* (this textbook) - A computer meeting the lab environment requirements (see Instructor Guide Overview) - Access to the companion Git repository → 15-Week University Course Syllabus
Use a `WITH HOLD` cursor - Use `FOR UPDATE OF` for the status column - Use positioned UPDATE - Handle errors gracefully — do not let one bad row stop the entire batch → Chapter 32 Exercises: Embedded SQL for COBOL and C
**Source wins** — the incoming change overwrites the local change - **Target wins** — the local change is preserved, incoming change is discarded - **Timestamp-based** — the most recent change (by timestamp) wins - **Custom stored procedure** — a user-defined procedure examines both versions and dec → Chapter 29: HADR and Replication — High Availability for DB2 LUW
RPO: 6 seconds (the Q Replication lag at the time of failure) - RTO: 10 minutes (from failure to service restoration) - 847 European transactions in the Q Replication queue were lost (within the 10-second RPO tolerance) - All other transactions were preserved → Case Study 29.2: Multi-Site Replication for Global Operations
**S (Share):** "I am reading this row. Others may also read it, but no one may modify it until I release my lock." - **U (Update):** "I am reading this row with the intention of updating it. Others may read it (S locks are compatible), but no other transaction may acquire a U or X lock." The U lock → Chapter 26: Locking, Concurrency, and Isolation Levels — Balancing Throughput and Consistency
S
SAF/RACF integration
API access is controlled by RACF profiles. A mobile app user authenticated via OAuth2 is mapped to a RACF user ID, and that user ID's DB2 privileges govern what data they can access. - **TLS 1.2/1.3** — all API traffic is encrypted in transit. - **API keys** — for partner identification and basic th → Chapter 35: RESTful APIs and Modern Access Patterns: z/OS Connect, GraphQL, and Event Streaming
20-25 correct: Excellent — you have strong command of the material - 15-19 correct: Good — review the sections where you missed questions - 10-14 correct: Fair — re-read the chapter with the quiz questions in mind - Below 10: Re-read the chapter carefully, then retake this quiz in 48 hours → Chapter 1 Quiz: What Is IBM DB2?
weekday vs. weekend, business hours vs. batch window, month-end vs. mid-month. 3. **Calculate statistical ranges** — mean, standard deviation, 95th percentile for each metric. 4. **Document the baseline** — store it where your monitoring tools can reference it. 5. **Review and refresh** — baselines → Chapter 20: Monitoring and Problem Determination — Knowing What's Wrong Before the Phone Rings
SELECT (Restriction)
choosing rows that satisfy a condition — corresponds to the SQL **WHERE** clause. 2. **PROJECT (Projection)** — choosing specific columns — corresponds to the **column list** in the SQL SELECT clause. 3. **JOIN** — combining rows from two tables based on a matching condition — corresponds to the SQL → Chapter 2 Quiz: The Relational Model
Step-by-step guide for creating SQL and external stored procedures and functions on DB2 for z/OS, with production-ready patterns. → Chapter 34: Further Reading
SG24-8077: "DB2 11 for z/OS Technical Overview"
Covers SQL PL enhancements in DB2 11, including array support, new handler capabilities, and performance improvements. → Chapter 34: Further Reading
SG24-8418: "IBM Event Streams (Kafka) on z/OS"
Guide to running Kafka on z/OS for event-driven architectures with DB2, including CDC integration patterns. → Chapter 35: Further Reading
SG24-8481: "z/OS Connect Enterprise Edition V3"
Step-by-step implementation guide for z/OS Connect, including real-world examples of exposing DB2 stored procedures as REST APIs, with security configuration and performance tuning. → Chapter 35: Further Reading
more entries = fewer hash collisions - **Monitor LOCK_ENTRY_SHORTAGE and FALSE_CONTENTION counters** in IFCID 0230 and RMF reports - **Rebuild the lock structure** with a larger lock table if false contention is excessive → Chapter 28: Data Sharing and Parallel Sysplex — Multi-Member DB2 on z/OS
Contains the executable form of SQL packages. When you BIND a package, DB2 compiles the SQL statements and stores the access paths (the optimizer's chosen execution plans) in SPT01. At runtime, DB2 reads the skeleton package from SPT01, fills in any variable values, and executes the plan. SPT01 is t → Chapter 21: Catalog and Directory — Understanding DB2's Data About Your Data
The LUW counterpart to the z/OS reference. Note the differences in tablespace management, BOOLEAN support, and constraint behavior. - IBM Knowledge Center: https://www.ibm.com/docs/en/db2/11.5 → Chapter 11: Further Reading
SQL Reference for DB2 13 for z/OS
The authoritative reference for all DDL syntax on z/OS, including CREATE TABLE, CREATE TABLESPACE, CREATE INDEX, ALTER TABLE, and DROP statements. Bookmark the "SQL statements" section. - IBM Knowledge Center: https://www.ibm.com/docs/en/db2-for-zos → Chapter 11: Further Reading
Stack Overflow: ibm-db2 tag
Community Q&A for Db2 development, including cloud-specific questions. https://stackoverflow.com/questions/tagged/ibm-db2 → Chapter 31 Further Reading
Stale statistics
The table has grown or the data distribution has changed since RUNSTATS was last executed - **Correlated predicates** — The optimizer assumes predicates are independent, but in reality, knowing the value of one column tells you a lot about the value of another - **Skewed data** — If a column has a f → Chapter 23: Reading and Interpreting EXPLAIN — The Single Most Important DBA Skill
access paths, authorization, and validation are all predetermined. This eliminates runtime overhead and provides predictable performance, making it ideal for high-volume batch processing. → Chapter 32 Key Takeaways
M1 writes the commit log record to its local active log - M1 writes the dirty page P to the GBP (this is synchronous in SYNC mode) - The CF registers that page P has been updated by M1 - The CF sends a **cross-invalidation (XI) signal** to M2, because M2 has page P in its local buffer pool - M2 rece → Chapter 28: Data Sharing and Parallel Sysplex — Multi-Member DB2 on z/OS
Step 3: M2's transaction reads the row.
M2 checks its local buffer pool for page P — finds it marked invalid - M2 reads page P from the GBP (a GBP read, counted as a GBP hit) - M2 now has the current version of the row - M2 requests an L-lock (shared) on the row — granted because M1 has released its X lock → Chapter 28: Data Sharing and Parallel Sysplex — Multi-Member DB2 on z/OS
Dedicate a full 20--30 minutes to NULLs early in the course. Do not treat NULL handling as an aside. - Use a truth table on the whiteboard showing how AND, OR, and NOT behave with UNKNOWN. Have students predict results before revealing them. - Demonstrate the `NOT IN` trap with a live query. Show ho → Common Student Struggles with DB2
complete SELECT statements embedded inside other SELECT statements. A subquery computes one result, and the outer query uses that result as if it were a constant, a list, or an entire table. Once you internalize this pattern, the complexity ceiling of what you can express in a single SQL statement r → Chapter 8: Subqueries and Common Table Expressions — Queries Within Queries
Used when entities share common attributes but have type-specific attributes. Appropriate when the subtypes have meaningfully different attributes (not just one or two extra columns) and queries commonly need to access all types together. Implemented via table-per-type in the Meridian Bank model. → Chapter 13 Quiz: Logical Database Design
Find all tables using the same column name (potential join partners) 2. **SYSRELS / SYSCAT.REFERENCES** — Foreign keys that include the column 3. **SYSVIEWDEP / SYSCAT.VIEWDEP** — Views that reference the table containing the column 4. **SYSPACKDEP / SYSCAT.PACKAGEDEP** — Compiled packages (programs → Chapter 21 Quiz: Catalog and Directory
Maps each object (tablespace partition, index space) to the range of log records that contain changes to that object. When DB2 needs to recover an object — for instance, during RECOVER TABLESPACE — it consults SYSLGRNX to determine which log records to read. Without SYSLGRNX, recovery would require → Chapter 21: Catalog and Directory — Understanding DB2's Data About Your Data
GDPS halts Metro Mirror replication 2. **T+0:05** — GDPS reverses the mirror direction (Indianapolis volumes become primary) 3. **T+0:12** — GDPS initiates z/OS IPL at Indianapolis using the mirrored system volumes 4. **T+2:30** — z/OS is operational at Indianapolis 5. **T+3:00** — GDPS starts the D → Case Study 1: Disaster Recovery Drill — Simulating a Data Center Failure
Table cardinality and page count
via basic RUNSTATS (always collect this) 2. **COLCARD for all indexed columns** — essential for filter factor calculation 3. **Frequency values for skewed columns** — STATUS, TYPE, STATE, and similar categorical columns 4. **HIGH2KEY/LOW2KEY for range-queried columns** — DATE, AMOUNT, BALANCE column → Chapter 22: Key Takeaways
200 concurrent simulated ATM sessions - Each session: balance check, withdrawal, receipt generation - 5-second think time between operations - Target: 95th percentile response < 150 ms → Case Study 2: Memory Constraint Tuning on Limited Hardware
Test 3: Sustained Load (2 hours)
Continuous Test 2 workload - Monitor buffer pool hit ratios and response times - Hit ratios should stabilize within 20 minutes (cache warm-up) - No degradation trend after warm-up → Case Study 2: Memory Constraint Tuning on Limited Hardware
an approach where the database automatically estimates the cost of different execution strategies and chooses the most efficient one. → Chapter 1 Quiz: What Is IBM DB2?
The entity-relationship diagram
a wall-sized printout with all 32 entities and their relationships in Crow's foot notation. 2. **The data dictionary** — a spreadsheet with every entity, every attribute, data types, domains, and business rules. 3. **Scenario walkthroughs** — you trace five common business processes through the mode → Case Study 1: Meridian Bank Complete Data Model
for example, a function applied to the indexed column (`WHERE YEAR(TRANS_DATE) = 2025`), a LIKE pattern with a leading wildcard (`WHERE CUST_NAME LIKE '%Smith'`), or an OR condition that cannot be converted to an IN list - **Statistics are stale or missing**, causing the optimizer to underestimate t → Chapter 23: Reading and Interpreting EXPLAIN — The Single Most Important DBA Skill
Thread Management:
**CTHREAD** (Connected Threads): The maximum number of allied threads. This is your connection limit. If all CTHREAD slots are full, new connection attempts are rejected. Set it based on your peak concurrent user count plus a safety margin. - **MAXDBAT** (Maximum Database Access Threads): The maximu → Chapter 4: Setting Up Your DB2 Environment -- Installation, Configuration, and Your First Database
Three temporal models serve three purposes:
System-time: automatic audit trail (when the database changed) - Business-time: application-managed validity periods (when facts are effective) - Bitemporal: both dimensions (the regulatory gold standard) → Chapter 10 Key Takeaways
Pure Java — no native libraries required. - Communicates directly via DRDA over TCP/IP. - Works identically on any JVM platform (Linux, Windows, z/OS client). - Simpler deployment — just add the JAR to your classpath. - The standard choice for virtually all new development. - Essential for container → Chapter 33: JDBC, ODBC, and Python Access — Connecting Modern Applications to DB2
Type 4 vs. Type 2 Drivers:
**Type 4 (pure Java):** Communicates directly with the Db2 server over TCP/IP. No local Db2 installation required. This is what DBeaver and most modern applications use. - **Type 2 (native CLI):** Uses the local Db2 client libraries. Requires a Db2 client installation. Offers slightly better perform → Chapter 4: Setting Up Your DB2 Environment -- Installation, Configuration, and Your First Database
Write SELECT queries with filtering, sorting, and aliasing. - Join two or more tables using inner, outer, and self-joins. - Write correlated subqueries and recursive CTEs. - Use aggregate functions with GROUP BY, HAVING, ROLLUP, and CUBE. - Apply window functions for ranking, running totals, and mov → Self-Paced Learning Guide
When STMM works well:
Workloads that change throughout the day (heavy sorts in the morning, heavy OLTP in the afternoon) - New systems where the DBA does not yet know the optimal memory distribution - Systems where the DBA does not have time for manual tuning → Chapter 25: Buffer Pool and Memory Tuning — Giving DB2 the Right Resources
When to be cautious with STMM:
Critical OLTP pools where you need a guaranteed minimum. STMM might shrink a pool during a low-activity period, causing a cold cache when activity resumes. - Very large systems (hundreds of GB) where STMM's periodic adjustments may cause measurable disruption. → Chapter 25: Buffer Pool and Memory Tuning — Giving DB2 the Right Resources
**Type safety is gone.** Every value is a string. DATE_OF_BIRTH and BALANCE are stored as VARCHAR. No CHECK constraints, no data type validation. - **Queries are hideous.** To reconstruct a single customer record, you need to PIVOT (or write one self-JOIN per attribute). A five-column query becomes → Chapter 13: Logical Database Design — From Business Requirements to Entity-Relationship Models
Why it is problematic:
**Foreign keys cannot validate.** You cannot create a FK from ACCOUNT.ACCOUNT_TYPE to CODES where CODE_TYPE = 'ACCT_TYPE'. DB2 foreign keys reference primary keys or unique constraints — they do not support conditional references. - **Each code type has different attributes.** Country codes need a c → Chapter 13: Logical Database Design — From Business Requirements to Entity-Relationship Models
Add the three new columns (MASTER_CUSTOMER_ID, IDENTITY_VERIFIED_FLAG, IDENTITY_VERIFIED_DATE) - Add the new foreign key to MASTER.MASTER_CUSTOMER - These changes are additive and low-risk; existing code continues to work unchanged → Case Study 2: Impact Analysis Using the Catalog Before a Major Change
Window 2 (Week 2):
Modify EMAIL_ADDRESS from CHAR(50) to VARCHAR(254) - Drop LEGACY_SYSTEM_CODE - Prerequisite: the 4 packages and 3 views referencing LEGACY_SYSTEM_CODE must be modified and recompiled before the window - During the window: ALTER TABLE, REBUILD IX_CUST_EMAIL, REBIND 187 packages - Estimated time: 4 ho → Case Study 2: Impact Analysis Using the Catalog Before a Major Change
Windows-Specific Considerations:
Db2 on Windows runs as a Windows service. Use `db2start` and `db2stop` or the Windows Services panel to control it. - The DB2 Command Window sets required environment variables automatically. If you use a plain Command Prompt or PowerShell, run `db2cmd` first or source the DB2 profile manually. - Fi → Chapter 4: Setting Up Your DB2 Environment -- Installation, Configuration, and Your First Database
IBM Knowledge Center: Complete reference for z/OS Connect Enterprise Edition including API Toolkit usage, service archive creation, Liberty server configuration, and security setup. - https://www.ibm.com/docs/en/zos-connect → Chapter 35: Further Reading
no planned or unplanned outages - **Read/write scalability is needed** — multiple active members serve the workload - **The budget supports it** — pureScale requires multiple servers, InfiniBand networking, shared storage, and IBM licensing for each member → Chapter 29: HADR and Replication — High Availability for DB2 LUW