GiAPA Tracks SQL Performance Issues On IBM i
March 16, 2026 Alex Woodie
Tracking down SQL performance problems with IBM i applications sometimes can be difficult, thanks to the mysterious ways that IBM’s Db2 query optimizer works. To help shine a light on changes the optimizer may suddenly make to query access plans, the IBM i performance experts behind GiAPA developed a new tool, dubbed SQL Observer.
The Db2 query optimizer is a wonder of software engineering. The IBMers who developed it deserve a ton of credit for creating a smart piece of software that keeps SQL queries running so efficiently. Users simply submit the query they want to run, and the optimizer figures out the best way to fetch the data from the database.
However, even great pieces of software can sometimes leave users wanting more. And that’s the case with the Db2 query optimizer, which sometimes changes the access plans for inexplicable reasons.
The query optimizer may suddenly choose a different access plan based on changing conditions of the box. Small shifts in system conditions, such as available memory, index sizes, CPU load, and table sizes, can lead the query optimizer to select a different access plan, even while the job is still running. This can have a negative impact on the performance of jobs, even though the user made no changes to the software.
This is what drove Kaare Plesner, the creator of GiAPA at iPerformance ApS, to create SQL Observer. According to Plesner, SQL Observer works with the SQL Performance Center component of Access Client Solutions (ACS) to ensure that customers most important jobs are monitored, without needlessly impacting the performance of the IBM i host by monitoring all jobs.
While SQL Performance Center is powerful, it has some drawbacks. For starters, users need to know exactly which access plan is being used when they start monitoring, which can be difficult to do, according to Plesner. It would be nice if SQL Performance could monitor all SQL jobs on the IBM i server, but that’s not possible without turning the server to mush.
GiAPA SQL Observer helps by augmenting the data collection of SQL Performance Center with some additional capabilities. SQL Observer allows the user to specify which SQL jobs to monitor, instead of forcing them to know which access plan is being used. The tool also lets users specify how often to pull access plan log data from SQL Performance Center, thereby ensuring that system overhead is kept to a minimum.
“Instead of relying on unclear assumptions, you get the facts,” the company says in a video on SQL Observer. “You see the job and the SQL statement when the plan cache dumps were made, and where they are stored, the plan number, and the reason it was created and the routines used by the optimizer when selecting the plan.”
SQL Observer also collects the QRO code, which is a unique identifier assigned by the query engine for a specific query. The QRO codes act as signatures for SQL statements, and are critical for data engineers to track down performance issues.
“If there was a change of plan, data describing the previous plans are shown,” iPerformance says in the SQL Observer video. “In many cases, just seeing this data is enough to confirm a suspicion or explain a delay. For each SQL [statement], you will know if and when things change, where dumps are stored, and the QRO code needed to find the access plan if further investigation is needed.”
SQL Observer provides the detailed information necessary to track how SQL jobs tracks across access plans in the Db2 query optimizer.
Armed with the data provided by SQL Observer and SQL Performance Center, GiAPA customers can take steps to ensure that certain SQL access plans are used, thereby improving the performance of critical jobs.
In other news, iPerformance recently signed an agreement with R2i, an IT services firm based in Montreal, Canada, to distribute GiAPA in North America. R2i offers a range of technical services, including managed services on its cloud environment, dubbed Edwin, which is tailored to IBM Power, X86, VMware, and SAP environments.
R2i also boasts digital transformation and application modernization services, custom software development for IBM i, and third-party application maintenance. With the addition of GiAPA, R2i also now provides IBM i application performance testing.
SQL Observer is available as a component of GiAPA suite or as a standalone product. For more information, check out www.iperformance.dk or www.r2i.ca.
RELATED STORIES
Watson-Inspired Pattern Matching Drives IBM i Performance Breakthrough
