Limiting temporary storage and CPU used by queries

Edit online

Follow these recommendations to limit temporary storage and CPU usage.

You can prevent the query optimizer from using excessive temporary storage when running a query by setting the maximum temporary storage parameter for jobs on your system. The maximum temporary storage parameter (MAXTMPSTG) can be assigned to a class of jobs or it can be set for an individual job. The parameter, defined in megabytes, limits the amount of temporary storage that a job may allocate while it is running. The default value is *NOMAX, meaning no limit is placed on the temporary storage. See the Change Job (CHGJOB) command for more information: https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/cl/chgjob.htm#CHGJOB.MAXTMPSTG

Temporary storage can be allocated by application code, by the optimizer, or by other parts of the operating system. If the total temporary storage currently allocated by the job exceeds the maximum, the job is held and a CPI112E message, "Job & held by the system, MAXTMPSTG limit exceeded." is sent to the job log and to the QSYSOPR message queue. The job remains held until the maximum is increased or removed and the job is released, or until the job is ended. Note that with respect to the maximum temporary storage limit, only temporary storage newly allocated by the optimizer for the currently running query is counted. If a query plan is reused from the plan cache, any associated temporary storage will not be counted against the limit. Likewise, once the query has completed, the optimizer's temporary storage is no longer counted against the limit, even if the cached plan and its associated runtime objects continue to remain allocated.

Consider the following example for a job with MAXTMPSTG set to 100 MB:
  1. Query 1 is submitted, is optimized, and allocates 60 MB of temporary storage. When the query is done, the plan and its temporary storage are cached.
  2. Query 2 is submitted, is optimized, and allocates 50 MB of temporary storage. When the query is done, the plan and its temporary storage are cached. Total temporary storage usage has increased by 110 MB, but no single query has exceeded 100 MB, so the job continues to run.
  3. Query 3 is submitted, and a matching plan produced from a previous run is found. This plan uses temporary objects totaling 150 MB. However, because this job is not newly allocating the temporary storage, MAXTMPSTG is not exceeded.
  4. Query 4 is submitted, is optimized, and allocates 200 MB of temporary storage. MAXTMPSTG is exceeded and the job is held.

In a similar manner, the Maximum CPU time (CPUTIME) parameter may be used to prevent jobs from utilizing excessive amounts of CPU. The limit is specified in the number of milliseconds of CPU time that a job may consume. As with MAXTMPSTG, exceeding the limit will cause the job to be held until the limit is changed or the job is ended.

Related limits on temporary storage and running time are also available on the Change Query Attributes (CHGQRYA) https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/cl/chgqrya.htm command. Query processing time limit (QRYTIMLMT) can be used to prevent the optimizer from running queries that are expected to take a long time, whereas QRYSTGLMT can prevent the optimizer from running queries that are expected to use a large amount of temporary storage. Note that these differ from MAXTMPSTG and CPUTIME in that they apply to each query individually and are based on estimates calculated by the optimizer before the query runs. By contrast, the job-based limits apply to the actual resource usage measured as the job runs.