Monitoring and controlling Db2 by using profile tables

You can create profiles to monitor and control various aspects of a Db2 subsystem in specific application contexts, especially for remote applications that use TCP/IP connectivity to access Db2 for z/OS.

Before you begin

Begin program-specific programming interface information.

If a set of profile tables and related objects do not already exist on the Db2 subsystem, you must create them.

The profile tables and related indexes are created when you run job DSNTIJSG during Db2 installation, as described in Job DSNTIJSG. A complete set of profile tables and related indexes includes the following tables and indexes:

SYSIBM.DSN_PROFILE_TABLE
SYSIBM.DSN_PROFILE_HISTORY
SYSIBM.DSN_PROFILE_ATTRIBUTES
SYSIBM.DSN_PROFILE_ATTRIBUTES_HISTORY
SYSIBM.DSN_PROFILE_TABLE_IX_ALL
SYSIBM.DSN_PROFILE_TABLE_IX2_ALL
SYSIBM.DSN_PROFILE_ATTRIBUTES_IX_ALL

Start of change If you plan to use TCP/IP domain names for profile filtering, you must enable the database services address space (ssnmDBM1) to access TCP/IP services. See Enabling Db2 to access TCP/IP services in z/OS UNIX System Services. End of change

About this task

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

The DSN_PROFILE_TABLE rows define the filtering criteria for profiles, and DSN_PROFILE_ATTRIBUTES rows specify the actions that Db2 takes for processes that meet the filtering criteria of each profile. The PROFILEID values in one or more DSN_PROFILE_ATTRIBUTES rows associate them with a unique DSN_PROFILE table row with the same PROFILEID value.

Db2 supports a maximum of 4096 active DSN_PROFILE_TABLE rows.

The filtering criteria that you specify for each profile depend on its purpose. Each DSN_PROFILE_TABLE row specifies filtering criteria from a single filtering category, which is a set of one or more specific DSN_PROFILE_TABLE columns. The row must contain null values in other filtering columns not in same category. For a list of the filtering categories that you can specify for each type of profile, see the following table.

Tip: Insert only a single DSN_PROFILE_TABLE row for any specific set of filtering criteria. Start of change

If multiple DSN_PROFILE_TABLE rows specify the same filtering criteria, only the newest is row is accepted when you start the profiles, and the other duplicates are rejected. End of change

Table 1. Summary of uses for profiles, applicable filtering categories, and applicable KEYWORDS values
What the profile controls	DSN_PROFILE_ATTRIBUTES.KEYWORDS values	Applicable DSN_PROFILE_TABLE filtering categories	Where to find more information
Remote connections	MONITOR CONNECTIONS	LOCATION only: specify an IP address or a domain name that resolves to an IP address. An example fully qualified domain name is 'stlmvs1.svl.example.com'. An example fully qualified domain name is 'stlmvs1.svl.example.com'. The value is not case-sensitive, and profile matches can occur regardless of the case of the input values.	Monitoring remote connections by using profile tables
Remote connections	MONITOR ALL CONNECTIONS	LOCATION only: specify '*', '::0', or '0.0.0.0'.	Monitoring remote connections by using profile tables (see step 3)
Remote threads	MONITOR THREADS	LOCATION only PRDID only AUTHID, ROLE, or both COLLID, PKGNAME, or both One of CLIENT_APPLNAME, CLIENT_USERID, or CLIENT_WRKSTNNAME The filtering values are not case-sensitive, and profiles can match regardless of the case of the input values.	Monitoring threads by using profile tables
	MONITOR IDLE THREADS	LOCATION only PRDID only AUTHID, ROLE, or both COLLID, PKGNAME, or both One of CLIENT_APPLNAME, CLIENT_USERID, or CLIENT_WRKSTNNAME The filtering values are not case-sensitive, and profiles can match regardless of the case of the input values.	Monitoring remote idle threads by using profile tables
	MONITOR ALL THREADS	LOCATION only: specify '*', '::0', or '0.0.0.0'.	Monitoring threads by using profile tables (see step 3)
Special registers	SPECIAL_REGISTER	LOCATION only PRDID only AUTHID, ROLE, or both COLLID, PKGNAME, or both One of CLIENT_APPLNAME, CLIENT_USERID, or CLIENT_WRKSTNNAME The filtering values are not case-sensitive, and profiles can match regardless of the case of the input values.	Setting special registers by using profile tables
Global variables	GLOBAL_VARIABLE	LOCATION only PRDID only AUTHID, ROLE, or both COLLID, PKGNAME, or both One of CLIENT_APPLNAME, CLIENT_USERID, or CLIENT_WRKSTNNAME The filtering values are not case-sensitive, and profiles can match regardless of the case of the input values.	Setting built-in global variables by using profile tables
Lock sharing for RRS connections	SHARE_LOCKS	LOCATION only PRDID only AUTHID, ROLE, or both COLLID, PKGNAME, or both One of CLIENT_APPLNAME, CLIENT_USERID, or CLIENT_WRKSTNNAME The filtering values are not case-sensitive, and profiles can match regardless of the case of the input values.	Sharing locks for stored procedures that invoke transactions in RRS contexts by using profile tables
Optimization subsystem parameters	MIN STAR JOIN TABLES NPAGES THRESHOLD STAR JOIN	PLANNAME='*', COLLID, and PKGNAME (specify all three)	Optimizing subsystem parameters for SQL statements by using profile tables
Evaluating queries for acceleration	ACCEL_NAME_EXPLAIN	None (global scope) AUTHID and LOCATION PLANNAME, COLLID, and PKGNAME	See the accelerator product documentation.
Query acceleration thresholds	ACCEL_TABLE_THRESHOLD ACCEL_RESULTSIZE_THRESHOLD ACCEL_TOTALCOST_THRESHOLD	Contact IBM Support for the specific accelerator product.	See the accelerator product documentation.
Subsystem modeling	BP`name` MAX_RIDBLOCKS SORT_POOL_SIZE	None. Profiles for this purpose have a global scope on the test subsystem.	Modeling a production environment on a test subsystem

Procedure

To create a profile, complete the following general steps:

In the SYSIBM.DSN_PROFILE_TABLE, insert rows to define the filtering criteria for the profiles.
1. Specify a unique PROFILEID value to identify the profile.
2. In the other columns, specify the filtering criteria for the profile.
  The filtering criteria columns that you specify must be from a single filtering category for the type of profile that you are creating. See the Applicable DSN_PROFILE_TABLE filtering categories column in the preceding table. Because the filtering criteria must be from a single valid filtering category, every valid DSN_PROFILE_TABLE row contains null values in some columns.
  Also, the same filtering column and value must not already used by an existing profile.
  
  Tip: If you create multiple profiles with overlapping filtering criteria, Db2 applies only one profile from each filtering category, based on a specific order of precedence. If multiple DSN_PROFILE_TABLE rows specify the same filtering criteria, only the newest is row is accepted when you start the profiles, and the other duplicates are rejected. Also, exact values take precedence over values that use an asterisk (*) wildcard. However, profiles from different filtering categories can all apply. For more information about these rules, see How Db2 applies multiple matching profiles for threads and connections.
For column descriptions, see DSN_PROFILE_TABLE profile table.
In SYSIBM.DSN_PROFILE_ATTRIBUTES table, insert rows to specify the actions that Db2 takes when processes or applications match the filtering criteria specified in DSN_PROFILE_TABLE.
1. Specify the same PROFILEID value from the DSN_PROFILE_TABLE row for this profile.
  
  Tip: Use the same PROFILEID value for any DSN_PROFILE_ATTRIBUTES rows that require the same filtering criteria. If multiple DSN_PROFILE_TABLE rows contain exactly matching filtering criteria, only the newest duplicate row is accepted when you start the profiles, and the others are rejected and disabled.
2. Specify a KEYWORDS value to specify the action of the profile, as described in the preceding table.
3. Specify values in the appropriate ATTRIBUTEn columns for the type of profile that you are creating. For instructions, see the "Where to find more information" column in the preceding table.
For column descriptions, see DSN_PROFILE_ATTRIBUTES profile table.
Load or reload the profile tables into memory by issuing a START PROFILE command. (For best results, do not issue a STOP PROFILE command when you add or modify existing profiles. Use the STOP PROFILE command only if you intend to disable all existing profiles.) For more information, see Starting and stopping profiles.
Check the status of all newly added profiles in the STATUS columns of the DSN_PROFILE_HISTORY and DSN_PROFILE ATTRIBUTES_HISTORY tables.
Successful completion of the START PROFILE command does not imply that all profiles started successfully. If the STATUS column of either history table contains a value that does not start with 'ACCEPTED', further action is required to enable the profile or the keyword action.