I had done a bit of research on Multi-row fetching in COBOL-DB2 application programs for CPU time utilization/performance improvement and tried to implement the same in my system.
There is a COBOL-DB2 batch program which reads from DB2 tables and writes into a flat file as an extract of data.
The driving cursor has UNION ALL and its a join of 3 tables. I have verified the joins and the indexes defined, all look good.
In the peak time (something like during Christmas runs) the expectation is load would be more and the job might take too long to execute.
So, I tried Multi-row fetching (with row-set 100 once, with row-set 1000 once) to reduce the CPU time.
Below is the SQL query in cursor definition:
Code: Select all
SELECT A.Col1
,A.Col2
,A.Col3
,A.Col4
,B.Col5
,B.Col6
FROM
(
SELECT C.Col1
,C.Col2
,C.Col3
,C.Col4
,MAX(C.SEQ_NO) MAXSEQ
FROM
(
SELECT A.Col1
,A.Col2
,A.Col3
,A.Col4
,B.SEQ_NO
FROM TableA A
,TableB B
,TableC C
WHERE C.Col1 BETWEEN :WH-CR-PART-NO-FROM
AND :WH-CR-PART-NO-TO
AND C.Col1 = A.Col1
AND C.Col2 = A.Col2
AND A.Col3 >= C.Col3
AND A.Col3 <= SUBSTR(STRIP(C.Col3) || "9999",1,5)
AND A.Col7 <= :WW-PROCESS-DATE
AND (A.Col8 >= :WW-PROCESS-DATE
OR A.Col8 IS NULL)
AND C.Col4 = 0
AND C.SEQ_NO = B.SEQ_NO
AND B.Col9 <= :WW-WF-FORMAT-DATE
AND B.ColA >= :WW-WF-FORMAT-DATE
UNION ALL
SELECT A.Col1
,A.Col2
,A.Col3
,A.Col4
,B.SEQ_NO
FROM TableA A
,TableB B
,TableC C
WHERE C.Col1 BETWEEN :WH-CR-PART-NO-FROM
AND :WH-CR-PART-NO-TO
AND C.Col1 = A.Col1
AND C.Col2 = A.Col2
AND A.Col7 <= :WW-PROCESS-DATE
AND (A.Col8 >= :WW-PROCESS-DATE
OR A.Col8 IS NULL)
AND C.Col4 = A.Col4
AND C.SEQ_NO = B.SEQ_NO
AND B.Col9 <= :WW-WF-FORMAT-DATE
AND B.ColA >= :WW-WF-FORMAT-DATE
) C
GROUP BY C.Col1
,C.Col2
,C.Col3
,C.Col4 ) A
,TableB B
WHERE B.SEQ_NO = A.MAXSEQ
ORDER BY Col1
,Col2
,Col4
FOR FETCH ONLY
WITH UR
Code CPU # of Records Processed
Old 6.61 12068688
New 6.07 12068688
Its a mere 8% improvement on CPU time. As per some books, I read that it would be at least 60% of CPU time improvement.
I am afraid if I have missed anything in the process.
Can someone help me understand what could be the reason for not having much optimization for multi-row fetch technique.
Any help would be much appreciated.
Thanks in advance.