S/MF
StackMF Knowledge Base • z/OS
DB2 SQL & Performance 10 min read 2026-09-27

DB2 z/OS Query Optimization: Converting Stage 2 (Residual) into Stage 1 (Indexable) Predicates

A
Reviewed by Anshu
Chief Technology Architect & Mainframe Systems SME
Enterprise Modernization Advisory
Google AI Overview & Featured Snippet Quick Answer

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:

sql
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:

sql
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:

sql
-- 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:

sql
-- 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 be PIC 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

? Frequently Asked Questions

What is the root cause of DB2 z/OS Query Optimization: Converting Stage 2 (Residual) into Stage 1 (Indexable) Predicates? ↓
DB2 executes SQL through a two-tiered processing pipeline:.
How do you resolve DB2 z/OS Query Optimization: Converting Stage 2 (Residual) into Stage 1 (Indexable) Predicates in production? ↓
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.
How can teams prevent DB2 z/OS Query Optimization: Converting Stage 2 (Residual) into Stage 1 (Indexable) Predicates in enterprise pipelines? ↓
Integrate DB2 EXPLAIN checks into automated Endevor/Git pull request pipelines.

Recommended Technical Runbooks

Authoritative Reference Documentation

Official IBM manuals, Redbooks, and vendor technical advisories:

Related Topics: DB2SQL TuningEXPLAINStage 1Stage 2PLAN_TABLE
Diagnostic Runbook Notice & Nominative Fair Use Disclaimer

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 →

Enterprise Advisory

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%.