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.
By Dawn May04/26/2018
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
Dawn May is an IBM i consultant. She owns Dawn May Consulting, LLC in the Greater Boston area. Dawn is a former IBM senior technical staff member.