Monitoring and controlling Db2 with profile tables

You can create profiles to monitor and control various aspects of a Db2 subsystem in specific application contexts, especially for remote applications.

Before you begin

Begin program-specific programming interface information.

You must create a set of profile tables and related objects on the Db2 subsystem.

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

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.

DSN_PROFILE_TABLE rows define the filtering criteria for profiles, and DSN_PROFILE_ATTRIBUTES rows specify the action 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.

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 columns not in same filtering category. For a list of the filtering categories that you can specify for each type of profile, see the following table.

Table 1. Summary of uses for profiles, applicable filtering categories, and applicable KEYWORDS values
What the profile does Applicable filtering categories in DSN_PROFILE_TABLE columns KEYWORDS column values in DSN_PROFILE_ATTRIBUTES
Monitoring remote connections to Db2 LOCATION only (IP address or domain name )
  • Start of changeMONITOR ALL CONNECTIONSEnd of change
  • MONITOR CONNECTIONS
Monitoring threads
- or -
Monitoring idle threads
  • LOCATION only
  • PRDID only
  • AUTHID, ROLE, or both.
  • COLLID, PKGNAME, or both
  • One of CLIENT_APPLNAME, CLIENT_USERID, or CLIENT_WORKSTNNAME
  • Start of changeMONITOR ALL THREADSEnd of change
  • MONITOR THREADS
  • MONITOR IDLE THREADS
Start of changeSetting global variables for remote applications End of change Start of change
  • LOCATION only
  • PRDID only
  • AUTHID, ROLE, or both.
  • COLLID, PKGNAME, or both
  • One of CLIENT_APPLNAME, CLIENT_USERID, or CLIENT_WORKSTNNAME
End of change
Start of changeGLOBAL_VARIABLEEnd of change
Setting special register values for remote locations
  • LOCATION only
  • PRDID only
  • AUTHID, ROLE, or both.
  • COLLID, PKGNAME, or both
  • One of CLIENT_APPLNAME, CLIENT_USERID, or CLIENT_WORKSTNNAME
SPECIAL_REGISTER
Setting optimization parameters for specific applications PLANNAME='*', COLLID, and PKGNAME (specify all three)
  • 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 specific accelerator product.
  • ACCEL_TABLE_THRESHOLD
  • ACCEL_RESULTSIZE_THRESHOLD
  • ACCEL_TOTALCOST_THRESHOLD
Evaluating a dynamic SQL query for acceleration Supported by profiles with a global scope and with the following combinations:
  • None (as a global scope)
  • AUTHID and LOCATION
  • PLANNAME, COLLID, and PKGNAME
ACCEL_NAME_EXPLAIN

Procedure

To create a profile, complete the following general steps:

  1. Insert rows in the SYSIBM.DSN_PROFILE_TABLE 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 filtering categories column in Table 1. Because the filtering criteria must be from a single valid filtering category, every valid DSN_PROFILE_TABLE row contains null values in some columns.
      Tip: If you create multiple profiles with matching filtering criteria, Db2 applies only one profile from each filtering category, based on a specific order of precedence. Also, exact values take precedence over values that use an asterisk (*) wildcard. However, profiles from different filtering categories can all apply. For more about these rules, see How Db2 applies multiple matching profiles for threads and connections.
    For descriptions the columns, see DSN_PROFILE_TABLE profile table.
  2. Insert rows in the SYSIBM.DSN_PROFILE_ATTRIBUTES table to specify the actions that Db2 takes when processes or applications match the filtering criteria specified in DSN_PROFILE_TABLE.
    1. Insert a PROFILEID that matches the PROFILEID value is DSN_PROFILE_TABLE row for this profile.
    2. Insert a KEYWORDS value to specify the action of the profile, as described in Table 1.
    3. Insert values in the appropriate ATTRIBUTEn columns for the type of profile that you are creating. For instructions, follow the link for the type of profile that you are creating in the in Profile action column in Table 1.
    For descriptions of the columns, see DSN_PROFILE_ATTRIBUTES profile table.
  3. Issue START PROFILE commands to active the profiles, as described in Starting and stopping profiles.
    End program-specific programming interface information.