Optimizing subsystem parameters

You can set certain subsystem parameter values to help optimize how Db2 processes SQL queries.

Procedure

Begin program-specific programming interface information.To change these values subsequently, use one of the following approaches:

  • Use the SET SYSPARM command to change the parameter for the entire subsystem.
  • Create statement-level optimization parameters that apply to matching SQL statements in a specified context.
    This method is preferred over the use of profile tables, and provides the ability to specify additional options.
  • Use profile tables to specify subsystem parameter values to be used in the context of particular queries.
    Only certain subsystem parameters can be modified by profiles.

Results

Db2 uses the following rules to determine the precedence and scope for setting subsystem parameter values:

  1. Values that are specified by statement-level or package optimization parameters. However, the existence of a valid PLAN_TABLE hint that applies to the statement prevents these values from being applied.
  2. Values specified by profile tables. These parameters apply to all statements that are meet the criteria that are specified profile, and the parameters are in effect only for the time in which that profile is enabled.
  3. System-level parameter values that are set by panels or the SET SYSPARM command. These parameters apply to the entire subsystem until the values are changed. End program-specific programming interface information.