Improved Temporary Storage Tracking for IBM i (Part 9)
IBM continues to enhance temporary storage tracking and management, which was significantly improved beginning with IBM i 7.2.
IBM continues to enhance temporary storage tracking and management, which was significantly improved beginning with IBM i 7.2.
One of the first changes made, back in 7.1, was to hold jobs that exceed their maximum CPU or maximum temporary storage; I described this change in IBM i 7.1: Jobs Exceeding their CPU or Storage Limits are now Held. Of course, you need to set the limits for the maximum CPU or maximum temporary storage parameters to take advantage of this control.
The temporary storage buckets, added in 7.2 and initially written about in Part 1 of this series, are either scoped to a job or are system-wide buckets. The maximum temporary storage parameter limits the amount of temporary storage a job can allocate.
As written in parts 7 and 8 of this blog series, Db2 for i uses temporary storage for running queries. Parts 7 & 8 link to articles that describe how temporary storage is used by the SQL Query Engine and how it’s tracked in several global buckets whose names begin with *DATABASE.
Why did I summarize these seemingly disparate blog topics here? PTFs have been made available from IBM that expand the maximum temporary storage setting for a job to include the SQL temporary storage usage in the *DATABASE global buckets.
The blog posts noted above dovetail nicely with a recently published article titled Establish SQL Temporary Storage Limits by Tim Clark of IBM. This article on IBM i developerWorks explains changes that have been made to IBM i to limit the use of temporary storage used by SQL with the maximum temporary storage parameter for a job. Read Tim’s article for all the details.
For your reference, below are links to all the prior blog articles on improved tracking of temporary storage.
- 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
- Part 6 – Temporary storage service that allows you to use SQL to query the temporary storage buckets
- Part 7 – Temporary storage and the database, part 1
- Part 8 – Temporary storage and the database, part 2