Optimizing subsystem parameters for SQL statements by using 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.

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. In the PROFILEID column, specify a unique value or accept the generated default value. This value identifies the profile and the relationship between DSN_PROFILE_TABLE and DSN_PROFILE_ATTRIBUTES rows.
   2. 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 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.

      Other filtering columns must contain the null value.

   3. In the PROFILE_ENABLED column, specify 'Y' so that the profile is enabled when profiles are started.
      If the PROFILE_AUTOSTART subsystem parameter setting is YES, the profile starts when you issue a START PROFILE command or when Db2 starts.
   The row defines the filtering criteria of the profile.
2. Specify the subsystem parameters that you want to modify by inserting one ore more DSN_PROFILE_ATTRIBUTES table rows:
   1. Specify the PROFILEID value from the DSN_PROFILE_TABLE row that specifies the filtering criteria 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. In the KEYWORDS and ATTRIBUTEn columns, specify the actions for the profile:

   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.)
   IO WEIGHTING	Set ATTRIBUTE1 to DISABLE or ENABLE to specify how Db2 weights I/O and CPU cost during access path selection. ENABLE is the default value. (OPTIOWGT subsystem parameter. The OPTIOWGT subsystem parameter is deprecated.)
   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 in the range 3–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. (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.