Find the size of jobs using QTEMP on the IBM i System

Hunting down QTEMP Fat Cats

As an IBM i propeller-head you are most likely familiar with the QTEMP library. Right?

You already know that QTEMP isn’t a standard library – it’s unlike all the other libraries (called ‘schemas’ in SQL speak) because it’s yours and yours only:

• Every job has its own unique QTEMP library that only it can access
• Every QTEMP is created when a job starts uniquely for that job
• Every QTEMP and all it’s contents are deleted/destroyed when the job ends
• You cannot MOVE an object from a standard library into a QTEMP library
• There is no size limit on your QTEMP library (unless your user profile has a max storage setting against it)

Essentially, QTEMP is a unique temporary storage area for your programs to create temporary transient objects. Typically programmers will use it to create work files, temporary data objects, and any transient data that a program or job is using.

Programmers regularly use the QTEMP library

For example – imagine I’ve been tasked with writing a program to create a spreadsheet of some order information and then email it to a mailing list. I would typically design this using QTEMP to create/store the spreadsheet data while it’s being generated. I might create an empty file called “orderstuff” in a standard library. Then when the program runs it would duplicate that into QTEMP, load the QTEMP version and then do its email processing — the benefit of this technique is that we can have multiple jobs running doing the same thing (all duplicating the file and working on their own copies in QTEMP) without any contention issues. Good programming practice is to delete this QTEMP “orderstuff” file when I am finished with it. But some programmers are lazy and just leave it relying on the IBMi system to delete it when my job completes.

In summary — Good Programmers handle cleanup in QTEMP when they finish using these temporary objects. Bad Programmers don’t handle cleanup, instead opting to rely on the fact that QTEMP will be emptied when the job ends.

QUESTION – If QTEMP libraries are automatically deleted when the job ends or the user signs off, why bother with cleanup?

Once you finish with your QTEMP data delete, otherwise it’s simply sitting their eating up system storage.

“This is not a problem because it’s only a few megabytes” I can hear you think

But, since you can have thousands of jobs running your program – this lazy thinking can soon add up to terabytes of wasted storage. #naughtyprogrammer

Worse than this…

If your program is a constantly running job – for example a webserver process – this QTEMP storage can grow and grow and grow even ending up with building so much storage it can bring your system to its digital knees.

Temporary Storage and QTEMP are not the same thing

Temporary storage includes all kinds of things from table indexes, to job log information to objects that have been replaced by PDM compiles. Running the RCLSTG command, or IPL’iong (rebooting) your system will normally clean all this temporary storage back to zero.

We can look at system temporary storage using the WRKACTJOB command. Press F11 until you see the temporary storage column:

Note, the temporary storage (in megabytes) does not include storage for objects in the QTEMP library:

How do I find the size of QTEMP libraries across my IBM i System?

As long as your system includes the service packs and PTF’s released by IBM in 2018 . These software fixpacks included enhancements to IBM i services announced with IBM i 7.3 TR5 and IBM i 7.2 TR9 and Db2 Group PTF.

The most notable enhancement is a change to the QSYS2.ACTIVE_JOB_INFO() table which now includes an extra optional value called DETAILED_INFO. You can specify DETAILED_INFO => ‘ALL’ to return all kinds of information about your jobs but we are specificalytl interested in the DETAILED_INFO => ‘QTEMP’) value.

more columns about your active jobs.

The DETAILED_INFO parameter has three options:

• Return NONE of the additional information (the default)
• Return ALL of the additional information
• Return QTEMP_SIZE in addition to the general information

Using the QSYS2.ACTIVE_JOB_INFO service, a very simple SQL statement will return the jobs with the largest QTEMP library sizes.

— see which jobs have the largest QTEMP library

Let’s ask to see the JOB_NAME, AUTHORIZATION_NAME (the current user), QTEMP_SIZE, JOB_STATUS, and CPU_TIME. Then we can say only show where QTEMP is greater than ZERO MB, from largest to smallest, and just show the top twenty offenders:

select JOB_NAME, AUTHORIZATION_NAME, QTEMP_SIZE, JOB_STATUS, CPU_TIME from table (QSYS2.ACTIVE_JOB_INFO(DETAILED_INFO => 'QTEMP')) where QTEMP_SIZE >0 order by QTEMP_SIZE desc limit 20

And this shows us:

JOB_NAME AUTHO00001 QTEMP_SIZE JOB_STATUS CPU_TIME
065863/BACKBEAT/QPADEV0004 BACKBEAT 39 DSPW 39,250
065746/NICK/QPADEV0007 NICK 18 RUN 2,481
063430/QUSER/QNPSERVR QUSER 1 PSRW 9
065844/BBCRAIG/QPADEV0008 BBCRAIG 1 DSPW 1,809
065919/QTMHHTTP/QHTTP QTMHHTTP 1 SELW 23
066046/QUSER/QNPSERVS QUSER 1 PSRW 6
End of data

We could also do this using the IBM ACS SQL tool:

This SQL doesn’t work for me!?!?!!?

Go and talk to your IBM i Operations team and check that the required service packs are installed.