IBM Developer

Article

Establish SQL temporary storage limits

Put the brakes on runaway queries

By Tim Clark

On this page

Going off the rails on a crazy train

In a dramatic scene from the 2004 movie Spider-Man 2, hero Peter Parker finds himself caught on a runaway train loaded with passengers and hurtling toward the end of its track. Mustering all his spidery super powers, he brings the train to a gut-wrenching halt just before it plunges to certain destruction.

You won’t often have tentacled super-villains trying to push your IBM i system past its limits. But it can sometimes seem as if you do: ad-hoc queries, bad application logic, or that one frustrated user pressing Submit over and over can exhaust your system’s temporary storage and leave it maxed out and unresponsive. As with runaway trains, it is best to stop runaway jobs before they gain a full head of steam. Improved support from IBM for temporary storage controls makes this easier than ever. So, put on your favorite superhero mask, and keep reading to find out how to increase your own query-taming superpowers.

The temporary storage story

First, let’s do a brief review of how database temporary storage works on IBM i. When a database query runs, temporary storage is allocated to perform the functions of the database engine. Some of this storage is allocated by the database manager and assigned to the job running the query. Other storage is allocated by the SQL query engine (SQE) and is not assigned to the job. Instead, this storage is assigned to one of several global storage buckets, including *DATABASE Segment Cache, *DATABASE SQE Heap, *DATABASE DSI SQE MTI, and *DATABASE DS SQE LOB buckets. For the remainder of this article, we’ll refer to this global storage used by the query engine to run the query as SQE temporary storage. The allocations made by the database manager, other system components, and the application program itself will be referred to as non-SQE temporary storage.

SQL temporary storage problems can be caused by poorly written ad-hoc queries or they can simply be the effect of big data getting bigger. Regardless of the cause, no one wants to see their system slow to a crawl because one (or several) queries went wild and consumed all available storage. The query engine has always contained some simple safety measures to reserve a small amount (approximately 1%) of system storage. However, these limits are last resort efforts to avoid causing a system crash, and they are of little use to system administrators and database engineers who want to fine-tune their systems. More capable tools are needed, and IBM i provides them.

Readers with long experience on IBM i, and before that on OS/400, will know that controlling the resource usage of jobs and queries has been a capability of the operating system for many years. The Predictive Query Governor has been available since release IBM System i5 V5R4. Job-based limits on CPU and temporary storage limits are even older. These tools are still valuable in many situations, but the newest tool, the Query Supervisor, provides an even stronger set of controls over query resource usage, including for SQE temporary storage. We’ll look at each of these tools to consider how they may help combat query temporary storage problems.

Query Governor

The predictive Query Governor provides a way for the database engineer or administrator to intervene before a query runs by setting limits on the amount of SQE temporary storage or run time that a query may consume. After the query has been optimized, but before it begins to run, the Query Governor compares the estimated resource usage to any defined Query Governor limits. If an estimate exceeds the defined limit, the Query Governor will send a CPA4259 inquiry message and prevent the query from running until a response is made to the inquiry message. Additional customization is available by registering an exit program with the QIBM_QQQ_QUERY_GOVR exit point.

The Query Governor can be useful, but it is limited by its reliance on estimated resource usage. As queries grow more complex, so does the difficulty of calculating accurate estimates for resource usage. In practice, the Query Governor often ends up with both false positives (stopping a query that should have been permitted) and false negatives (allowing a query that should have been stopped.) For this reason, IBM recommends using the next two controls rather than the Query Governor for most resource monitoring needs.

Job controls

A more robust method of setting temporary storage limits is to use the maximum temporary storage (MAXTMPSTG) parameter on the Change Job (CHGJOB) command. Although earlier releases applied these limits only to non-SQE temporary storage, IBM i 7.2 and later releases also include SQE temporary storage within the scope of MAXTMPSTG. And unlike the Query Governor limits, MAXTMPSTG applies to actual resource usage, not just estimates.

To apply a limit to multiple jobs, you can use the Create Class (CRTCLS) or Change Class (CHGCLS) commands to set the MAXTMPSTG parameter for the appropriate class associated with a subsystem description’s routing entry.

Whether for a single job or for a class, setting the value (in megabytes) assigned to this parameter will limit the amount of temporary storage (both SQE and non-SQE) a job can use. If the limit is reached, the job is held and a CPI112E message, “Job … held by the system, MAXTMPSTG limit exceeded.” is sent to the QSYSOPR message queue and to the job log. The job remains held until someone intervenes by ending the job or by increasing (or removing) the MAXTMPSTG limit for that job and then releasing the job.

Query Supervisor

While MAXTMPSTG helps you prevent runaway temporary storage consumption in a simple and direct manner, the Query Supervisor offers a more flexible and complete solution. Introduced in IBM i 7.3, the Query Supervisor provides real-time monitoring of query resource usage during query execution. Various kinds of resources can be monitored, like CPU time and I/O operations, but for this article, we’ll focus on supervising temporary storage.

It is important to know that the temporary storage monitored by the Query Supervisor is SQE temporary storage. Non-SQE temporary storage is not tracked by the Query Supervisor. If non-SQE temporary storage is a concern, MAXTMPSTG is the better solution.

However, for most database applications, it is SQE temporary storage that causes the most frequent problems. The Query Supervisor lets you define multiple thresholds for temporary storage (and other resources) and to apply them differently depending on user, job, and subsystem. Defining a threshold is as simple as calling the ADD_QUERY_THRESHOLD procedure. For example, to add a threshold of 500 megabytes (MB) for all queries running in the QUSRWRK subsystem, you could use the following syntax:

CALL QSYS2.ADD_QUERY_THRESHOLD(THRESHOLD_NAME => 'No runaways!',
                               THRESHOLD_TYPE => 'TEMPORARY STORAGE',
                               THRESHOLD_VALUE => '500',
                               SUBSYSTEMS => 'QUSRWRK');

But adding thresholds is only the first step. The second step is to define the action that is taken when the threshold is reached. With the Query Supervisor, that action is entirely under your control, defined by the exit programs that you register with the QIBM_QQQ_QRY_SUPER exit point. Writing your own exit program for the Query Supervisor may seem a daunting task, so IBM has provided CL, RPG, and C language source code for a number of ready-for-use example exit programs. These enable you to notify QSYSOPR, capture information about the query, and even instruct the query engine to end the query once a threshold has been exceeded.

Once you have configured a threshold and an exit program to respond to the threshold, the Query Supervisor is ready to help you take control of query temporary storage problems.

Storage accounting surprises

As noted earlier, unlike other temporary storage allocated for a job, SQE temporary storage is allocated out of system global storage buckets. This storage may contain objects like temporary results and maintained temporary indexes (MTIs). Because these objects are globally available through the plan cache, other jobs are free to access and use these objects when running queries. This caching and sharing provides performance benefits, but accurately assigning ownership of the underlying storage quickly becomes complicated.

To accommodate this complexity, the tools described in this article only consider the storage that is newly allocated for the query currently running. If cached objects are re-used, these are not included in the SQE temporary storage accounting. Similarly, once a query ends and its cached objects become available to other jobs, the storage allocated for those objects is no longer counted against the job’s SQE temporary storage. This is true even though the storage remains allocated on the system.

Let’s look at an example. The MAXTMPSTG for a job is set at 1 gigabyte (GB) and a Query Supervisor threshold of 500 MB is active for the same job. As the job runs, it consumes 100 megabytes (MB) of non-SQE temporary storage. The job also runs three queries in sequence, each of which requires 400 MB of newly allocated SQE temporary storage for database objects. The query engine caches these objects for reuse. As a result, the job has increased system temporary storage usage by a total of 1300 MB, which is greater than the 1 GB MAXTMPSTG limit. We might expect that running the third query would cause the job to be held. However, because only the active cursor’s storage is counted, at any point in the job’s run no more than 100 MB plus 400 MB was counted toward the limit. The calculated temporary storage used was never more than 500 MB, and the MAXTMPSTG was never reached. Furthermore, because no query allocated more than 400 MB of new SQE temporary storage, the Query Supervisor limit of 500 MB was not reached.

On the other hand, if the job runs a fourth query requiring over 900 MB of new SQE temporary storage, both the MAXTMPSTG limit and the Query Supervisor threshold will be reached. The Query Supervisor exit programs will be invoked once the allocation has exceeded 500 MB. If the exit programs allow the query to continue running, the job will continue until the MAXTMPSTG limit is met, and then it will be held. The job will remain held until it is ended or until the limit is increased and the job is released. Note that canceling the query will not release the job. The cancel may be requested while the job is held, but the job must be released before the cancel request is processed.

At this point, you might wonder: couldn’t the job just keep running new 400 MB queries and exhaust the system’s storage in that way? It is important to remember that there are other internal limits controlling the caching of database temporary query objects. While the details are beyond the scope of this article, it is sufficient to note that the SQL plan cache will not grow unbounded. Eventually some of the temporary results will be purged to make room for newer ones. The situation with MTIs is a little different, but a previous article in this series gives guidance on how to monitor these.

Putting it to use

Having considered the way that the SQE temporary storage accounting works, we can develop a general strategy for using the temporary storage monitoring support. You’ll want to think about the jobs, users, or subsystems you want to limit and how much storage would be considered excessive. Well-behaved applications that use predictable queries might not need any limiting. But applications and interfaces that allow ad-hoc queries are susceptible to runaway allocations and will merit closer evaluation. Consider how much main store memory is allocated to the pool that the jobs are running in. How much storage is available in SYSBAS? How many of the susceptible jobs run simultaneously? You don’t want a limit so high that the job (or several jobs combined) can fill up SYSBAS without encountering the limit, but neither do you want to set the limit so low that you are constantly having to intervene and release jobs or crawl through hundreds of useless alerts.

You’ll also want to consider what kind of intervention you want to take when a storage limit has been exceeded. Is holding the job or pausing the query sufficient? Or do you need a more flexible response?

The following table summarizes the differences between the various tools and can help you determine which tool is best for your situation.

Tool Applies to Intervention taken Other resources monitored

Job limits (MAXTMPSTG)
Combined actual SQE and non-SQE temporary storage
Job held
Actual job CPU time

Query Governor
Estimated SQE temporary storage
Query paused for inquiry message

Others defined by exit programs
Estimated query run time

Query Supervisor
Actual SQE temporary storage
Defined by exit programs
Actual query run time

Actual query CPU time

Actual query I/O operations

If you do stop a runaway query in its tracks, consider it an opportunity to delve deeper. Avoid the temptation to end the query and move on. Instead, find out why the query is consuming so much storage. Is the query trying to do too much? Did the query environment change? Would the right index enable the optimizer to choose a plan that uses fewer temporary runtime objects? If you need help, see the resources at the end of this article for more information about improving your SQL performance.

Full steam ahead

Quick thinking, web-shooters, and super-human strength enabled Spider-Man to save a train full of terrified passengers. Your “super-powers” may be less glamorous, but they are no less essential to keeping your systems running well. With resource management tools from the Db2 for i team and the information in this series of articles, you have more tools to diagnose, solve, and—most importantly—prevent serious system emergencies.