With the ever increasing number of features and functions being added to Db2 for z/OS it’s easy to overlook the finer points of some of those features. Take in point the “Profile Tables”.
You can create profiles to selectively control groups of distributed (DBAT) threads and connections, set thresholds for query acceleration (not covered in this article) or provide override properties to model your test subsystem to reflect the computing power of your production environment.
You create profiles to define filtering scopes for processes within Db2 by inserting rows into the DSN_PROFILE_TABLE. You also specify actions for Db2 to take when a process, such as an SQL statement, thread, or connection meets the criteria of the profile by inserting related rows into the DSN_PROFILE_ATTRIBUTES table. You can start and stop your profiles using profile commands.
Monitoring and controlling remote connections.
The CONDBAT subsystem parameter sets the maximum number of concurrent inbound DDF connections that are allowed. You may have a group of connections connecting through a specific IP address or Domain name that you want to apply a smaller limit to. First insert a row in the DSN_PROFILE_TABLE table to create the profile and specify the filtering scope. You can only specify an IP address or domain name value in the LOCATION column for this type of profile. Other combinations of criteria are not accepted. Next insert a row in the DSN_PROFILE_ATTRIBUTES table to specify the monitoring function type in the KEYWORD column, the monitoring action in ATTTRIBUTE1 (warning or exception), and the thresholds IN ATTRIBUTE2.
INSERT INTO SYSIBM.DSN_PROFILE_TABLE (PROFILEID, LOCATION, ENABLE_PROFILE)
VALUES (30,'p390.themisinc.com','Y');
INSERT INTO SYSIBM.DSN_PROFILE_ATTRIBUTES (PROFILEID, KEYWORDS, ATTRIBUTE1, ATTRIBUTE2)
VALUES (30, 'MONITOR CONNECTIONS', 'WARNING', 100);
The possible values in ATTRIBUTE1 has two parts, the action and the level diagnosis information contained in the associated DSNT772I console message issued by Db2. WARNING issues a console message about every 5 minutes depending on the specified diagnosis level. EXCEPTION issues the messages that are specified by the diagnosis level and rejects any new incoming remote connection requests until the number of connection requests fall below the threshold in attribute 2.
Insert values in the ATTRIBUTE2 column of to specify the threshold that the monitor uses, this value must be less than the CONDBAT subsystem parameter.
Monitoring and controlling remote threads.
Similar to monitoring and controlling connections but at a more granular level is the capability to set limits to groups of active distributed database access threads (DBATs). The MAXDBAT subsystem parameter sets the maximum number of database access threads (DBATs) that are allowed to be concurrently active for your subsystem. You may have a group of threads that you want to apply a smaller limit to and optionally abort if they reach the limit you set. First insert a row in the DSN_PROFILE_TABLE table to create the profile and specify the filtering scope, for thread monitoring the scope can be LOCATION only; PRDID only; AUTHID, ROLE, or both; COLLID, PKGNAME, or both; or one of CLIENT_APPLNAME, CLIENT_USERID, CLIENT_WORKSTNNAME.
Next insert a row in the DSN_PROFILE_ATTRIBUTES table to specify the monitoring function type in the KEYWORD column, the monitoring action in ATTTRIBUTE1 (warning or exception), and the thresholds in ATTRIBUTE2. Finally, issue the –START PROFILE command to load the profile tables for the enabled profiles.
INSERT INTO SYSIBM.DSN_PROFILE_TABLE (PROFILEID, COLLID, PROFILE_ENABLED)
VALUES (21,'DB1029CL','Y');
INSERT INTO SYSIBM.DSN_PROFILE_ATTRIBUTES (PROFILEID, KEYWORDS, ATTRIBUTE1, ATTRIBUTE2)
VALUES (21, 'MONITOR THREADS', 'EXCEPTON_DIAGLEVEL2' , 100);
Actions taken depend on the filtering scope when thresholds for EXCEPTION profiles are exceeded.
- IP Address or domain name: Thread is queued until another server thread becomes available and the number of concurrent active threads falls below the profile exception threshold. The connection that is associated with the thread remains open and is not terminated.
- Product identifier, role, authorization identifier, or server location name: Thread is queued and suspended until another server thread becomes available and the number of concurrent active threads falls below the profile exception threshold. Db2 suspends only as many threads as the profile exception threshold. When the total number of the threads being queued and suspended exceeds the profile exception threshold value for a particular profile ID, the Db2 server fails subsequent connection requests and returns SQLCODE -30041 to the client.
- Collection identifier, package name, client user name, client application name, or client workstation name: Thread is queued and suspended until another server thread becomes available and the number of concurrent active threads goes below the profile exception threshold. The connection that is associated with the thread remains open and is not terminated. Db2 suspends only the number of the threads up to the profile exception threshold. Once the total of the threads being queued and suspended exceeds the profile exception threshold value for a particular profile ID, Db2 fails the subsequent SQL statement and returns SQLCODE -30041 to the client.
Monitoring and controlling Idle threads.
Similar to monitoring remote active threads you can set profile limits for the idle thread timeout subsystem parameter (IDTHTOIN) to enable longer or shorter idle wait times for certain threads, without increasing the system-wide limit for idle thread timeouts. Unlike MAXDBAT and CONDBAT overrides the IDTHTOIN override value does not have to be less than the subsystem value. A zero value means that matching threads are allowed to remain idle indefinitely. For example, for profile 21 (plan level monitoring set above) we prevent the idle thread system parameter from terminating the threads.
INSERT INTO SYSIBM.DSN_PROFILE_ATTRIBUTES (PROFILEID, KEYWORDS, ATTRIBUTE1, ATTRIBUTE2)
VALUES (21, 'MONITOR IDLE THREADS', 'WARNING' , 0);
The following example will monitor idle threads connecting via the db2jcc_application and if the idle thread time reaches 60 seconds the thread will be rolled back and an exception triggered. The advantage here is if you have idle threads consistently staying on the active thread chain holding resources they forgot to free, for example held cursors they did not close before they committed or declared temporary tables. The rollback will destroy held cursors, kept dynamic statements, and declared temporary tables that were created in the transaction.
INSERT INTO SYSIBM.DSN_PROFILE_TABLE (PROFILEID, CLIENT_APPLNAME, ENABLE_PROFILE)
VALUES (35,'db2jcc_application','Y');
INSERT INTO SYSIBM.DSN_PROFILE_ATTRIBUTES (PROFILEID, KEYWORDS, ATTRIBUTE1, ATTRIBUTE2)
VALUES (35, 'MONITOR IDLE THREADS', 'EXCEPTON_ROLLBACK_DIAGLEVEL2', 50);
Setting Special Registers and Built-in Global Variables
You can create profiles to sets certain special register values or Db2 built-in global variables for remote applications that meet the criteria that are defined in the profile. If more than one profile applies to a thread or connection, the profiles are evaluated in the order they were defined. Db2 automatically applies the special register or global variable value to the Db2 process when the connection is initially established and whenever a connection is reused. For example, for the dynamic SQL processes controlled by client application profile 35 we want to set the application compatibility, allow for CP parallelism and set the get archive global variable.
INSERT INTO SYSIBM.DSN_PROFILE_ATTRIBUTES (PROFILEID,KEYWORDS,ATTRIBUTE1)
VALUES(35,'SPECIAL_REGISTER', 'SET CURRENT APPLICATION COMPATIBILITY = ''V12R1500'' );
INSERT INTO SYSIBM.DSN_PROFILE_ATTRIBUTES (PROFILEID,KEYWORDS,ATTRIBUTE1)
VALUES(35,'SPECIAL_REGISTER', 'SET CURRENT DEGREE = ANY') ;
INSERT INTO SYSIBM.DSN_PROFILE_ATTRIBUTES (PROFILEID,KEYWORDS,ATTRIBUTE1)
VALUES(35,'GLOBAL_VARIABLE', 'SET SYSIBMADM.GET_ARCHIVE = ''Y'' ') ;
Setting subsystem overrides
You can create profiles to specify that Db2 uses particular subsystem parameters when executing SQL statements that meet the criteria defined in the profile. The scope can be PLANNAME *, COLLID and PKGNAME. If all three entries are an * then the profile has a global scope. The overrides you can provide are NPGTHRSH (KEYWORD: NPAGES THRESHOLD), OPTIOWGT (deprecated), STARJOIN (KEYWORD: STAR JOIN) and SJTABLES (KEYWORD: MIN STAR JOIN TABLES).
Application testing on test subsystems with different parameters and configurations than production can result in different access paths. These differences can cause performance problems to remain undetected on the test system, only to be discovered in production. You can specify that Db2 model your production configuration and parameters in your test subsystem. The key parameters and configuration details Db2 uses for access path selection are listed here.
Modeling involves:
1. First gather your production environment information. (see Db2 documentation for suggestions)
2. Update your test subsystem parameter load module (job DSNTIJUZ) and add:
- SIMULATED_CPU_SPEED to match production
- SIMULATED_CPU_COUNT to match production
3. Create a profile and insert Attributes to match production for:
- RID pool size
- Sort pool size
- Buffer pool settings
Examples:
-- Create a global scope profile
INSERT INTO SYSIBM.DSN_PROFILE_TABLE (PROFILEID) VALUES (99);
-- Insert properties for your global scope profile
INSERT INTO SYSIBM.DSN_PROFILE_ATTRIBUTES (PROFILEID, KEYWORDS, ATTRIBUTE1, ATTRIBUTE2)
VALUES (99, 'BP1',NULL, 250000);
-- Repeat for each buffer pool
INSERT INTO SYSIBM.DSN_PROFILE_ATTRIBUTES (PROFILEID,KEYWORDS,ATTRIBUTE1,ATTRIBUTE2)
VALUES (99, 'BP2',NULL, 250000);
The example buffer pool settings do not actually change the physical buffer pool size in your test subsystem, the values you supply are only used for access path selection.
Starting and Stopping the Profile
Prior to Db2 12 you must explicitly start the profile each time Db2 starts. This is easily done with the command:
-START PROFILE
Beginning in Db2 12 you can set the subsystem parameter PROFILE_AUTOSTART to do this automatically. When you make changes to the profile tables they will not become effective until you issue the -START PROFILE command.
Use the following command to stop the profile:
-STOP PROFILE
Reference
For more information about the use of profiles check out the following link:
https://www.ibm.com/support/knowledgecenter/en/SSEPEK_12.0.0/usrtab/src/tpc/db2z_profiletables.html