DB2 for i Query Optimization: Visual Explain in ACS, SQE vs CQE, Index Design, Encoded Vector Indexes, STRDBMON, and Reading the SQL Plan Cache on IBM i in 2026

By /

The previous post covered CL error handling and exception management — IBM i message types, MONMSG command patterns for program-level and command-level monitoring, program message queues, SNDPGMMSG and RCVMSG, condition handlers in free-format CL procedures, DMPJOB diagnostic dumps, and a complete production CL error-handling template. This post covers DB2 for i query optimization: the SQE and CQE query engines, Visual Explain in IBM i Access Client Solutions, index design strategies, encoded vector indexes, STRDBMON, and reading the SQL plan cache using QSYS2 catalog views to diagnose and resolve slow queries on IBM i in 2026.

SQE vs CQE — Two Query Engines

DB2 for i has two query engines that have coexisted since IBM i V5R2:

  • SQE (SQL Query Engine) — the modern engine introduced in V5R2. SQE processes SQL queries, uses a cost-based optimizer, maintains a plan cache, and leverages statistics for better plan selection. All new queries should be processed by SQE.
  • CQE (Classic Query Engine) — the original query engine, predating modern SQL on IBM i. CQE processes queries over physical files using OPNQRYF-style access, DDS logical files, and some legacy SQL patterns. CQE does not maintain a plan cache and does not use statistics the same way SQE does.

A query falls back to CQE when it uses constructs that SQE cannot process: certain old-style OPNQRYF-like syntax, some DDS join logical files, or when the SQE optimizer encounters a CQE-only feature. IBM has steadily expanded SQE coverage with each OS release. On IBM i 7.5 in 2026, almost all SQL queries run through SQE unless they involve very old DDS constructs.

To check which engine processed a query, use the plan cache or Visual Explain — the query engine is shown in the plan details. The QAQQINI query options file can influence engine selection (for example the IGNORE_DERIVED_INDEX option), though this is rarely needed on modern IBM i releases.

Visual Explain in ACS

IBM i Access Client Solutions (ACS) includes a Visual Explain tool that graphically displays the query access plan chosen by the DB2 optimizer. It shows every step the optimizer took: which indexes were used, how tables were joined, whether a hash join or nested loop was used, the estimated and actual row counts, and where the optimizer spent the most time.

To use Visual Explain in ACS:

  1. Open ACS → Run SQL Scripts
  2. Type or paste the SQL statement
  3. Click Visual Explain (the toolbar icon showing a magnifying glass over a query plan, or use View → Run with Visual Explain)
  4. The explain runs the query and displays the plan graphically

Key elements in a Visual Explain output to examine:

  • Index scan vs table scan — a table scan on a large table is almost always the first thing to address. Look for nodes labelled “Table Scan” with high row estimates.
  • Index selection — which index was selected, and was it the best available? Look for “Index Scan” nodes and note the index name and estimated rows.
  • Join method — Hash Join is generally efficient for large equi-joins; Nested Loop Join is efficient when the inner table access is by key. Merge Scan Join (sort-merge) indicates that both sides are sorted first.
  • Temporary result — a “Temp Table” node indicates the optimizer created a temporary copy of data for sorting, grouping, or joining. This is expensive on large tables.
  • Estimated vs actual rows — a large discrepancy (e.g., optimizer estimated 100 rows but found 1,000,000) indicates stale statistics.

Index Design Principles for DB2 for i

The DB2 for i optimizer selects access paths from available indexes (keyed logical files, SQL CREATE INDEX indexes, and system-maintained access paths). Effective index design for IBM i follows these principles:

Radix (binary tree) indexes are created with CREATE INDEX or via keyed DDS logical files. They are best for equality and range predicates on high-cardinality columns (columns with many distinct values):

-- Create an index on CUSTMST for customer status + account balance (compound)
CREATE INDEX APPLIB.IX_CUST_STS_BAL
  ON APPLIB.CUSTMST (CUSSTS ASC, ACCTBAL DESC);

-- Create an index supporting a common WHERE + ORDER BY pattern
CREATE INDEX APPLIB.IX_ORD_CUSTDT
  ON APPLIB.ORDMST (CUSNO ASC, ORDDAT DESC, ORDAMT, ORDSTS);
-- Trailing key columns can let the index cover the SELECT list. INCLUDE is accepted only on UNIQUE indexes.

Index guidelines:

  • Put the most selective column (fewest duplicate values) first in a compound index for equality predicates
  • Put the range or ORDER BY column last in a compound index
  • Add trailing key columns (or INCLUDE columns on a unique index) to create covering indexes that avoid base table reads for common SELECT patterns
  • Do not index low-cardinality columns (e.g., a status column with only 3 values) with a radix index — use an EVI instead
  • Every foreign key should have a supporting index to avoid full table scans on join

Encoded Vector Indexes (EVI)

An Encoded Vector Index (EVI) is a bitmap-style index designed for low-cardinality columns — columns where the number of distinct values is small relative to the total row count (e.g., status codes, region codes, boolean flags). EVIs are extremely efficient for queries that filter on multiple low-cardinality columns simultaneously (AND/OR combinations), and for aggregation queries (GROUP BY, COUNT, SUM).

-- Create an EVI on a low-cardinality status column
CREATE ENCODED VECTOR INDEX APPLIB.EVI_CUST_STS
  ON APPLIB.CUSTMST (CUSSTS);

-- Create an EVI on order status (5 possible values)
CREATE ENCODED VECTOR INDEX APPLIB.EVI_ORD_STS
  ON APPLIB.ORDMST (ORDSTS);

-- Create an EVI on region code
CREATE ENCODED VECTOR INDEX APPLIB.EVI_ORD_RGN
  ON APPLIB.ORDMST (REGION);

-- A query filtering on both STATUS and REGION will use both EVIs
-- and combine them via bitmap AND — extremely fast for large tables
SELECT COUNT(*), SUM(ORDAMT)
FROM   APPLIB.ORDMST
WHERE  ORDSTS = 'SH'        -- EVI_ORD_STS used
AND    REGION = 'MIDWEST';  -- EVI_ORD_RGN used, then AND-combined

When to use EVI vs radix index:

  • Use EVI for columns with fewer than ~100 distinct values (status, type, region, boolean flags)
  • Use radix index for high-cardinality columns (customer number, order number, date, name)
  • Both can coexist on the same table — the optimizer picks based on the query predicates

STRDBMON — Database Monitor

STRDBMON (Start Database Monitor) captures detailed optimizer information for queries that run while monitoring is active. The data is written to a DB2 for i table that you specify. This is the most powerful tool for identifying slow queries in a running application.

/* Start monitoring — capture query plans for the next 30 minutes */
STRDBMON OUTFILE(QTEMP/DBMON) JOB(*ALL) COMMENT('Performance investigation')

/* ... run the application workload ... */

/* End monitoring */
ENDDBMON JOB(*ALL)

/* Analyze the results using ACS Visual Explain on the monitor output */
/* Or query the monitor table directly */

Query the STRDBMON output table to find the slowest queries:

-- Find the top 20 slowest queries from a STRDBMON capture
SELECT
    QQJFLD  AS job_number,
    QQUCNT  AS execution_count,
    QQETIM  AS elapsed_time_ms,
    QQPTIM  AS parse_time_ms,
    QQROWS  AS rows_fetched,
    LEFT(QQSTMT, 200) AS sql_statement
FROM QTEMP.DBMON
WHERE QQRID = 3000           -- RID 3000 = SQL statement open
ORDER BY QQETIM DESC
FETCH FIRST 20 ROWS ONLY;

Reading the SQL Plan Cache

DB2 for i SQE maintains a plan cache — a shared memory area that stores compiled query plans. When the same query runs again, SQE can reuse the cached plan rather than re-optimizing. The plan cache is examined through ACS SQL Performance Center snapshots and database monitor data:

Use the SQL Performance Center in ACS to take a plan cache snapshot or to work with database monitor data, then sort the statements by total or average run time and open the worst ones in Visual Explain. There are no QSYS2.SQL_PLAN_CACHE or QSYS2.SQL_PLAN_CACHE_STMT views with the columns shown in earlier versions of this post.

Optimizer Goals and OPTIMIZE FOR

DB2 for i’s optimizer defaults to minimizing total elapsed time for the full result set. For interactive queries that display only the first few rows, it can be more efficient to optimize for first-row retrieval:

-- Default: optimize for fetching all rows (best for batch)
SELECT CUSNO, CUSNM, ACCTBAL
FROM   APPLIB.CUSTMST
WHERE  CUSSTS = 'A'
ORDER BY ACCTBAL DESC;

-- OPTIMIZE FOR first 10 rows (best for interactive paging)
SELECT CUSNO, CUSNM, ACCTBAL
FROM   APPLIB.CUSTMST
WHERE  CUSSTS = 'A'
ORDER BY ACCTBAL DESC
OPTIMIZE FOR 10 ROWS;

-- Let the optimizer choose join order unless you have a specific, measured problem

Collecting and Refreshing Statistics

The SQE optimizer uses column statistics (histograms of value distribution) to estimate row counts and choose optimal plans. If statistics are stale or missing, the optimizer makes poor decisions — the most common cause of suddenly slow queries after a large data load.

-- Look at the column statistics the optimizer holds for a table
SELECT *
FROM   QSYS2.SYSCOLUMNSTAT
WHERE  TABLE_SCHEMA = 'APPLIB'
  AND  TABLE_NAME   = 'CUSTMST';

Statistics are normally collected in the background by the system job QDBFSTCCOL (controlled by the system value of the same name), so a scheduled collection is rarely needed. For tables that receive large periodic loads (nightly batch inserts or replacements), check the column statistics and the plans after the load before the next day’s interactive workload begins, and request collection in ACS if they look stale.

Next post: OAuth 2.0 and JWT Authentication from IBM i — implementing the client credentials flow from ILE RPG, managing Bearer tokens in DB2 for i, calling OAuth-protected REST APIs, JWT structure, and managing SSL certificates in the IBM i digital certificate store.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top