Tuning poorly-performing DB2 queries can be a full-time job, but it helps immensely if you know how to tweak queries to behave the way you’d like them to behave.
One tweak you can use is to disable list prefetch using the OPTIMIZE FOR 1 ROW clause. If the performance of your query suffers due to list prefetch, OPTIMIZE FOR 1 ROW will cause DB2 to avoid an access path that uses list prefetch. This capability might be of particular use in an online environment in which data is displayed to the end user a screen at a time.
Another useful tweak is knowing how to selectively disable index access. This can be done by appending OR 0=1 to a predicate to eliminate index access. For example, consider a query against the EMP table on which two indexes exist: one on EMPNO and one on WORKDEPT.
In this case, the OR 0=1 prohibits DB2 from choosing the WORKDEPT index by making the predicate Stage 2. This forces DB2 to use either the index on EMPNO or a table space scan. Similar techniques include adding 0 to a numeric column or appending a null string to a character column to avoid indexed access. The latter is preferred because it disables matching index access but leaves the predicate Stage 1. For example:
SELECT EMPNO, WORKDEPT, EDLEVEL, SALARY
WHERE EMPNO BETWEEN '000020' AND '000350'
AND WORKDEPT > 'A01' CONCAT '';
Both OPTIMIZE FOR 1 ROW and using OR 0=1 are valid query tweaks for specific types of tuning. The following techniques can be used to tweak queries to try to encourage DB2 to use different access paths:
OPTIMIZE FOR n ROWS: Note that the n can be any value.
FETCH FIRST n ROWS ONLY: Again, where n can be any value.
No Operation (+0, -0, /1, *1, CONCAT ''): Adding or subtracting zero, dividing or multiplying by 1, or concatenating an empty string will not change the results of a query but might change the optimizer’s decision.
These techniques can cause DB2 to choose a different access path. Consider using them when you are in a jam and need to try different types of access. Compare and contrast the results and costs of each scenario to determine which might be most useful to your particular situation.
Although non-column expressions are indexable, IBM has excepted the “no operation” expressions because they are used as tricks to fool the optimizer. IBM did not include these expressions because these tricks have been deployed by DB2 developers to avoid indexed access for more than a decade. An example SQL statement using one of these tricks follows:
WHERE EMPNO < :HOST-VAR CONCAT '';
In this case, a table space scan is used because an empty string is concatenated to the host variable in the predicate and no other predicates are available for indexed access. However, the predicate remains Stage 1.
Although hints and tweaks are not to be considered everyday tuning tools, using them for a statement or two is probably better than changing the statistics for all queries, unless the performance of every query is bad.
i have some doubt here hows this predicate change useful in query performance improvement. since it is restricting DB2 from index access. and if i want a particular index to be chosen, is there any way to do that.
thanks in advance.....