DB2 z/OS Query Optimization: Converting Stage 2 (Residual) into Stage 1 (Indexable) Predicates
Executive Summary: DB2 z/OS Query Optimization: Converting Stage 2 (Residual) into Stage 1 (Indexable) Predicates
In DB2 for z/OS, Stage 1 (sargable) predicates are evaluated inside the Data Manager (DM) at near-hardware speeds, while Stage 2 (residual) predicates require transferring rows to the Relational Data System (RDS), burning massive CPU. Learn how to refactor SQL for Stage 1 execution.
1 Problem Statement & Architecture Context: DB2 z/OS Query Optimization: Converting Stage 2 (Residual) into Stage 1 (Indexable) Predicates
A core query scanning a 50-million-row customer table consumes 45 CPU seconds per execution:
SELECT CUST_ID, CUST_NAME, CURRENT_BALANCE
FROM CUSTOMER_TABLE
WHERE CUST_STATUS || CUST_REGION = 'A' || 'EAST'
AND YEAR(LAST_LOGIN_DATE) = 2026;
When running DB2 EXPLAIN, the developer sees STAGE='2' in DSN_PREDICAT_TABLE. The query causes massive I/O buffer pool churn and inflates the enterprise 4HRA MIPS billing peak.
2 Technical Root Cause & Architecture Mechanics
DB2 executes SQL through a two-tiered processing pipeline:
1. Stage 1 (Data Manager / DM / Sargable): Evaluated directly as rows are read from disk or buffer pools. Non-qualifying rows are discarded immediately without CPU-intensive context switching.
2. Stage 2 (Relational Data System / RDS / Residual): Rows passing Stage 1 must be packaged into internal structures and passed across internal component boundaries to RDS for evaluation.
When developers wrap columns in scalar functions (YEAR(COL), SUBSTR(COL), COL1 || COL2), use mismatched datatypes (e.g., comparing VARCHAR to INTEGER), or use expressions on indexable columns, the DB2 query optimizer cannot use an index and is forced to push evaluation into Stage 2.
3 Implementation Guide & Modernization Strategy
# 1. Inspect DSN_PREDICAT_TABLE via EXPLAIN
Run EXPLAIN ALL SET QUERYNO = 101 FOR ... and check predicate stages:
SELECT PREDNO, STAGE, TYPE, PREDICATE_TEXT
FROM DSN_PREDICAT_TABLE
WHERE QUERYNO = 101;
Look for any predicate where STAGE = '2'.
# 2. Refactor Scalar Functions into Range Predicates
Avoid function wrappers on columns in the WHERE clause:
-- SLOW (Stage 2 - Full scan of all rows):
WHERE YEAR(LAST_LOGIN_DATE) = 2026
-- FAST (Stage 1 / Indexable Range Search):
WHERE LAST_LOGIN_DATE >= '2026-01-01-00.00.00.000000'
AND LAST_LOGIN_DATE <= '2026-12-31-23.59.59.999999'
# 3. Separate Concatenated Columns
Never concatenate columns on the left-hand side of a comparison:
-- SLOW (Stage 2):
WHERE CUST_STATUS || CUST_REGION = 'AEAST'
-- FAST (Stage 1 - Indexable composite match):
WHERE CUST_STATUS = 'A'
AND CUST_REGION = 'EAST'
# 4. Match Host Variable Datatypes Exactly
In COBOL programs, ensure host variables match the DB2 catalog column definition:
- If column is
DECIMAL(9,2), host variable must bePIC S9(7)V99 COMP-3. - If host variable is defined as
PIC X(10)or integer, DB2 must perform type conversion at runtime, degrading the predicate to Stage 2.
Technical Specifications Matrix
| Attribute | Specification |
|---|---|
| Target Environment | IBM z/OS • DB2 SQL & Performance |
| Key Technologies | DB2, SQL Tuning, EXPLAIN |
| Delivery SLA | Accelerated Sprint Delivery via StackMF Pods |
| Technical Reviewer | Anshu, Chief Technology Architect • StackMF Architecture Pod |
Production Readiness & Verification Checklist
- Integrate DB2 EXPLAIN checks into automated Endevor/Git pull request pipelines.
- Never apply scalar functions to indexed columns; rewrite as explicit range boundaries.
- Ensure COBOL host variable datatypes exactly replicate DB2 DCLGEN definitions.
- Create generated columns or expression-based indexes (`CREATE INDEX ... ON TABLE (YEAR(DATE_COL))`) if SQL cannot be refactored.
? Frequently Asked Questions
What is the root cause of DB2 z/OS Query Optimization: Converting Stage 2 (Residual) into Stage 1 (Indexable) Predicates? ↓
How do you resolve DB2 z/OS Query Optimization: Converting Stage 2 (Residual) into Stage 1 (Indexable) Predicates in production? ↓
How can teams prevent DB2 z/OS Query Optimization: Converting Stage 2 (Residual) into Stage 1 (Indexable) Predicates in enterprise pipelines? ↓
Recommended Technical Runbooks
Resolving DB2 SQLCODE -911 (Reason 00C90088 / Deadlock) and -904 Resource Unavailable
How Outdated RUNSTATS Cause Catastrophic Tablespace Scans in DB2 for z/OS
Preventing DB2 Lock Escalation: Tuning LOCKSIZE, Isolation Levels, and SKIP LOCKED DATA
Authoritative Reference Documentation
Official IBM manuals, Redbooks, and vendor technical advisories:
This diagnostic runbook is published by StackMF Technologies LLP for educational and architectural reference only. All code snippets, JCL, and procedures are provided "AS IS" without warranty of any kind. Always test changes thoroughly in non-production sysplex environments prior to production rollout.
IBM, z/OS, CICS, Db2, IMS, RACF, and IDz are registered trademarks of International Business Machines Corporation. Broadcom, CA-7, and Endevor are trademarks of Broadcom Inc. All other trademarks belong to their respective owners and are referenced under the Nominative Fair Use Doctrine (US Lanham Act 15 U.S.C. ยง 1125 / Section 30 of the Indian Trade Marks Act, 1999) solely for technology compatibility and diagnostic identification. StackMF Technologies LLP is an independent consulting entity not affiliated with or endorsed by these vendors. View Full Legal & IP Policy →
Struggling with Critical Mainframe Incidents or Vendor Renewal Pressure?
StackMF deploys certified Senior Mainframe Engineers fluent in both z/OS legacy internals (COBOL, DB2, CICS, VSAM, CA-7, Endevor) and modern cloud stacks (React, Kafka, AWS, Git). Onboard dedicated pods in 48 hours or cut Broadcom licensing by 60%.