Revisiting the SQL Queries

One way to improve your application performance is to scan through the queries used in them. Most of the time, the design of the query would have been pretty complex and the DB2 optimizer was unable to determine a ' good ' access path. A good practice would be to visit the application plan table and look for query numbers ( QUERYNO ) having ACCESSTYPE & MATCHCOL value of I and 0 respectively. You can also look as to why it is R & 0. Using your shops administration tools ( CA - Platinum or IBM Administration tool or any other ) list out the queries which you plan to re-visit.

Here is my experience on one of the query that I modified  -

SELECT * FROM PRDNSCH.TABLE01 WHERE
( ( COL1 = :HV-01 AND
COL2 > :HV-02 ) OR
(   COL1 > :HV-03 ) ) ;

This query had an ACCESSTYPE value of I and a MATCHCOLS value of 0, its obvious isn't it - open and close brackets, OR clause : DB2 optimizer went dizzy and could not help much with respect to access path even if an index was present on COL1 & COL2.

I managed to split the query into two queries using the UNION clause. Here is how the query looked after the split -

SELECT * FROM PRDNSCH.TABLE01 WHERE
COL1 = :HV-01 AND
COL2 > :HV-02

UNION

SELECT * FROM PRDNSCH.TABLE01 WHERE
COL1 > :HV-03     ;

The first query above the UNION clause now had an ACCESSTYPE & MATCHCOLS value of I & 2 while the query below the UNION clause had an ACCESSTYPE & MATCHCOLS value of I & 1 respectively. The access path improved and the application program run time efficiency improved proportionally.

How was I sure that the these queries would result the same amount of rows ? Here is how you ensure it. You will have to first get some unique values from the table or approach your application development team for the same. Feed those values and perform a SELECT operation for the two queries as shown below. To double check, apply two or three test cases and verify the results.
First perform a SELECT operation on the table without modifying the original query.

SELECT * FROM PRDNSCH.TABLE01 WHERE
( ( COL1 = 1 AND
COL2 > 2 ) OR
(   COL1 > 3 ) ) ;

Number of rows retrieved - 100 Rows

Perform a SELECT operation on the table by applying the new query that you just designed.

SELECT * FROM PRDNSCH.TABLE01 WHERE
COL1 = 1 AND
COL2 > 2

UNION

SELECT * FROM PRDNSCH.TABLE01 WHERE
COL1 > 3     ;

Number of rows retrieved for 1st half of the query - 23 Rows
Number of rows retrieved for 2nd half of the query - 77 Rows

Number of rows retrieved - 100 Rows

Amazing !