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

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

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.

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. 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.

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 TCP/IP 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'. The value is not case-sensitive, and profile matches can occur regardless of the case of the input values. If a domain name value can resolve to more than one IP address, Db2 uses only the first resolved IP address for profile filtering.	Monitoring remote connections by using profile tables
	MONITOR ALL CONNECTIONS	LOCATION only: specify '*', '::0', or '0.0.0.0'.	Monitoring remote connections by using profile tables (See step 3)
Secure TCP/IP connectivity	MONITOR product_type CONNECTIONS FOR SECURITY	LOCATION only: specify one of the following values: '*', '::0', or '0.0.0.0' (for all connections). A domain name that resolves to an IP address. An example fully qualified domain name is 'stlmvs1.svl.example.com'. IPV4 or IPV6 IP address. IPV4 or IPV6 subnet address.	Discovering and controlling secure TCP/IP connectivity with profile tables
Remote TCP/IP 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 (remote TCP/IP)	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
	FL 500 SPECIAL_REGISTER (local)	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.
Global variables	GLOBAL_VARIABLE (remote TCP/IP)	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
	FL 500 GLOBAL_VARIABLE (local)	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.
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
Package release behavior	FL 500 RELEASE_PACKAGE	For local threads and remote threads (DBATs), you can use the following filtering categories: AUTHID, ROLE, or both COLLID, PKGNAME, or both One of CLIENT_APPLNAME, CLIENT_USERID, or CLIENT_WRKSTNNAME You can also specify the following filtering categories for DBATs, but not for local threads: LOCATION only PRDID only The filtering values are not case-sensitive, and profiles can match regardless of the case of the input values.	Overriding the RELEASE(DEALLOCATE) option for packages 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	ACCEL_TABLE_THRESHOLD ACCEL_RESULTSIZE_THRESHOLD ACCEL_TOTALCOST_THRESHOLD	Contact IBM Support for the specific accelerator product.	See the accelerator product documentation.
Subsystem modeling	BPname 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

To create a profile, complete the following general steps:

1. 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.
2. 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.
3. 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.
4. 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.