If you have ever experienced the problem of a run-a-way Query or SQL, you may know of the potential danger that...
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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.
1. CRTDUPOBJ OBJ(QBATCH) FROMLIB(QGPL) OBJTYPE(*CLS) + NEWOBJ(MAXBATCH
2. CHGCLS CLS(MAXBATCH) MAXTMPSTG(512000)
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
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.
3. DSPSBSD SBSD(QBATCH)
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.
4. CHGRTGE SBSD(QPGMR) SEQNBR(20) CLS(MAXBATCH)
5. CHGRTGE SBSD(QPGMR) SEQNBR(9999) CLS(MABATCH)
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.
MORE INFORMATION ON THIS TOPIC
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.