First step is to point out the DB2 statement into program involved in the problem. To save time, I used SQLTUNE to dispaly the following panel :
It seems that request number 2 when Opening Cursor is the request to improve.
I displayed the DB2 statement having this long response time and here is the quizz of this month.
Like for any performance problem, once the request in error is identified, it is necessary to obtain access path used by DB2 to obtain data (this information is provided using explain statement).
I asked Sqltune to display access path and i received the following panel :
CUSTOMER table is defined with 2 indexes , the first : I1CUSTOMER on SOCNUMBER column and the second one : I2CUSTOMER on NAME column.
Surprised by this long response time, i asked End users of this transaction, how they used it.
They told me, this transaction was used to identify on person.
For example, when they are looking for a Mr SMITH, they specify 1 in the Social security number field for male and SMI in the name field (first few characters of name)
to obtain all persons whose name starts by SMI.
The DB2 statements allows optimizer to choose between accessing table through index I1CUSTOMER using condition SOCNUMBER BETWEEN :SOC1 AND :SOC2
or through index I2CUSTOMER using condition NAME BETWEEN :NAME1 AND :NAME2.
In both case DB2 optimizer estimates 10% of total number of rows in table are accessed (BETWEEN).
Because Clustering index on this table is on social security number, it is the one used by DB2 to minimize number of I/O.
In that specific case, it is not the best solution, because specifying 1 in first position of column social security number
is not selective at all, and to answer that request, DB2 has to access about 10% of that huge table.
Accessing table using index on NAME would be much more selective, so faster.
But you cannot force DB2 to use a specific index. We are going to forbid DB2 to use index based on social security number.
To do so, let's disqualify predicates SOCNUMBER BETWEEN :SOC1 and :SOC2 to use an index.
An easy way, is to add a OR for this this predicate only.
When Db2 evaluates, all available access path to determine the best one,
all predicates containing OR are excluded.
By adding a condition that does not change predicate on SOCNUMBER but containing OR , we decide to modify request adding OR 1=0 .
Let 's see new access path used by DB2 after modification.
This seems very interresting. After modification and program compilation, i execute transaction with same information and i obtain the following results.
My mission is accomplished, and I can invite you next month for a new problem analysis.
