Optimizing subsystem parameters for SQL statements with profile tables

You can create profiles to specify that Db2 applies specific subsystem parameter settings when executing SQL statements that meet the criteria defined in the profile.

Before you begin

You must create a set of profile tables and related objects on the Db2 subsystem. For a list of objects and how to create them, see Profile tables.

This task describes one of several uses for profile tables. For an overview of how to use profile tables and a summary of the different uses, see Monitoring and controlling Db2 with profile tables.

About this task

You can use profile tables to modify the following subsystem parameters for statements from specific applications:
  • NPGTHRSH
  • OPTIOWGT
  • STARJOIN
  • SJTABLES

Procedure

To use profiles to modify the subsystem parameters that Db2 uses to execute specific SQL statements:

  1. Create a profile by inserting rows in DSN_PROFILE_TABLE.
    1. Insert a unique value in the PROFILEID column, or accept the generated default value. This value identifies the profile and the relationship between DSN_PROFILE_TABLE and DSN_PROFILE_ATTRIBUTES rows.
    2. Insert values to specify the filtering criteria of the profile.
      You must specify each of the following filtering columns:
      • PLANNAME='*'
      • COLLID
      • PKGNAME
      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.

      Other filtering columns must contain the null value.

    3. Insert 'Y' in the PROFILE_ENABLED column to specify that the profile is enabled when profiles are started.
      The profile starts when you issue a START PROFILE command or when Db2 starts, if the PROFILE_AUTOSTART subsystem parameter setting is YES.
    The row defines the filtering criteria of the profile.
  2. Specify the subsystem parameter that you want to modify by inserting a SYSIBM.DSN_PROFILE_ATTRIBUTES row:
    1. Insert the PROFILEID value from the related DSN_PROFILE_TABLE row that specifies the filtering criteria for this profile.
    2. Insert KEYWORDS and ATTRIBUTEn column values:
    Table 1. Values for the KEYWORDS and ATTRIBUTEn columns of SYSIBM.DSN_PROFILE_ATTRIBUTES
    KEYWORDS column ATTRIBUTEn columns
    NPAGES THRESHOLD Set ATTRIBUTE2 to an integer, 0 or greater, to specify the pages threshold for index access. (NPGTHRSH subsystem parameter.)
    STAR JOIN Set ATTRIBUTE1 to DISABLE or ENABLE to specify whether Db2 uses star join processing. (STARJOIN subsystem parameter)
    MIN STAR JOIN TABLES Set ATTRIBUTE2 to an integer between 3 and 225 to specify the minimum number of tables for star join processing. (SJTABLES subsystem parameter.)
  3. Load or reload the profile tables into memory by issuing a START PROFILE command, as described in Starting and stopping profiles.

Example

Suppose that you insert the row in the following table into SYSIBM.DSN_PROFILE_ATTRIBUTES:
Table 2. Sample data in SYSIBM.DSN_PROFILE_ATTRIBUTES.
PROFILEID KEYWORDS ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE TIMESTAMP REMARKS
17 STAR JOIN DISABLE     2005-06-23...  
This row specifies that Db2 is to disable star join processing for all statements that match the filtering criteria specified in the row with the PROFILEID='17'. DSN_PROFILE_TABLE.