DB2 for z/OS Access Paths: When to Leverage Hash Access vs Traditional Index Scans

High-frequency OLTP transactions querying customer or account tables by primary key consume excessive CPU:

DSNX001I SQL SELECT ELAPSED TIME: 1.2MS (3 INDEX LEVELS TRAVERSED)
TRANSACTION VOLUME: 45,000 TRANSACTIONS / SEC
PEAK 4HRA CPU UTILIZATION: 94% ON GENERAL PROCESSORS

At scale, traversing 3 to 4 B-tree index levels for every single row query creates significant CPU overhead.

Standard DB2 tables utilize B-tree indexes where the optimizer reads root, intermediate, and leaf index pages before reading the actual data page (3-4 getpage requests per query). Hash access calculates an algorithmic page offset directly from the hash key, reducing the operation to a single getpage for matching rows.

# 1. Define Hash Organization on Tablespace

ALTER TABLESPACE DBNAME.CUSTTS
  ORGANIZATION HASH UNIQUE (CUST_ID)
  HASH SPACE 500M;

# 2. Verify Access Path in EXPLAIN PLAN_TABLE

Query the DB2 catalog plan table for your queries:

SELECT QUERYNO, ACCESSTYPE, MATCHCOLS, ACCESSNAME
FROM PLAN_TABLE
WHERE QUERYNO = 101;

• ACCESSTYPE = 'H': Hash access successfully chosen by the optimizer!
• Getpage count drops from 4 to 1.

# 3. When NOT to Use Hash Access

• Tables frequently subjected to range queries (WHERE CUST_ID BETWEEN 1000 AND 2000). Hash access does not support range scans.
• Tables with unpredictable, explosive growth beyond the defined HASH SPACE.
• Tables where mass inserts cause excessive hash collision overflow records.