This is the final article in the series of blogs reviewing the enhancements in IBM i 7.2 for improved tracking of temporary storage. This blog describes the IBM i Temporary Storage Service. It is one of the many IBM i Services that have been added to IBM i over the past few years.
The IBM i Temporary Storage Service allows you to use SQL to retrieve information about the temporary storage buckets. You can view the temporary storage buckets in Navigator for i (which was covered in Part 2), but there is no command interface to display that information. If you need a text-based interface to display the buckets, this temporary storage service is how you do it.
The SYSTMPSTG article in the IBM i Knowledge Center has a nice write-up on the storage buckets.
If you have a temporary storage leak, often that temporary storage will be associated with a job and you can use the new column in Active Jobs (or Work with Active Jobs on the green screen) to sort the jobs by the “temporary storage” column. Sometimes, ended jobs may still be associated with a temporary storage bucket if the job ended without releasing the temporary storage it allocated.
However, many other system activities also use temporary storage. This is one of the major changes in the 7.2 temporary storage accounting – you can now see and account for all temporary storage use on the system. If you suspect that the storage consumption is not associated with a job, you need to review the temporary storage buckets.
With the temporary storage service, you can create SQL statements that will show you exactly the temporary storage usage that you want to see. Let’s review a few simple examples:
· This first query will return all the temporary storage buckets, sorted by the current bucket size; all the columns of information are displayed.
SELECT * FROM QSYS2.SYSTMPSTG ORDER BY “BUCKET_CURRENT_SIZE” DESC
The results of this query are below:
· This next query will return all the temporary storage buckets that are not associated with a job.
SELECT “BUCKET_CURRENT_SIZE”, “BUCKET_PEAK_SIZE”, “GLOBAL_BUCKET_NAME” FROM QSYS2.SYSTMPSTG WHERE((“GLOBAL_BUCKET_NAME” IS NOT NULL)) ORDER BY “BUCKET_CURRENT_SIZE” DESC
The results of this query are below:
· The following query will return the temporary storage buckets for just the active jobs, with the results sorted by the current bucket size. Note that only some of the columns are selected in this query.
SELECT”JOB_NAME”, “JOB_USER_NAME”, “JOB_NUMBER”, “BUCKET_CURRENT_SIZE”, “BUCKET_PEAK_SIZE” FROM QSYS2.SYSTMPSTG WHERE((“JOB_STATUS” = ‘*ACTIVE’)) ORDER BY “BUCKET_CURRENT_SIZE” DESC
The results of this query are below.
You can see the jobs using the largest amount of temporary storage are at the top of the list. This is somewhat similar to what you can get from Active Jobs or WRKACTJOB, but this query also returns the peak bucket size, which is not viewable from Active Jobs.
In this example, the peak bucket size is nearly the same as the current bucket size. However, there may be cases where storage is used then released – the peak bucket size can help you find the buckets that peaked at a large value.
You could easily modify the query above to show you temporary storage associated with ended jobs.
You can use more complex queries to extend your results. The example on the IBM i developerWorks page extends the query to pull in additional information using the QSYS2.GET_JOB_INFO service. In that example, the query looks for active database server jobs using the most temporary storage and extends the query to also include the SQL statement the job is executing.
This blog concludes a review of all the new capabilities for improved tracking of temporary storage with the 7.2 release. Here is a full list of my 7.2 temporary storage tracking blogs:
· Part 1 – Introduced the changes made in 7.2 including the new temporary storage buckets
· Part 2 – System status enhancements in 7.2, including the ability to display the temporary storage buckets in Navigator for i
· Part 3 – Enhancements made to better understand temporary storage consumption at a job level
· Part 4 – Enhancements made to IBM i performance data and the new charts in the performance data investigator
· Part 5 – Notification/automation support to proactively determine potential temporary storage issues
This blog post was originally published on IBMSystemsMag.com and is reproduced here by permission of IBM Systems Media.