IBM Reason Codes Do Not Solve the Problem, but They Change the Question

An SQL access plan can change for many reasons. The useful question is not simply whether the plan changed, but what IBM recorded about the conditions surrounding that change.

IBM i provides reason codes when an access plan is rebuilt or when the optimizer selects a different access method. These codes do not provide a complete diagnosis. They do, however, give an investigation a much more concrete starting point.

Why a reason code matters

Without historical evidence, a team may only know that an SQL job became slower. The SQL statement may be unchanged, and a current plan may look reasonable. What is missing is the context: what was different when the plan changed?

A recorded reason code can help narrow that question. It may indicate that a table or member was recreated, that row counts changed significantly, that an index was added or became unavailable, or that the optimizer selected a different Open Data Path.

Each of these possibilities points the investigation in a different direction.

A clue is not a verdict

It is important not to treat a reason code as a final explanation. A code can show that a plan was rebuilt after a relevant condition changed, but it does not automatically tell us whether the resulting plan was good or bad for a particular workload.

The value is that it replaces a vague question with a more focused one. Instead of asking why SQL suddenly became slower in general, the team can ask whether the change followed a recreated table, changed row counts, an index change, or another recorded condition.

That is a much better place to begin.

From plan change to practical investigation

Consider a plan that changed after an index became unavailable. That does not prove that the missing index explains all of the performance difference. But it gives the technical team a specific condition to examine.

The same is true when a plan is rebuilt because the number of rows has changed significantly. The next step is not to assume that more rows are automatically the problem. The next step is to look at how the optimizer responded, what access plan it selected, and whether that choice makes sense for the current data and workload.

Reason codes help connect a performance change to the event or condition that may have influenced it.

Why history is essential

A reason code is most useful when it is connected to a history of access plans. A single current plan cannot show what was selected before, when the plan changed, or which conditions were recorded at the time.

GiAPA SQL Observer is designed to preserve selected Plan Cache information over time. It can show that a plan changed and retain the IBM-supplied reason information associated with that change.

This does not replace detailed SQL analysis. It makes the evidence easier to find, so that IBM SQL Performance Center or another technical investigation can begin with a clearer question.

The practical benefit

SQL performance work becomes more manageable when changes can be placed in context. The team does not have to begin with a broad search through every possible explanation.

Instead, historical evidence can show what changed, when it changed, and what IBM recorded about the event. That makes it easier to direct expert attention where it is most likely to be useful.

A reason code does not solve the problem. But it can change the question from “Why did SQL get slower?” to “What changed here, and what should we examine next?”

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

The guide explains why a plan snapshot is not enough, how plan history creates useful context, and how GiAPA SQL Observer complements IBM SQL Performance Center.

‍