From Raw Plan Cache Records to Usable SQL Evidence

IBM i Plan Cache contains valuable information about SQL access plans, but the raw data is not written for a human reader. It consists of compact technical records, codes and values that must be interpreted before they can help explain a performance change.

That distinction matters. A record may show that a plan changed, but not immediately tell an operations team or developer what changed, when it changed, or where to begin looking.

Compact data can contain a great deal of history

An access-plan record can contain information about the SQL statement, the job that used it, the plan number, the time of the observation and the reasons associated with a changed plan. This is useful evidence, but it is spread across technical fields and IBM-specific codes.

For example, a reason code may indicate that a table or member was recreated, that the number of rows changed substantially, that an index became unavailable, or that the Optimizer selected a different Open Data Path.

None of those details alone proves the root cause of a slowdown. They do, however, turn a vague observation into a more focused technical question.

The challenge is not only collecting the records

Collecting Plan Cache information is only the first step. The information must also be retained in a form that makes comparison possible.

A single snapshot can show the plan selected at one moment. A series of recorded snapshots can show that the same SQL statement used another plan earlier, when the plan changed, and which IBM reason codes were recorded at the time.

That historical context is especially valuable when a job has run reliably for a long period and then begins to behave differently. Without it, a team may see the current plan but have no practical way to compare it with the plan that was used before the change.

Turning codes into an investigation starting point

GiAPA SQL Observer retrieves selected Plan Cache data at defined intervals and expands the compact records into a report intended for investigation.

The report can connect an SQL statement with the access plans observed over time and present IBM’s recorded reasons for plan changes in a readable form. Instead of beginning with a large amount of raw data, the user can identify the relevant statement, see that a plan changed, and examine the context around that change.

This does not remove the need for SQL expertise. It makes that expertise more effective by preserving the evidence needed to ask the right question.

IBM’s reason codes provide context, not a verdict

It is important not to treat a reason code as a diagnosis. A code indicating that a table was recreated, for example, does not automatically explain all performance behaviour that followed.

Its value is that it narrows the investigation. It may show that a plan was rebuilt after a specific event, or that the Optimizer had different conditions when it selected a new access method.

That is a much stronger starting point than simply knowing that “SQL became slower.” The investigation can proceed from a recorded change, at a recorded time, with a concrete technical context.

From historical evidence to deeper analysis

When the relevant statement and plan have been identified, IBM SQL Performance Center can be used for detailed examination of the plan itself.

GiAPA SQL Observer and IBM SQL Performance Center therefore serve different but complementary purposes. SQL Observer preserves history and helps reveal where a relevant change occurred. SQL Performance Center supports the deeper analysis of why a particular plan behaves as it does.

For teams responsible for IBM i applications, that combination makes SQL performance easier to follow over time. It replaces part of the guesswork with a record of what actually happened.

This article is part of GiAPA’s guide: When SQL Performance Changes: What Access-Plan History Can Reveal on IBM i.