We have a online master table search/filtering screen. As of now it has 18 inputs criteria & you can fill up 1 or many of them. The first filled up field determines the sort fields of the listing. To achieve this we have 18 x 2 SQLs in the program (one for UP one for DOWN for each sort order) and similarly some 20-22 index's in the table for index cover.
Now the required enhancement is a sort functionality on the selected 1 of the 6 displayed cols. But to achieve this following the same strategy we are ending up with 100s of SQLs & similar counts of indexes.
The typical SQL for below scenario is like::
FIELD01 - FIELD18 are the 18 search criteria possible
FIELD10 is the 1st search criteria entered
FIELD15 followed by REC_TIMESTAMP is the sort order selected. REC_TIMESTAMP is an unique id for a rec.
SELECT FIELD01, FIELD02, ... FIELDnn
WHERE SYSTEM_ID = :WS-SYSTEM-ID
AND FIELD01 BETWEEN :WS-FIELD01-LOW AND :WS-FIELD01-HIGH
AND FIELD02 BETWEEN :WS-FIELD02-LOW AND :WS-FIELD02-HIGH
AND FIELD18 BETWEEN :WS-FIELD18-LOW AND :WS-FIELD18-HIGH
AND FIELD10 = :WS-FIELD10-LOW
AND (FIELD15 > :WS-FIELD15
OR (FIELD15 = :WS-FIELD15
AND REC_TIMESTAMP > :WS-LAST-REC-TIMESTAMP))
ORDER BY FIELD15, REC_TIMESTAMP
FOR FETCH ONLY OPTIMIZE FOR 15 ROWS
For entered search criteria both LOW & HIGH will have the entered value. For others it will be LOW-VALUE & HIGH-VALUE respectively.
Please suggest any better approach? I am really skeptical of response of this approach & I am sure the next thing coming-up is sort across multiple cols
We are using DB2 V8 on CICS-COBOL. Tentative data volume is >= 50000, 100000 rec stress test. And being an online screen, response time is important; will need as much direct indexed access as possible.