This Db2 for z/OS News from the Lab blog entry was originally published on 2019-09-23.
By Adrian Burke and Paul McWilliams.
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.
- DSN_PROFILE_TABLE
- DSN_PROFILE_ATTRIBUTES
- DSN_PROFILE__HISTORY
- DSN_PROFILE_ATTRIBUTES_HISTORY
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_PROFILE_ATTRIBUTES table to specify the threshold values for the connections, threads, or timeout values. Once the rows are inserted issue the -START PROFILE command. You will receive notifications in the MSTR joblog from these profiles being exercised. Inserting the WARNING keyword into column ATTRIBUTE1 will simply issue a message, while the EXCEPTION keyword causes Db2 to automatically react to queue threads, deny new connections, or timeout an idle thread.
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.)
| Filter criteria |
Result |
| LOCATION (IP address or domain) |
- This is the only viable filter for MONITOR CONNECTIONS exceptions and will limit the total active/inactive connections at x.
- For MONITOR THREADS exceptions this will allow x number of concurrent active threads, and any threads x will queue until CONDBAT is reached. |
| PRDID (Product identifier): i.e. JCC04220 represents the V11.1 M1 FP1 Db2 connect level of the JDBC driver |
For MONITOR THREADS exceptions this will allow x number of concurrent active threads, and allow another x number of threads to queue, and the connection request for 2x+1 thread is rejected |
| AUTHID, ROLE, or both |
For MONITOR THREADS exceptions this will allow x number of concurrent active threads, and allow another x number of threads to queue, and the connection request for 2x+1 thread is rejected |
| One of CLIENT_APPLNAME, CLIENT_USERID, or CLIENT_WORKSTNNAME |
For MONITOR THREADS exceptions this will allow x number of concurrent active threads, and allow another x number of threads to queue, and the connection request for the 2x+1 thread is rejected |
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_PROFILE_ATTRIBUTES_HISTORY tables, which contains information about why the profile you inserted might have been rejected in the STATUS column. It is key to check the contents of the STATUS column anytime a profile is inserted or updated. If you update a profile, always remember to issue the -START PROFILE command to refresh Db2’s internal list of profiles it is using. You can turn individual profiles on and off by updating the PROFILE_ENABLED column of the DSN_PROFILE_TABLE. The -DIS PROFILE command can be used to see if profile monitoring in general is active with the DSNT753I message STATUS = ON.
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
DSNT772I -DB2B DSNLAGNT A MONITOR PROFILE EXCEPTION CONDITION OCCURRED 10 TIME(S) IN PROFILE ID=3
WITH PROFILE FILTERING SCOPE=AUTHID
WITH REASON=00E30507
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
DSNT774I -D2PN DSNLILNR SERVER DISTRIBUTED AGENT 521 WITH
LUWID=GA204031.A55B.000000000000
THREAD-INFO=-UNKNOWN:*:*:*:*:*:*:*
FOR LOCATION=::9.32.64.49
RECEIVED MONITOR CONNECTIONS
EXCEPTION DUE TO PROFILE ID=2
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.
INSERT INTO SYSIBM.DSN_PROFILE_TABLE (PROFILEID,LOCATION) VALUES (1,‘*');
INSERT INTO SYSIBM.DSN_PROFILE_ATTRIBUTES (PROFILEID, KEYWORDS, ATTRIBUTE1, ATTRIBUTE2)
VALUES (1,'MONITOR CONNECTIONS',‘WARNING',100);
Example of monitoring for the DDFPROD AUTHID to utilize more than 50 concurrent threads.
INSERT INTO SYSIBM.DSN_PROFILE_TABLE (PROFILEID,AUTHID) VALUES (2,‘DDFPROD');
INSERT INTO SYSIBM.DSN_PROFILE_ATTRIBUTES (PROFILEID, KEYWORDS, ATTRIBUTE1, ATTRIBUTE2)
VALUES (2,'MONITOR THREADS',‘WARNING',50);
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:
- Look for a future post in this blog, where we’ll discuss another use case: sending inbound connections to the correct collection, based on the APPLCOMPAT and function levels that the applications need in Db2 12 continuous delivery.
- See the documentation IBM Knowledge Center. For example, you can start from the following pages:
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.
#Db2forz/OS#db2z/os#Db2Znews