In both partitioned and non-partitioned database environments, when EXTEND is specified, the optimizer searches for opportunities to transform both "NOT IN" and "NOT EXISTS" subqueries into anti-joins.
When set to YES, this registry variable enables deferred prepare semantics such that all untyped parameter markers used in CLI and JDBC 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.
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. 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.
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.
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 This registry variable prevents the optimizer from considering list prefetch in such situations.
SELECT * FROM T WHERE
T.COL = (SELECT MIN(T.COL)
FROM T WHERE ...)
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 optimizer does not change its optimization techniques.
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.
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 above for level 5 optimization levels. which implies that the query will be optimized at a level similar to optimization level 2.
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.
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.
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.
When this registry variable is set to NO, the SELECTIVITY clause can only be specified in a user-defined predicate.
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.