According to the Collins Dictionary, the word taming can be defined as: to overcome the wildness or fierceness of; make gentle, docile, obedient, or spiritless; subdue. Wild and fierce are words that you normally would not associate with the temporary storage used by queries. However, if the temporary storage used by runaway queries starts to push your system storage levels to 90-95% of capacity, then you probably will feel the fear associated with a wild animal approaching your camp. There are probably a few administrators out there who have had nightmares about system crashes due to running out of disk space.
The good news is that over the last several years, Db2 for i has added features (e.g., Query Supervisor) that make it easier to monitor and control the temporary storage being used by queries. The bad news is that our IBM Technology Expert Labs team is seeing these newer Db2 features not being used by clients as they engage us to help deal with system and SQL performance issues. You know what they say: you can lead a horse to water but you can’t make him drink.
To make it easier to leverage some
of these Db2 for i features to keep query temporary storage usage under
control, the IBM Technology Expert Labs team recently made two new tools
available. These new aids are a Configurable Query Supervisor exit program
toolkit and a Work with Queries using Temporary Storage command.
The Query Supervisor is a very flexible solution because you can create an exit
program to take a wide variety of actions whenever a query exceeds a threshold
like 10 GB of temporary storage. What our Db2 Expert Labs teams has
discovered is that Database Engineer (DBE) and System Admin teams don’t have the time and/or skills to
create and maintain an exit program. Thus, this new toolkit allows clients to
use the Query Supervisor to control query temporary storage without any
programming! The exit program logic is controlled with a configuration table.
After defining a query supervisor
threshold, user just need to perform a simple insert operation on the
configuration table to define which actions should be taken when a query exceeds a
temporary storage threshold. The supported actions include notifying QSYSOPR,
cancelling the query, logging the event on a data queue, and sending an email
via the SNDDST command. These actions
can also be conditional based on the current usage percentage of the system
storage pool. For example, you may want to cancel a query if the System Storage
Pool usage percentage is at 90%. However, if the System Storage usage is only
at 70%, then maybe you have the exit program just notify QSYSOPR and allow the query
to continue to run. If the initial set of configured actions don’t meet your
requirements, then you just need to update the settings in the configuration
table. No changes to the exit program or
recompiles are necessary!
The Active_Query_Info
service makes it pretty
easy to identify queries on your system that are consuming temporary storage or
rely on large maintained temporary indexes (MTIs). Once the query is
identified, however, you’re probably going to have to run another command to
take action on the query that is gobbling up temporary storage. The new Work with Queries using Temporary
Storage (WRKQTMPSTG) command enables you to take quick action on the queries
that are returned by the Db2 service.
As you can see in the figure below
once an active query with high temporary storage usage is identified you can
either end or hold the job to prevent the query from consuming more temporary
storage on the system. The command also allows you to log the offending
query to a Plan Cache Snapshot for later review by a DBE or developer. Another option would be to use the QRO Hash
value from the display to analyze the query with Visual Explain by using this value as a
filter in the Plan Cache viewer that’s available with the IBM i Access Client
Solutions (ACS) SQL Performance Center.
This new support also provides filter and sort capabilities to make it faster for you to identity the queries causing the temporary storage headaches on your system.
Neither of
these new tools completely automates the taming of query temporary storage on
your system, but hopefully you can see how they make it much simpler for you to
monitor and control query temporary storage usage on your system. Why live in
fear of wild, runaway queries overrunning the storage on your system when
there’s a better way. If you’re interested in getting more information on how
to purchase these new tools from IBM Technology Expert Labs, then just shoot me
an email.
