Registries for compiler and runtime performance improvement

You can improve the performance of your Db2® Big SQL environment by using the db2set command to change the values of certain registry variables. These registry variables can provide some performance benefit for specific queries such as SELECT DISTINCT queries.

DB2COMPOPT
Specifies whether the compiler should use rewrite opportunities to improve the access plan.

If you specify the -immediate parameter on the db2set command, the change takes effect immediately for all future compiled SQL statements. You do not need to restart the instance.

  • Operating systems: All supported Db2 Big SQL platforms
  • Values:
    • SET_TO_JOIN: The compiler attempts to rewrite the INTERSECT and EXCEPT operators to use JOIN instead of UNION. Because the JOIN operator is more efficient than UNION, this can improve performance.
    • ENABLE_GEN_PRED_SUBSTRING: If the compiler plans a query against a table that has a partition expression with a substring, the compiler automatically generates a predicate on the partitioning column if there is a predicate on the column that is used to derive the partitioning column. This can lead to more partition elimination and fewer scans of the entire table, which can improve performance without a change to the original query.
    • ENABLE_DIST_TO_GROUPBY: If the compiler plans a query with a SELECT DISTINCT clause at the top, and no ORDER BY, FETCH FIRST n ROWS, or LIMIT clause in the query, the compiler rewrites the query to include a GROUP BY clause, which can enhance performance.
  • Default (Db2 Big SQL tables): SET_TO_JOIN, ENABLE_GEN_PRED_SUBSTRING
DB2_REDUCED_OPTIMIZATION
Specifies whether the query optimizer uses reduced optimization.

If you specify the -immediate parameter on the db2set command, the change takes effect immediately for all future compiled SQL statements. You do not need to restart the instance.

  • Operating systems: All supported Db2 Big SQL platforms
  • Values:
    • MSJOIN ON
    • MSJOIN OFF: The optimizer plans queries without merge join operators. There can be performance improvement in some cases when merge joins are not used in access plans.
  • Default (Db2 Big SQL tables): MSJOIN ON
DB2_EXTENDED_OPTIMIZATION
Specifies whether the query optimizer uses optimization extensions.

If you specify the -immediate parameter on the db2set command, the change takes effect immediately for all future compiled SQL statements. You do not need to restart the instance.

  • Operating systems: All supported Db2 Big SQL platforms
  • Values:
    • JRFPREDS ON: The optimizer decides whether to inject join range filter predicates (JRFPs) into the access plan. This runtime filtering reduces the number of rows that are scanned and then passed to the hash join operators during query processing. In cases where the extra predicates lead to more partition elimination, this registry value can improve performance significantly.
    • JRFPREDS RESTRICT: Only JRFPs on partitioning columns and sorted columns are pushed down to the I/O engine. This default behavior is not reflected in db2set output.
    • JRFPREDS OFF
    • ENHANCED_JRFPREDS ON: The optimizer decides whether to inject JRFPs into the access plans when there is a three-way join (or more) involving hash join operators. For star schema joins where multiple dimension tables are joined to a fact table, there can be performance improvements when this registry variable is enabled. This default behavior is not reflected in db2set output.
    • ENHANCED_JRFPREDS OFF
    • BI_INFER_CC ON: If no statistics have been collected on columns that are involved in the query, the optimizer attempts to fabricate statistics in a way that favors hash joins over nested loop joins.
    • BI_INFER_CC OFF
    • NO_MGMD: This registry value can improve performance when a query selects multiple distinct columns and there is aggregation on one or more of those columns. For example:
      SELECT MAX(DISTINCT(col1)), SUM(DISTINCT(col2))
  FROM tab1
    • XUNIONPART ON: Specifies that for queries with TQ operators that are directly below a UNION operator, access plans with multiple partitioning columns are favored over those with single partitioning columns. This default behavior is not reflected in db2set output.
    • XUNIONPART OFF
  • Default (Db2 Big SQL tables): JRFPREDS RESTRICT, ENHANCED_JRFPREDS ON, BI_INFER_CC ON, XUNIONPART ON
DB2_SORT_AFTER_TQ
Specifies how the optimizer works with directed table queues when the receiving end requires the data to be sorted and the number of receiving nodes is equal to the number of sending nodes.
  • Operating systems: All supported Db2 Big SQL platforms
  • Values:
    • NO: The optimizer usually sorts at the sending end, and merges the rows at the receiving end.
    • YES: The optimizer usually transmits the rows unsorted, does not merge at the receiving end, and sorts the rows at the receiving end after receiving all the rows.
    • FORCEGB: If the query optimizer constructs a GROUPBY or aggregation plan, the optimizer attempts to force a plan that transmits the rows unsorted, does not merge at the receiving end, and sorts the rows at the receiving end after receiving all the rows.
    • FORCEGB nM: If the query optimizer constructs a GROUPBY or aggregation plan, and the estimated input size for the GROUPBY is less than n megabytes, the optimizer attempts to force a plan that transmits the rows unsorted, does not merge at the receiving end, and sorts the rows at the receiving end after receiving all the rows.
  • Default (Db2 Big SQL tables): NO