Optimizing SQL Queries on DB2 for i

A query that came back in two seconds yesterday and takes forty today is one of the most frequent phone calls. The temptation is to add indexes at random until something improves. On Db2 for i there is no need: the system can tell you exactly what it does not like, you only have to ask.

First: look at what it is actually doing

The starting point is Visual Explain, inside Run SQL Scripts in IBM i Access Client Solutions. Write the query, press Ctrl+Shift+E instead of running it, and the access plan appears as a diagram: which files are read, in what order, and above all how.

There is one detail to look for straight away: a box labelled Table Scan. It means the database is reading the file from beginning to end. On a ten-thousand-row table that is not a problem. On a ten-million-row one, it is the whole explanation.

The index advisor already keeps the list

Db2 for i records on its own, continuously, which indexes it would have liked. The information sits in a system view you can query like any other table:

SELECT TABLE_NAME,
       KEY_COLUMNS_ADVISED,
       TIMES_ADVISED,
       LAST_ADVISED
  FROM QSYS2.SYSIXADV
 WHERE TABLE_SCHEMA = 'MYLIB'
 ORDER BY TIMES_ADVISED DESC
 FETCH FIRST 20 ROWS ONLY;

TIMES_ADVISED is the column to watch: it says how many times the system wished for that index. An index advised once may have been a one-off query; one advised four thousand times is slowing down something that runs every day.

Be careful not to create everything on that list, though. Every index has to be maintained on each INSERT, UPDATE and DELETE: twenty indexes on a heavily updated file can do more damage than the problem they solve. Start from the top three by number of requests and measure again.

Conditions that stop the index being used

Even with the right index, the database can be forced to ignore it. That happens when the indexed column is wrapped inside a function:

-- The index on ORDDATE is not used:
-- the function has to be applied to every row
WHERE YEAR(ORDDATE) = 2026

-- Rewritten this way, the index becomes usable again
WHERE ORDDATE >= '2026-01-01'
  AND ORDDATE <  '2027-01-01'

The same goes for UPPER(NAME) = 'ROSSI', for SUBSTR(CODE, 1, 3) = 'ABC' (which can be rewritten as CODE LIKE 'ABC%'), and for comparisons between different types, where the database has to convert a column before it can compare it.

A case you meet often on IBM i is comparing a numeric field with an alphanumeric constant, typical of legacy DDS files where codes are numeric. If CUSTNO is packed(7:0), writing WHERE CUSTNO = '0001234' forces a conversion row by row. It has to be compared with a number: WHERE CUSTNO = 1234.

Measure instead of remembering

Impressions mislead, mainly because the second run of a query is almost always faster than the first: the data is already in memory. For reliable numbers you need the database monitor:

STRDBMON OUTFILE(MYLIB/MONITOR) JOB(*ALL)

Leave it on for a representative window — half an hour of real work is already telling — then stop it with ENDDBMON. What is left is an ordinary table you can query in SQL: every row is an event, and the records with QQRID = 3000 hold the timings of individual queries.

The monitor has a performance cost, so turn it on when you need it and off straight after. On a very busy system it is worth limiting it to a single job rather than *ALL.

A working order that gets results

1. Reproduce the slowdown and take the starting time.
2. Visual Explain on the offending query: look for Table Scans.
3. Check SYSIXADV for that table.
4. Verify that no condition applies a function to the filtered columns.
5. Create one index at a time and measure again.
6. After a few weeks, check that the indexes you created are actually being used: the QSYS2.SYSTABLEINDEXSTAT view reports how many queries have taken advantage of them.

Step 6 is the one always skipped, and it is the one that stops you ending up, two years from now, with forty indexes nobody knows anything about.

A note on the two engines

Db2 for i has two optimisers: the older CQE and the far more capable SQE. Some constructs push a query back onto the CQE, and there most of the advice above matters less. The exact conditions change from release to release, so it is worth checking them against the documentation for your own version: Visual Explain always states at the top of the plan which engine was used.

Which versions this works on

Index advisor and the QSYS2/SYSIXADV table — documented at least from IBM i 7.3, and present in the 7.4 documentation and later. The advisor collects data on its own, with nothing to switch on.

QSYS2.INDEX_ADVICE — a procedure that condenses duplicate advice into a view in QTEMP. It came later than the raw table: if it does not exist on your release, query SYSIXADV directly as shown above, which works everywhere.

Visual Explain — today it lives in Run SQL Scripts in IBM i Access Client Solutions. Before ACS the same function was in System i Navigator (and before that in Operations Navigator): the diagram is the same, only the program you open it from has changed.

STRDBMON — a long-standing CL command, present for many releases.

The exact PTF level at which each database function was enabled is published by IBM on the Db2 for i – Technology Updates page, listed in the sources. That is the right place to check what is available on your own machine.

Sources

• Index advisor — IBM Documentation 7.4
• Displaying index advisor information — IBM Documentation 7.4
• QSYS2.INDEX_ADVICE procedure — IBM Support
• Db2 for i — Technology Updates (PTF levels for each function)