DB2 for i Advanced SQL in 2026: SQE vs CQE, Index Advisor, Column Statistics, Materialized Query Tables, and Query Performance Patterns

By /

The previous post covered IBM i performance tuning — memory pools, WRKACTJOB, Collection Services, and diagnosing slow batch jobs. This post goes deeper into the query layer: how DB2 for i decides how to run a query, what the Index Advisor and Plan Cache tell you, when to use Materialized Query Tables, and the SQL patterns that consistently cause performance problems on IBM i.

SQE and CQE: two query engines in one database

DB2 for i contains two query engines: the SQL Query Engine (SQE) and the Classic Query Engine (CQE). Understanding which one processes your query determines which tools you use to tune it.

SQE (SQL Query Engine) — the modern engine, introduced with DB2 UDB for iSeries (V5R2). Handles all SQL statements submitted through the SQL interfaces: embedded SQL in RPG, JDBC, ODBC, DRDA, interactive SQL (STRSQL/ACS). Uses a cost-based optimiser with column statistics, bitmap indexes, hash joins, and dynamic index creation. The plan cache (SYSQRYSLT) captures SQE plans.

CQE (Classic Query Engine) — the legacy engine, which predates SQL on IBM i. Handles OPNQRYF (Open Query File), Query/400 reports, and some specific SQL constructs that SQE does not yet support. CQE uses simpler access plans and does not populate the plan cache.

Identifying which engine processed a query:

The engine that ran a statement is shown in Visual Explain, in plan cache snapshots and in database monitor data (STRDBMON). There is no QSYS2.SYSQRYSLT catalog view to query for it.

If a query is running in CQE when you expect SQE, the most common reasons are: the query contains OPNQRYF-style parameter syntax, it uses a feature not yet supported by SQE (some specific join types or legacy DDS file formats), or the query was submitted through a non-SQL interface.

Column statistics and the optimiser

The SQE cost-based optimiser builds access plans based on statistics. Without statistics, it estimates based on file size and key structure alone. With column-level statistics, it makes much better decisions about join order, index selection, and whether a table scan is cheaper than a keyed read.

Checking when statistics were last collected:

SELECT *
FROM   QSYS2.SYSCOLUMNSTAT
WHERE  TABLE_SCHEMA = 'ORDLIB'
  AND  TABLE_NAME   = 'ORDHDR';

Collecting statistics manually:

There is no QSYS2.COLLECT_COLUMN_STATISTICS procedure. Statistics are normally collected automatically in the background by the system job QDBFSTCCOL, and you can request collection for specific columns in ACS (the table’s Statistics tab) or through the Db2 for i statistics APIs.

Automatic statistics maintenance: background statistics collection is controlled by the system value QDBFSTCCOL (Allow background database statistics collection), and the default setting allows both user-requested and system-generated requests. Check it with:

DSPSYSVAL SYSVAL(QDBFSTCCOL)

For production systems with tables that change significantly day-to-day (order processing, inventory), leaving background statistics collection enabled keeps the optimiser from relying on stale statistics.

The Index Advisor

The Index Advisor (SYSIXADV) records indexes that the optimiser would have liked to use but that did not exist. It is populated by both SQE and CQE. Checking it regularly and creating the advised indexes is the highest-ROI query tuning activity for most IBM i shops.

-- Most frequently advised missing indexes
SELECT SYSTEM_TABLE_SCHEMA,
       SYSTEM_TABLE_NAME,
       TIMES_ADVISED,
       LAST_ADVISED,
       KEY_COLUMNS_ADVISED,
       MTI_USED,            -- Y = a temporary index was built instead
       MTI_CREATED
FROM   QSYS2.SYSIXADV
WHERE  SYSTEM_TABLE_SCHEMA = 'ORDLIB'
ORDER  BY TIMES_ADVISED DESC
FETCH FIRST 20 ROWS ONLY;

MTI_USED = Y means the optimiser built a temporary index (Managed Temporary Index) at query time because a permanent index did not exist. This is expensive — building a temporary index takes time and storage. A query that builds an MTI every time it runs is a strong candidate for a permanent index.

Creating the advised index:

-- Example: Index Advisor shows ORDSTS, ORDDAT frequently needed together
CREATE INDEX ORDLIB.ORDHDR_STS_DAT_IDX
    ON ORDLIB.ORDHDR (ORDSTS ASC, ORDDAT DESC);

After creating the index, the optimiser picks it up automatically — no recompilation needed.

Visual Explain

Visual Explain in ACS (IBM i Access Client Solutions) renders the SQE access plan as a diagram. Each node shows the operation (table scan, index scan, hash join, sort) and estimated cost. Use it to answer: why is this query slow?

How to use Visual Explain:

  1. Open ACS → Run SQL Scripts
  2. Type or paste the SQL statement
  3. Right-click → Visual Explain (or press F13)
  4. Read the plan from bottom to top — leaves are the base table accesses, the root is the final result

Red flags in a Visual Explain plan:

  • Table scan on a large table — the optimizer chose not to use any index. Check why: missing index, statistics stale, predicate not sargable (not index-usable)
  • Temporary result — a sort or grouping operation required materialising an intermediate result set to disk
  • CQE label instead of SQE — the query fell back to the classic engine; investigate why
  • High estimated rows at an early join stage — the join order may be wrong; statistics may be stale

Materialized Query Tables (MQTs)

A Materialized Query Table is a table whose content is defined by a query and physically stored. It is the IBM i equivalent of a materialized view. The query optimiser can transparently substitute an MQT for a more expensive query — even in queries that do not explicitly reference the MQT.

When to use MQTs:

  • Aggregations that are computed repeatedly (daily sales totals, regional summaries)
  • Expensive joins between large tables that are queried frequently with the same join pattern
  • Analytics queries against OLTP tables where real-time freshness is not required

Creating an MQT for open order summaries by customer:

CREATE TABLE ORDLIB.MQT_CUST_OPEN_ORDERS AS (
    SELECT H.CUSTNUM,
           C.CUSTNAME,
           COUNT(*)         AS OPEN_ORDER_CNT,
           SUM(H.ORDAMT)    AS TOTAL_OPEN_AMT,
           MAX(H.ORDDAT)    AS LATEST_ORDER_DATE
    FROM   ORDLIB.ORDHDR   H
    JOIN   ORDLIB.CUSTOMER  C ON C.CUSTNUM = H.CUSTNUM
    WHERE  H.ORDSTS = 'OP'
    GROUP  BY H.CUSTNUM, C.CUSTNAME
)
DATA INITIALLY DEFERRED
REFRESH DEFERRED
MAINTAINED BY USER
ENABLE QUERY OPTIMIZATION;

Populating the MQT:

REFRESH TABLE ORDLIB.MQT_CUST_OPEN_ORDERS;

How the optimiser uses MQTs: Once the MQT is populated and enabled for query optimisation, a query like:

SELECT CUSTNUM, SUM(ORDAMT) AS TOTAL
FROM   ORDLIB.ORDHDR
WHERE  ORDSTS = 'OP'
GROUP  BY CUSTNUM;

…may be transparently answered from the MQT rather than scanning ORDHDR. Visual Explain will show “MQT rewrite” in the plan when this happens.

Scheduling MQT refreshes in CL:

/* Refresh MQT as part of nightly batch */
RUNSQL SQL('REFRESH TABLE ORDLIB.MQT_CUST_OPEN_ORDERS') COMMIT(*NONE)

Encoded vector indexes for analytics queries

Db2 for i supports encoded vector indexes (EVIs) for low-cardinality columns. An EVI is efficient for columns with few distinct values (status codes, region codes, flags) when combined with other predicates in analytics queries.

-- Encoded vector index on order status (few distinct values: OP, CL, HL, CN)
CREATE ENCODED VECTOR INDEX ORDLIB.ORDHDR_STS_EVI
    ON ORDLIB.ORDHDR (ORDSTS);

The optimiser can combine multiple EVIs with AND/OR operations. A query filtering on both ORDSTS and REGION can use both EVIs together and then fetch only the qualifying rows, which often suits analytics patterns better than a single radix index.

SQL anti-patterns specific to DB2 for i

Non-sargable predicates — functions on indexed columns:

-- BAD: function applied to the indexed column; index on ORDDAT cannot be used
WHERE YEAR(ORDDAT) = 2026 AND MONTH(ORDDAT) = 5

-- GOOD: range predicate; index on ORDDAT IS used
WHERE ORDDAT BETWEEN DATE('2026-05-01') AND DATE('2026-05-31')

LIKE with a leading wildcard:

-- BAD: leading % forces a table scan regardless of index
WHERE CUSTNAME LIKE '%SMITH'

-- ACCEPTABLE: trailing wildcard can use an index
WHERE CUSTNAME LIKE 'SMITH%'

OR across multiple columns (use UNION ALL instead):

-- BAD for large tables: OR prevents efficient index use
WHERE ORDSTS = 'OP' OR ORDSTS = 'HL'

-- GOOD: IN clause is sargable
WHERE ORDSTS IN ('OP', 'HL')

-- Or UNION ALL for very different access patterns
SELECT * FROM ORDLIB.ORDHDR WHERE ORDSTS = 'OP'
UNION ALL
SELECT * FROM ORDLIB.ORDHDR WHERE ORDSTS = 'HL'

SELECT * in production queries:

-- BAD: retrieves all columns even if only 3 are needed; prevents index-only scans
SELECT * FROM ORDLIB.ORDHDR WHERE CUSTNUM = :custNum

-- GOOD: only retrieve needed columns; allows index-only access if index covers them
SELECT ORDNUM, ORDAMT, ORDDAT FROM ORDLIB.ORDHDR WHERE CUSTNUM = :custNum

Implicit conversions between character and numeric:

-- BAD: CUSTNUM is CHAR(10); comparing to an integer forces conversion, defeating the index
WHERE CUSTNUM = 12345

-- GOOD: match the data type of the column
WHERE CUSTNUM = '0000012345'

Plan cache analysis workflow

A structured approach to identifying and fixing query performance problems:

-- Step 1: Find the slowest statements: use the SQL Performance Center in ACS
--         (plan cache snapshot or database monitor) and sort by total run time.

-- Step 2: For each slow query, check if Index Advisor has a recommendation
SELECT TIMES_ADVISED, KEY_COLUMNS_ADVISED, MTI_USED
FROM   QSYS2.SYSIXADV
WHERE  SYSTEM_TABLE_SCHEMA = 'ORDLIB'
  AND  SYSTEM_TABLE_NAME   = 'ORDHDR'
ORDER  BY TIMES_ADVISED DESC;

-- Step 3: Check the column statistics for tables in slow queries
SELECT *
FROM   QSYS2.SYSCOLUMNSTAT
WHERE  TABLE_SCHEMA = 'ORDLIB'
  AND  TABLE_NAME   = 'ORDHDR';

-- Step 4: Run Visual Explain in ACS for the specific SQL statement
-- Step 5: Create recommended indexes or refresh statistics
-- Step 6: Re-run the statement and compare the new plan and run time

Query performance on DB2 for i is predictable and improvable. The Index Advisor tells you what indexes to create. Visual Explain tells you why an existing plan is suboptimal. MQTs let you pre-compute expensive aggregations. Statistics keep the optimiser accurate. Working through these tools systematically resolves the vast majority of query performance problems without requiring schema changes or application rewrites.

Next post: IBM i system operations with CL — subsystem management, job control commands, data queues for inter-job communication, output queue management, and the operational CL patterns that IBM i administrators use every day.

Leave a Comment

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

Scroll to Top