Registries for compiler and runtime performance improvement

You can set performance variables using the db2set command to improve performance. This topic lists the performance registries supported in the Big SQL environment

DB2COMPOPT

• Operating systems: All
• Values: SET_TO_JOIN, ENABLE_GEN_PRED_SUBSTRING
• Default (Big SQL tables): SET_TO_JOIN, ENABLE_GEN_PRED_SUBSTRING
• Use: Specifies whether the compiler should use rewrite opportunities to improve the access plan.

When you set DB2COMPOPT to SET_TO_JOIN, the compiler attempts to rewrite the INTERSECT and EXCEPT operators to use JOIN instead of UNION. Since the JOIN operator is more efficient than UNION, setting DB2COMPOPT can improve performance.

When you set DB2COMPOPT to ENABLE_GEN_PRED_SUBSTRING and 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 in turn can improve performance with no change to the original query.

DB2_EXTENDED_OPTIMIZATION

• Operating system: All
• Values: ON, OFF, JRFPREDS ON, JRFPREDS OFF, BI_INFER_CC ON, BI_INFER_CC OFF
• Default (Big SQL tables): JRFPREDS ON, BI_INFER_CC ON

• Use: Specifies whether the query optimizer uses optimization extensions.

When you set DB2_EXTENDED_OPTIMIZATION to JRFPREDS ON, the optimizer selects whether to inject Join Range Filter Predicates (JRFPs) into the access plan. This runtime filtering reduces the number of rows that are scanned during query processing and then passed to the hash join operators. If you set this variable with the db2set command using the -immediate parameter, the change takes effect immediately for all future compiled SQL statements. You do not need to restart the instance.

In cases where the extra predicates injected lead to more partition elimination, this registry can improve performance significantly.

When you set DB2_EXTENDED_OPTIMIZATION to BI_INFER_CC ON and no statistics have been collected on columns that are involved in the query, the optimizer attempts to fabricate statistics on Hadoop tables in a way that favors hash joins over nested loop joins. If you set this variable with the db2set command by using the -immediate parameter, the change takes effect immediately for all future compiled SQL statements. You do not need to restart the instance.

DB2_SORT_AFTER_TQ

• Operating system: All
• Values: YES, NO, FORCEGB, FORCEGB nM
• Default: NO

• Use: 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.

The following list describes the results when you set DB2_SORT_AFTER_TQ to a particular value.

• 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, not merge at the receiving end, and sort 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, not merge at the receiving end, and sort the rows at the receiving end after receiving all the rows.