The ability to monitor Db2 using
profile tables is a newer, though by no means brand new capability for Db2
DBAs. You can use profile tables to monitor and control various aspects of Db2
performance such as remote connections and certain DSNZPARMs.
But this blog post is not intended
to describe what profile monitoring is, but to discuss the new capabilities
added in Db2 12 to enhance profile monitoring.
There are four new enhancements offered
by Db2 12 for the use of system profiles.
The first enhancement is the ability
to automatically start profiles when you start up a Db2 subsystem. This can be
accomplished using a new subsystem parameter called PROFILE_AUTOSTART. Setting
the parameter to YES causes Db2 to automatically execute START PROFILE command
processing. The default is NO, which means that Db2 will not initiate START
PROFILE when the subsystem starts up.
The second improvement is the
addition of support for global variables. As of Db2 12 you can specify the
following global variables as a KEYWORDS column value in the SYSIBM.DSN_PROFILE_ATTRIBUTES
table:
- GET_ARCHIVE
- MOVE_TO_ARCHIVE
- TEMPORAL_LOGICAL_TRANSACTION_TIME
- TEMPORAL_LOGICAL_TRANSACTIONS
If a profile filter matches a
connection, Db2 will automatically apply the built-in global variable value to the Db2 process of that
connection when the connection is initially established, and when a connection is reused.
Wildcarding support is the third
enhancement for profiles in Db2 12. One row for each profile is contained in the
SYSIBM.DSN_PROFILE_TABLE. Each column in the table informs Db2 which connection
to monitor. Without wildcarding, handling various connections required multiple
rows to be defined in the table. But with Db2 12, you can have one row representing
more than one connection. Wildcarding is available for AUTHID (authorization
IDs), LOCATION (IP addresses of monitored connections), and PRDID (product
specific identifier, for example DSN for Db2).
The fourth and final enhancement is
for managing idle threads. The MONITOR IDLE THREADS column in the SYSIBM.DSN_PROFILE_ATTRIBUTES
table directs DB2 to monitor (for an approximate amount of time) an active server thread’s idle
time. The ATTRIBUTE1 column, which is used to specify the type of messages and level of detail of
messages issued for monitored threads, has been enhanced to allow the following
values:
- EXCEPTION_ROLLBACK
- EXCEPTION_ROLLBACK_DIAGLEVEL1
- EXCEPTION_ROLLBACK_DIAGLEVEL2
Note: This particular change to idle
threads
for EXCEPTION_ROLLBACK was made available
in Db2 11 after general
availability, and will be
available on a Db2 12 system after new function
is activated.
For more details on any of these
capabilities, or indeed, on profile monitoring in general, refer to the IBM Db2
12 for z/OS Managing Performance manual, SC27-8857.
