Profiles for monitoring and controlling Db2 for z/OS® subsystems

A profile is a set of criteria that identifies a particular context on a Db2® subsystem. Examples include threads, connections, or SQL statements that have particular attributes.

Important: Start of changeIf the value of the PROFILE_AUTOSTART subsystem parameter is set to YES, the START PROFILE command is issued automatically as part of Db2 start processing.End of change

Overview of uses for profiles

You can create profiles to define filtering scopes for processes within Db2. The filtering for each profile is controlled by the column values in the DSN_PROFILE_TABLE. You can also specify actions for Db2 to take when a process, such as a SQL statement, thread, or connection meets the criteria of the profile. The actions are specified by the column values in the DSN_PROFILE_ATTRIBUTES table. The actions that you can specify include:
  • Monitoring threads and connections
  • Setting or disabling certain subsystem parameters for particular SQL statements
  • Specifying subsystem properties when modeling a production environment on a test system
  • Setting thresholds for query acceleration
  • Start of changeEvaluating a dynamic SQL query for accelerationEnd of change
  • Start of changeSetting the value of a special registerEnd of change

Many of these actions are not closely related. The rules for how to define the scope of the profile depend on the action that the profile defines. Certain combinations of filtering columns are either required or accepted only for certain KEYWORDS values. The following table summarized these relationships. For detailed information about the accepted filtering columns and KEYWORDS values, see the specific information for each action.

Table 1. Summary of uses for profiles, applicable filtering columns, and applicable KEYWORDS values
Profile action Applicable filtering columns Applicable KEYWORDS values
Monitoring the use of system resources by remote applications, including remote threads and connections Start of change1End of change Any of the following combinations with particular rules for certain KEYWORDS values:
  • LOCATION only
  • PRDID only
  • AUTHID, ROLE, or both.
  • COLLID, PKGNAME, or both
  • One of CLIENT_APPLNAME, CLIENT_USERID, CLIENT_WORKSTNNAME
  • MONITOR CONNECTIONS
  • MONITOR THREADS
  • MONITOR IDLE THREADS
Setting or disabling optimization parameters for SQL statements All of the following columns:
  • PLANNAME set to '*'
  • COLLID
  • PKGNAME
  • MIN STAR JOIN TABLES
  • NPAGES THRESHOLD
  • STAR JOIN
Modeling a production system on a test system None. Profiles for this purpose have a global scope on the test subsystem.
  • BPname
  • MAX_RIDBLOCKS
  • SORT_POOL_SIZE
Setting thresholds for query acceleration Contact IBM® Support for the accelerator product.
  • ACCEL_TABLE_THRESHOLD
  • ACCEL_RESULTSIZE_THRESHOLD
  • ACCEL_TOTALCOST_THRESHOLD
Start of changeEvaluating a dynamic SQL query for accelerationEnd of change Start of changeSupported by profiles with a global scope and those with the following combinations:
  • None (as a global scope)
  • AUTHID and LOCATION
  • PLANNAME, COLLID, and PKGNAME
End of change
Start of changeACCEL_NAME_EXPLAINEnd of change
Start of changeSetting the value of a special register for remote applications Start of change1End of changeEnd of change Start of changeAny of the following combinations with particular rules for certain KEYWORDS values:
  • LOCATION only
  • PRDID only
  • AUTHID, ROLE, or both.
  • COLLID, PKGNAME, or both
  • One of CLIENT_APPLNAME, CLIENT_USERID, CLIENT_WORKSTNNAME
End of change
Start of changeSPECIAL_REGISTEREnd of change
Notes:
  1. Start of changeProfiles of this type apply only to remote applications.End of change

The scope of a profile

The scope of a profile, the defined context in which Db2 applies the actions specified by the profile, are stored as rows in the SYSIBM.DSN_PROFILE_TABLE table. Rows in the SYSIBM.DSN_PROFILE_ATTRIBUTES table control the actions that Db2 applies when a process meets the criteria defined by the profile. The values of the PROFILEID columns of each table associate each profile with the corresponding actions for that profile. The PROFILE_ENABLED column indicates whether Db2 activates the profile when you start monitoring.

How you define a profile depends on the context that you want to define and the actions that you want Db2 to perform. A valid row in SYSIBM.DSN_PROFILE_TABLE always contains null values in some columns. Which of the following columns that you define depend on the purpose of the profile:
  • AUTHID
  • COLLID
  • LOCATION
  • PKGNAME
  • PLANNAME
  • PRDID
  • ROLE
  • CLIENT_APPLNAME
  • CLIENT_USERID
  • CLIENT_WRKSTNNAME

For profiles that specify values for optimizing subsystem parameters, the following table shows combinations of criteria that create valid profiles. Other combinations are not valid.

Table 2. Categories and columns used to specify valid profiles that specify subsystem parameters
Filtering category Columns to specify
Collection identifier and package name Specify all of the following columns:
  • PLANNAME (specify only '*')
  • COLLID
  • PKGNAME

For profiles that define contexts for the use of system resources by threads and connections, the following combinations of criteria create valid profiles. Other combinations are not valid.

Table 3. Categories and columns used to specify valid profiles for monitoring system resources
Filtering category Columns to specify
Client IP address or domain name Specify only the LOCATION column. The value can be an IP address or domain name.

This category is the only accepted filtering criteria for profiles that specify the MONITOR CONNECTIONS.

Client product identifier Specify only the PRDID column.
Role or authorization identifier Specify one or all of the following columns:
  • ROLE
  • AUTHID

Profiles that specify both ROLE and AUTHID take precedence over profiles that specify only one value. Profiles that specify only ROLE take precedence over profiles that specify only AUTHID

Collection identifier or package name Specify only one or all of the following columns:
  • COLLID
  • PKGNAME

Profiles that specify both COLLID and PKGNAME take precedence over profiles that specify only one value. Profiles that specify only COLLID take precedence over profiles that specify only PKGNAME

Location name, or location alias Specify only the location name or location alias in LOCATION column.

This category applies only to profiles that specify MONITOR THREADS and MONITOR IDLE THREADS.

Client application name, user ID, or workstation ID. Specify only one of the following columns:
  • CLIENT_APPLNAME
  • CLIENT_USERID
  • CLIENT_WRKSTNNAME