Best practices: Monitor and control Db2 system resources with profile tables
Paul_McWilliams 110000JT36 Visits (1108)
In Db2 for z/OS you can use the profile monitoring capability to monitor or limit resource consumption by distributed database facility (DDF) workloads. This capability, which was first introduced in Db2 10, provides a helpful solution for clients who have been burdened in recent years by trying to control the consumption of resources for distributed workloads, as the number of inbound connections and consumption of database access threads by ill-behaved or unpredictable applications have impacted Db2 for z/OS availability.
The following profile tables and corresponding history tables have been supplied with Db2 for z/OS since Db2 9 for z/OS.
To create them, you run the DSNTIJSG installation job. For descriptions of these tables and their columns, see Profile tables.
One previous use of the profile tables enabled clients to simulate their production environments in test Db2 subsystems, so that the Db2 optimizer chose the same access paths in development and test as when the query is promoted to production. Keywords like SIMULATE_CPU_SPEED and COUNT, MAX_RIDBLOCKS, and BPxxx VPSIZE enable test and development environments to mimic production access paths without the physical investment. For more about this use of profile tables, see Modeling a production environment on a test subsystem.
However, our focus for this post is the profile monitoring capability for DDF workloads that was introduced in Db2 10. By using this capability, you can gain much more granular control over the use of resources by distributed workloads, such as the number of concurrent connections (CONDBAT zparm), the total number of concurrent database access threads (MAXDBAT zparm), and the idle thread timeout threshold (IDTHTOIN zparm).
To use this capability, you insert rows into the DSN_PROFILE_TABLE to control which AUTHIDs, IP addresses, or applications that the profile controls. Then you insert rows into the DSN_
The behaviors of profiles differ depending on the filtering criteria that you specify. You can specify the following filtering criteria. The descriptions assume that x is the limit value specified in the profile and that x is less that the corresponding CONDBAT or MAXDBAT zparm value.)
Each time a profile is exceeded, Db2 writes a corresponding message in the range DSNT771I - DSNT774I to the MSTR joblog, with a reason code in the range 00E30501-00E305018. Statistics for these exceptions also appear in the IFCID 402 records, and you can see other evidence of their effects in Statistics Class 1 records under the Global DDF heading.
To track the history of the profiles you can look in the DSN_PROFILE_HISTORY to determine when the profile was started and stopped, and DSN_
The scope of a Monitor Profile is inherently at the member level, meaning if you have a MONITOR CONNECTIONS profile set to 10 for a specific IP address, 10 connections are allowed to each Db2 that profile is started on. Regarding the scope of the profile you create, the GROUP_MEMBER column of the DSN_PROFILE_TABLE allows you to specify which member of the data sharing group the profile applies to, while leaving it blank implies all members use this profile. The PROFILE_AUTOSTART ZPARM will ensure these profiles come online when the Db2 address spaces are up, and avoids the need for a manual process to start them.
There are 3 levels of detail that you can collect when a profile is exceeded. Both WARNING and EXCEPTION profiles can use DIAGLEVEL1 - DIAGLEVEL3. In summary, level 1 reports that a profile has been exceeded, level 2 reports which profile was exceeded, and level 3 reports all lower level information and any detailed thread or connection information relevant to the profile.
Message example for DIAGLEVEL2
The information in the DSNT773I and DSNT774I (DIAGLEVEL3) messages is much more useful for determining which AUTHIDs or IP addresses which are causing the profile thresholds to be exceeded.
The DSNT77xI messages are written to the MSTR joblog at five-minute intervals unless you specify _DIAGLEVEL3, in which case a message is written each time the threshold is exceeded. If the messages are too voluminous with _DIAGLEVEL3, you can start IFCID 406 to capture the detailed information and externalize the data in a record trace.
Message example for DIAGLEVEL3
You can also see evidence of exception processing for monitor profiles in the standard SMF 100 statistics records and in the output of various DISPLAY commands. For example, if a MONITOR THREAD exception hit occurs, then DBAT/CONN QUEUED-MAX ACTIVE is incremented (field QDSTQDBT), and CUR TYPE 1 INACTIVE DBATS (field QDSTQCIT) shows the current queue depth. The DSNL092I message from the DISPLAY DDF DETAIL command also shows this information in the INADBAT field. Thus, HWM TYPE 1 INACTIVE DBATS (field QDSTQMIT) contains the high-water mark for threads implicitly suspended due to the profile. For troubleshooting purposes, you must then check the HWM ACTIVE AND DISCON DBATS (field QDSTHWAT) to see if DBAT/CONN QUEUED-MAX ACTIVE was incremented due to hitting the MAXDBAT subsystem parameter or a profile.
Use caution when you create any type of EXCEPTION profile because queuing of threads can lead to transaction response time delays; and rejecting of threads or connections may give the impression that the Db2 subsystem is down, when in fact the denial of service was intentional.
Example of monitoring for any unique IP address which creates more than 100 connections.
Example of monitoring for the DDFPROD AUTHID to utilize more than 50 concurrent threads.
Regarding MONITOR IDLE THREADS, this is the only profile type where the threshold can be either greater than or less than the corresponding IDTHTOIN zparm. The idea is to use the zparm value to govern most idle thread timeouts, and the profiles can be used for exception cases where the timeout value must be increased to allow certain queries to complete. Be careful though! Any thread that qualifies for either a _WARING or _EXCEPTION profile you create is no-longer governed by the IDTHTOIN zparm. So, you must create an _EXCEPTION profile for every _WARNING profile entered into the DSN_PROFILE_TABLE. Otherwise the target of the profile can idle indefinitely.
Lastly, you must also consider how Db2 applies profiles that specify overlapping filtering criteria. For descriptions of the various rules, see Interactions between profiles for monitoring threads and connections.
For more about using profiles in Db2 for z/OS:
Adrian Burke is a member of the DB2 for z/OS development SWAT team and Paul McWilliams is an information developer for Db2 for z/OS.