SQL compiler registry variables in an optimization profile

Optimization profiles can have different registry variable values applied to a specific query statement or to many query statements used in an application.

Setting registry variables in an optimization profile can increase the flexibility you have in using different query statements for different applications. When you use the db2set command to set registry variables, the registry variable values are applied to the entire instance. In optimization profiles, the registry variable values apply only to the statements specified in the optimization profile. By setting registry variables in an optimization profile, you can tailor specific statements for applications without worrying about the registry variable settings of other query statements.

Only a subset of registry variables can be set in an optimization profile. The following registry variables can be set in an optimization profile:
  • DB2_ANTIJOIN
  • DB2_EXPLAIN_OPT
  • DB2_EXTENDED_OPTIMIZATION (Only the ON, OFF, IXOR and PRLSI [ON|INCR|OFF] values are supported)
  • DB2_INLIST_TO_NLJN
  • DB2_MINIMIZE_LISTPREFETCH
  • DB2_NEW_CORR_SQ_FF
  • DB2_OPT_MAX_TEMP_SIZE
  • DB2_OPTIMIZER_VERSION
  • DB2_REDUCED_OPTIMIZATION
  • DB2_RESOLVE_CALL_CONFLICT
  • DB2_SELECTIVITY
  • DB2_SELUDI_COMM_BUFFER
  • DB2_SORT_AFTER_TQ

Registry variables can be set at both the global level and statement level. If the registry variable is set at the global level, it uses the registry variable settings for all the statements in the optimization profile. If the registry variable is set at the statement level the setting for that registry variable applies only to that specific statement. If the same registry variable is set at both the global and statement levels, the registry variable value at the statement level takes precedence.

Syntax for setting registry variables

Each registry variable is defined and set in an OPTION XML element, with a NAME and a VALUE attribute, all of which are nested in a REGISTRY element. For example:
   <REGISTRY>
        <OPTION NAME='DB2_SELECTIVITY'  VALUE='YES'/>
        <OPTION NAME='DB2_REDUCED_OPTIMIZATION'  VALUE='NO'/>
   </REGISTRY>

To have OPTION elements apply to all statements in the application that uses this profile, include the REGISTRY and OPTION elements in the global OPTGUIDELINES element.

To have OPTION elements apply to just a specific SQL statement, include the REGISTRY and OPTION elements in the applicable statement-level STMTPROFILE element. Different STMTPROFILE elements can have different OPTION element settings.

The following example shows registry variable settings at the application and statement level: 

<?xml version="1.0" encoding="UTF-8"?>
<OPTPROFILE>

   <!--Global section --> 
   <OPTGUIDELINES>
      <!-- New: registry variables that apply to all SQL statements that
           use this profile -->
      <REGISTRY>
         <OPTION NAME='DB2_SELECTIVITY'  VALUE='YES'/>
         <OPTION NAME='DB2_REDUCED_OPTIMIZATION'  VALUE='NO'/>
      </REGISTRY>
   </OPTGUIDELINES>

  <!-- First statement level profile -->   
  <STMTPROFILE ID='S1'>
      <STMTKEY>
         <![CDATA[select t1.c1, count(*) from t1,t2 where t1.c1 = t2.c1 
                  group by t1.c1]]>
      </STMTKEY>
      <OPTGUIDELINES>
         <!-- New: registry variables that JUST applies to the above 
              SQL statement when using this profile -->
         <REGISTRY>
            <OPTION  NAME='DB2_REDUCED_OPTIMIZATION'  VALUE='NO_SORT_NLJOIN'/>
         </REGISTRY>
         <NLJOIN>
            <TBSCAN TABLE='T1'/>
            <TBSCAN TABLE='T2'/>
         </NLJOIN>
      </OPTGUIDELINES>
  </STMTPROFILE>

  <!-- Second statement level profile -->
  <STMTPROFILE ID='S2'>
      <STMTKEY><![CDATA[select * from T1 where c1 in( 10,20)]]></STMTKEY>
      <OPTGUIDELINES>
         <!-- New: registry variables that JUST applies to the above
              SQL statement when using this profile -->
         <REGISTRY>
            <OPTION  NAME='DB2_REDUCED_OPTIMIZATION'  VALUE='YES'/>
         </REGISTRY>
      </OPTGUIDELINES>
  </STMTPROFILE>

</OPTPROFILE>

Order of precedence

The example above sets the same registry variables in multiple places. In addition to these settings, the db2set command can also be used to set registry variables. Registry variables are applied in the following order of precedence, from highest to lowest. If both options 1 and 2 are specified, then only option 1 will be used. Otherwise, the combined effect of all options are used. If a particular registry variable is set using multiple options, then the setting in the lowest level will take precedence.
  1. Statement level optimization profile settings, which are defined in a statement-level optimization guideline
  2. Embedded optimization guideline.
  3. Overall optimization profile settings, which are defined in a global optimization guideline.
  4. Registry variables set by the db2set command.

The following examples indicate which registry variable settings are used for various SQL statements using the above optimization guidelines:

Variables in effect: DB2_REDUCED_OPTIMIZATION=NO, DB2_SELECTIVITY=YES 
select t1.c1 from t1
Variables in effect: DB2_REDUCED_OPTIMIZATION=NO_SORT_NLJOIN, DB2_SELECTIVITY=YES
select t1.c1, count(*) from t1,t2 where t1.c1 = t2.c1 group by t1.c1
Variables in effect: DB2_REDUCED_OPTIMIZATION=YES, DB2_SELECTIVITY=YES
select * from T1 where c1 in (10,20)
/* <OPTGUIDELINES>
     <REGISTRY>
       <OPTION NAME='DB2_REDUCED_OPTIMIZATION' VALUE='NO_SORT_NLJOIN'/>
       <OPTION NAME='DB2_EXTENDED_OPTIMIZATION' VALUE='ON'/>
     </REGISTRY>
   </OPTGUIDELINES> */
Variables in effect: DB2_REDUCED_OPTIMIZATION=NO_SORT_NLJOIN, DB2_EXTENDED_OPTIMIZATION=ON, DB2_SELECTIVITY=YES
select count(*) from T1 where c1 >= 67
/* <OPTGUIDELINES>
     <REGISTRY>
       <OPTION NAME='DB2_REDUCED_OPTIMIZATION' VALUE='NO_SORT_NLJOIN'/>
       <OPTION NAME='DB2_EXTENDED_OPTIMIZATION' VALUE='ON'/>
     </REGISTRY>
   </OPTGUIDELINES> */;

Explain facility

The explain facility captures information about all SQL compiler registry variables that affect data manipulation language (DML) compilation and writes this information to explain tables.

You can use the Explain facility to determine which registry variables are being used for a particular statement. Activate the Explain facility and run the query. Three strings in the ENVVAR sections indicate where the setting has come from:
  • [Global Optimization Guideline]
  • [Statement-level Optimization Guideline]
  • [Embedded Optimization Guidelines]
For example: 
   ENVVAR : (Environment Variable)
       DB2_EXTENDED_OPTIMIZATION = ON

   ENVVAR : (Environment Variable)
       DB2_EXTENDED_OPTIMIZATION = ON [Global Optimization  Guideline]

   ENVVAR : (Environment Variable)
       DB2_EXTENDED_OPTIMIZATION = ON [Statement-level Optimization Guideline]

   ENVVAR : (Environment Variable)
       DB2_EXTENDED_OPTIMIZATION = ON [Embedded Optimization Guidelines]
If the registry variables are set in different places, the registry variable with the highest precedence is the only one displayed in the explain output.