Problem solve Get help with specific problems with your technologies, process and projects.

Limiting maximum Qtemp storage on query joins

This technique provides a way to set a ceiling limit of the amount of temporary storage a job can use.

If you have ever experienced the problem of a run-a-way Query or SQL, you may know of the potential danger that...

exists with your DASD reserve going critical. Often ill constructed joins will run for an excessive amount of time, building work files in the jobs Qtemp library. Since Qtemp library objects for jobs other than your own are hard to see this condition is difficult to diagnose. The typical symptoms are that your system may be slowing down and you see on the Work Systems Status (WRKSYSSTS) display the "% system ASP used" figure going sky high. This technique provides a way to set a ceiling limit of the amount of temporary storage a job can use.

Class objects on your system have a parameter for "Maximum temporary storage in megabytes" (MAXTMPSTG). Once your jobs use of Qtemp exceeds this value the job will be ended. This is not a soft landing, but rather a hard end job with the message.

CPC1217  Message . . . . :   Job ended abnormally.                                   
Cause . . . . . :   The job exceeded the maximum temporary storage limit.   

However, your job is not likely to be canceled if you are using the Qtemp library to store normal work objects (files, pgms, etc.). As the help text states for the MAXTMPSTG parameter on the class object, "This does not apply to the use of permanent storage, which is controlled through the user profile."

Implementing this technique is quite simple. My example will set limits for only batch jobs. Replace the references for QBATCH to QINTER to include interactive processing.



Note here that the value I use for MAXTMPSTG is 512000. This is because the value entered is in 'kilobytes'. I am setting the value to 500 'megabytes'. To calculate this parameter use X 1024 = kilobytes, or 500 X 1024 = 512000. After setting this value it is easy to verify that it is correct. Use the command DSPCLS MAXBATCH and the figure will be shown in megabytes. Because every system is different you will need to find the value that works well on your system. I suggest 300 - 500 megabytes. However, you may have some jobs that require more space.

Now you must replace the IBM default class assignments in your subsystem definitions. For the appropriate subsystems (likely QINTER and QBATCH) use the command Display Subsystem Description.


Then use menu option '7. Routing entries' to display a list of routing entries for the subsystem. You need to find any in the 'Compare Value' column showing 'QCMDB' or '*ANY' (QCMDI for interactive). Now note the sequence column 'Seq Nbr' values for these lines with the needed compare values.

Seq Nbr    Program       Library       Compare Value
      10      QARDRIVE  QSYS          '525XTEST'   
      20      QCMD          QSYS          'QCMDB'      
    700      QCL              QSYS          'QCMD38'     
  9999      QCMD          QSYS          *ANY 

Note: While viewing the Routing entries use the option '5' to display the details and note the current class value. If you wish to reset to default you will want to have these names. The default is QBATCH (QINTER for interactive).

Now we want to change the subsystem description to use our new class objects. This can be done while the subsystem is active.



Notice above that I changed both SEQNBR 20 and 999.

Some 3rd party packages that use SQL may install their own routing entries and class objects. You should contact their support staff for details to have this system management technique control their joins as well.


The Best Web Links: tips, tutorials and more.

Ask your systems management questions--or help out your peers by answering them--in our live discussion forums.

Ask the Experts yourself: Our systems management gurus are waiting to answer your technical questions.

Dig Deeper on Performance