Query compiler variables

• Operating system: All
• Default=NO in an ESE environment, Default=EXTEND in a non-ESE environment, Values: YES, NO, or EXTEND

• When this variable is set to YES, the optimizer searches for opportunities to transform NOT EXISTS subqueries into anti-joins which can be processed more efficiently by DB2.

  When this variable is set to NO, the optimizer limits the opportunities to transform NOT EXISTS subqueries into anti-joins.

  When this variable is set to EXTEND, the optimizer searches for opportunities to transform both NOT IN and NOT EXISTS subqueries into anti-joins.

  Changes to this variable can take effect immediately for all future compiled SQL statements if the db2set command is issued with the -immediate parameter. You do not need to restart the instance.

• Operating system: All
• Default=NO, Values: YES or NO

• When set to YES, this registry variable enables deferred prepare semantics such that all untyped parameter markers used in PREPARE statements will derive their data types and length attributes based on the input descriptor associated with the subsequent OPEN or EXECUTE statements. This allows untyped parameter markers to be used in more places than was supported previously.

  Note: Setting DB2_DEFERRED_PREPARE_SEMANTICS to YES may cause unintended effects or results. In cases where the data type in the input descriptor is different from the data type derived using the rules for "Determining data types of untyped expressions," the following can occur:

  • The query performance is degraded because of the additional cast operation.
  • The query fails because a data type cannot be converted.
  • The query can return different results.

  For example, assume a table t1, with a column char_col which is defined as VARCHAR(10) with values '1', '100', '200', 'xxx'. A user runs the following query:

  select * from t1 where char_col = ?

  If the data type of the input parameter is INTEGER, and deferred prepare is being used, the column char_col is cast to numeric. However, the query fails because one of the rows in the table contains non-numeric data ('xxx') which cannot be converted to a numeric value.
  When set to YES_DBCS_GRAPHIC_TO_CHAR, this registry variable specifies that parameter markers are to be typed as VARCHAR instead of VARGRAPHIC. The DB2_DEFERRED_PREPARE_SEMANTICS registry variable has this setting implicitly if all of the following are true:

  • DB2_DEFERRED_PREPARE_SEMANTICS is not set (that is, set to NULL).
  • The DB2_COMPATIBILITY_VECTOR registry variable is set to ORA, MYS, or MSS.
  • You are in a double-byte character set (DBCS) environment.

  The DB2_DEFERRED_PREPARE_SEMANTICS registry variable must be set prior to issuing the db2start command.

  This registry variable is only recommended for Unicode and SBCS databases.

• Operating system: All
• Default=NO, Values: YES or NO
• In some situations, the SQL and XQuery compiler can rewrite an IN list predicate to a join. For example, the following query:

      SELECT *
       FROM EMPLOYEE
       WHERE DEPTNO IN ('D11', 'D21', 'E21')
  	  

  could be written as:

      SELECT *
       FROM EMPLOYEE, (VALUES 'D11', 'D21', 'E21) AS V(DNO)
       WHERE DEPTNO = V.DNO
  	  

  This revision might provide better performance if there is an index on DEPTNO. The list of values would be accessed first and joined to EMPLOYEE with a nested loop join using the index to apply the join predicate.

  Sometimes the optimizer does not have accurate information to determine the best join method for the rewritten version of the query. This can occur if the IN list contains parameter markers or host variables which prevent the optimizer from using catalog statistics to determine the selectivity. This registry variable causes the optimizer to favor nested loop joins to join the list of values, using the table that contributes the IN list as the inner table in the join.

  Note: When either or both of the DB2® query compiler variables DB2_MINIMIZE_LISTPREFETCH and DB2_INLIST_TO_NLJN, are set to YES, they remain active even if REOPT(ONCE) is specified.

  Changes to this variable can take effect immediately for all future compiled SQL statements if the db2set command is issued with the -immediate parameter. You do not need to restart the instance.

• Operating system: All
• Default=Y,Y,
• Controls the use of sub-element statistics. These are statistics about the content of data in columns when the data has a structure in the form of a series of sub-fields or sub-elements delimited by blanks. Collection of sub-element statistics is optional and controlled by options in the RUNSTATS command or API.
  Important: This variable is deprecated and might be removed in a future release because you should only change the settings under the advisement of IBM® service.
  This registry variable affects how the optimizer deals with a predicate of the form:

     COLUMN LIKE '%xxxxxx%'

  where the xxxxxx is any string of characters.

  The syntax showing how this registry variable is used is:

     db2set DB2_LIKE_VARCHAR=[Y|N|S|num1] [,Y|N|S|num2]

  where
  • The term preceding the comma, or the only term to the right of the predicate, means the following but only if the second term is specified as N or the column does not have positive sub-element statistics:
    • S – The optimizer estimates the length of each element in a series of elements concatenated together to form a column based on the length of the string enclosed in the % characters.
    • Y – The default. Use a default value of 1.9 for the algorithm parameter. Use a variable-length sub-element algorithm with the algorithm parameter.
    • N – Use a fixed-length sub-element algorithm.
    • num1 – Use the value of num1 as the algorithm parameter with the variable length sub-element algorithm.
  • The term following the comma means the following, but only for columns that do have positive sub-element statistics:
    • N – Do not use sub-element statistics. The first term takes effect
    • Y – The default. Use a variable-length sub-element algorithm that uses sub-element statistics together with the 1.9 default value for the algorithm parameter in the case of columns with positive sub-element statistics.
    • num2 – Use a variable-length sub-element algorithm that uses sub-element statistics together with the value of num2 as the algorithm parameter in the case of columns with positive sub-element statistics.

  Changes to this variable can take effect immediately for all future compiled SQL statements if the db2set command is issued with the -immediate parameter. You do not need to restart the instance.

• Operating system: All
• Default=NO, Values: YES or NO
• List prefetch is a special table access method that involves retrieving the qualifying RIDs from the index, sorting them by page number and then prefetching the data pages. Sometimes the optimizer does not have accurate information to determine if list prefetch is a good access method. This might occur when predicate selectivities contain parameter markers or host variables that prevent the optimizer from using catalog statistics to determine the selectivity.

  This registry variable prevents the optimizer from considering list prefetch in such situations.

  Note: When either or both of the DB2 query compiler variables DB2_MINIMIZE_LISTPREFETCH and DB2_INLIST_TO_NLJN, are set to YES, they remain active even if REOPT(ONCE) is specified.

  Changes to this variable can take effect immediately for all future compiled SQL statements if the db2set command is issued with the -immediate parameter. You do not need to restart the instance.

• Operating system: All
• Default=OFF, Values: ON or OFF
• Affects the selectivity value computed by the query optimizer for certain subquery predicates when it is set to ON. It can be used to improve the accuracy of the selectivity value of equality subquery predicates that use the MIN or MAX aggregate function in the SELECT list of the subquery. For example:

  SELECT * FROM T WHERE 
  T.COL = (SELECT MIN(T.COL) 
  FROM T WHERE …)

  Changes to this variable can take effect immediately for all future compiled SQL statements if the db2set command is issued with the -immediate parameter. You do not need to restart the instance.

• Operating system: All
• Default=NULL, Values: amount of space in megabytes that can be used by a query in all temporary table spaces
• Limits the amount of space that queries can use in the temporary table spaces. Setting DB2_OPT_MAX_TEMP_SIZE can cause the optimizer to choose a plan that is more expensive than would otherwise be chosen, but which uses less space in the temporary table spaces. If you set DB2_OPT_MAX_TEMP_SIZE, be sure to balance your need to limit use of temporary table space against the efficiency of the plan your setting causes to be chosen.

  If DB2_WORKLOAD=SAP is set, DB2_OPT_MAX_TEMP_SIZE is automatically set to 10 240 (10 GB).

  If you run a query that uses temporary table space in excess of the value set for DB2_OPT_MAX_TEMP_SIZE, the query does not fail, but you receive a warning that its performance may be suboptimal, as not all resources may be available.

  The operations considered by the optimizer that are affected by the limit set by DB2_OPT_MAX_TEMP_SIZE are:

  • Explicit sorts for operations such as ORDER BY, DISTINCT, GROUP BY, merge scan joins, and nested loop joins.
  • Explicit temporary tables
  • Implicit temporary tables for hash joins and duplicate merge joins

  Changes to this variable can take effect immediately for all future compiled SQL statements if the db2set command is issued with the -immediate parameter. You do not need to restart the instance.

• Operating system: All
• Default=NO, Values: NO, YES, any integer, DISABLE, JUMPSCAN, NO_SORT_NLJOIN, or NO_SORT_MGJOIN
• This registry variable lets you request either reduced optimization features or rigid use of optimization features at the specified optimization level. If you reduce the number of optimization techniques that are used, you also reduce time and resource use during optimization.
  If you set this variable, the following syntax rules apply:

  • Separate each option with a comma (,), and ensure that no spaces appear before or after the comma.
  • Separate an option and the value for that option with a single space.
  • If the setting includes a space, enclose the setting in double quotation marks (“”).
  The following example shows the correct syntax:

  db2set DB2_REDUCED_OPTIMIZATION="NO_SORT_NLJOIN,JUMPSCAN ON"

  Note: Although optimization time and resource use might be reduced, the risk of producing a less than optimal data access plan is increased. Use this registry variable only when advised by IBM or one of its partners.
  • If set to NO

    The optimizer does not change its optimization techniques.

  • If set to YES

    If the optimization level is 5 (the default) or lower, the optimizer disables some optimization techniques that might consume significant prepare time and resources but do not usually produce a better access plan.

    If the optimization level is exactly 5, the optimizer scales back or disables some additional techniques, which might further reduce optimization time and resource use, but also further increase the risk of a less than optimal access plan. For optimization levels lower than 5, some of these techniques might not be in effect in any case. If they are, however, they remain in effect.

  • If set to any integer

    The effect is the same as YES, with the following additional behavior for dynamically prepared queries optimized at level 5. If the total number of joins in any query block exceeds the setting, then the optimizer switches to greedy join enumeration instead of disabling additional optimization techniques as described previously for level 5 optimization levels. which implies that the query will be optimized at a level similar to optimization level 2.

  • If set to DISABLE

    The behavior of the optimizer when unconstrained by this DB2_REDUCED_OPTIMIZATION variable is sometimes to dynamically reduce the optimization for dynamic queries at optimization level 5. This setting disables this behavior and requires the optimizer to perform full level 5 optimization.

  • If set to JUMPSCAN
    Use this option to control if the DB2 optimizer can use jump scan operations. You can specify the following values:

    • OFF = The DB2 optimizer will not create plans using jumps cans.
    • ON = The DB2 optimizer uses cost-based analysis to determine whether to generate plans that use jump scans (default).
  • If set to NO_SORT_NLJOIN

    The optimizer does not generate query plans that force sorts for nested loop joins (NLJN). These types of sorts can be useful for improving performance; therefore, be careful when using the NO_SORT_NLJOIN option, as performance can be severely impacted.

  • If set to NO_SORT_MGJOIN

    The optimizer does not generate query plans that force sorts for merge scan joins (MSJN). These types of sorts can be useful for improving performance; therefore, be careful when using the NO_SORT_MGJOIN option, as performance can be severely impacted.

    Note that the dynamic optimization reduction at optimization level 5 takes precedence over the behavior described for optimization level of exactly 5 when DB2_REDUCED_OPTIMIZATION is set to YES as well as the behavior described for the integer setting.

  • If set to ZZJN:
    Use this option to control how the DB2 optimizer uses the zigzag join method for star schema-based queries that contain one fact table. You can specify the following values:

    • OFF = The DB2 optimizer does not use the zigzag join method.
    • ON = The DB2 optimizer uses cost-based analysis to determine whether to use the zigzag join method or a different join method (default).
    • FORCE = If the zigzag join method is feasible, the DB2 optimizer uses the zigzag join method.
  • If set to ZZJN_MULTI_FACT:
    Use this option to control how the DB2 optimizer uses the zigzag join method for star schema-based queries that contain more than one fact table. You can specify the following values:

    • OFF = The DB2 optimizer does not use the zigzag join method.
    • ON = The DB2 optimizer uses cost-based analysis to determine whether to use the zigzag join method or a different join method (default).
    • FORCE = If the zigzag join method is feasible, the DB2 optimizer uses the zigzag join method.

  Changes to this variable can take effect immediately for all future compiled SQL statements if the db2set command is issued with the -immediate parameter. You do not need to restart the instance.

• Operating system: All
• Default=NO, Values: YES or NO
• This registry variable controls where the SELECTIVITY clause can be used in search conditions in SQL statements.

  When this registry variable is set to NO, the SELECTIVITY clause can only be specified in a user-defined predicate.

  When this registry variable is set to YES, the SELECTIVITY clause can be specified for the following predicates:

  • A user-defined predicate
  • A basic predicate in which at least one expression contains host variables or parameter markers

  Changes to this variable can take effect immediately for all future compiled SQL statements if the db2set command is issued with the -immediate parameter. You do not need to restart the instance.

• Operating system: All
• Default=Empty string, Values:
  • APREUSE {YES | NO}
  • BLOCKING {UNAMBIG | ALL | NO}
  • CONCURRENTACCESSRESOLUTION { USE CURRENTLY COMMITTED | WAIT FOR OUTCOME }
  • DATETIME {DEF | USA | EUR | ISO | JIS | LOC}
  • DEGREE {1 | degree-of-parallelism | ANY}
  • DYNAMICRULES {BIND | INVOKEBIND | DEFINEBIND | RUN | INVOKERUN | DEFINERUN}
  • EXPLAIN {NO | YES | ALL}
  • EXPLSNAP {NO | YES | ALL}
  • FEDERATED {NO | YES}
  • INSERT {DEF | BUF}
  • ISOLATION {CS | RR | UR | RS | NC}
  • OPTPROFILE {profile_name | schema_name.profile_name}
  • QUERYOPT optimization-level
  • REOPT {NONE | ONCE | ALWAYS}
  • STATICREADONLY {YES|NO|INSENSITIVE}
  • VALIDATE {RUN | BIND}
• The DB2_SQLROUTINE_PREPOPTS registry variable can be used to customize the precompile and bind options for SQL and XQuery procedures and functions. When setting this variable, separate each of the options with a space, as follows:

  db2set DB2_SQLROUTINE_PREPOPTS="BLOCKING ALL VALIDATE RUN"

  For a complete description of each option and its settings, see "BIND command."

If you want to achieve the same results as DB2_SQLROUTINE_PREPOPTS for select individual procedures, but without restarting the instance, use the SET_ROUTINE_OPTS procedure.