MVSFORUMS.com

[suresh_d]

Hi,

I need help in fine tuning the below cursors in my program which is taking very much time. I have no idea how to do a query optimisation.

[ash_sudp]

Hi Suresh,

I did not look at the SQL, but as a first step speak to your DBA to find out all the columns indexed on the table.

Reason:
If you do a order by OR any sort on the resultant on a non indexed column then you are doing a whole table scan. This will consume a lot of time. So check on the indexed part and see if the column in order by is a part of the primary index. If not see how it can be made a part of the promary index. This will help.

let me know if you need any more details.

Ashok Mohan.

[RonB]

I'm certainly no DB2 expert, but here's what I see after looking at the first cursor query, which was:

DECLARE KUND1 CURSOR FOR
SELECT DISTINCT A.PONR
,A.KUNDNR
,HUSHALLID
,A.BOLAGKOD
,YEAR(CURRENT DATE - FODELSEDATE)
,MONTH(CURRENT DATE - FODELSEDATE)
,DAVILLA
,CI_BRF
,A.LKFKOD
,A.LANSKOD
,A.KOMMUNKOD
,A.FORSAMLINGKOD
,SUBSTR(EMAILADRESS,1,50)
,A.POSTNR
,PKISTATUS
,A.CUSTOMERVALUE
FROM TPY001KUND A
WHERE EXISTS
(SELECT 1 FROM TPY122FORSV B
WHERE A.PONR = B.PONR
AND A.BOLAGKOD = B.BOLAGKOD
AND B.BRANSCHGRP IN
('CIVIL','MOTOR','OLFA','GRUPP'))
AND A.BOLAGKOD IN ('TH','AF')
AND A.PONR <> 0
AND FODELSEDATE <> ' '
AND A.REGTILLF IN
(SELECT MAX(REGTILLF)
FROM TPY001KUND C
WHERE C.PONR = A.PONR
AND C.BOLAGKOD = A.BOLAGKOD
AND C.BOLAGKOD IN ('TH','AF'))
ORDER BY A.PONR
WITH UR
END-EXEC.

First of all, having an ORDER BY A.PONR clause in addition to specifying DISTINCT A.PONR . . . A.CUSTOMERVALUE as the SELECT clause, is redundant since the DISTINCT clause implies that the resultant rows must be sorted in the order specified, and A.PONR is the first column selected in the DISTINCT clause.

Secondly, the WHERE C.BOLAGKOD IN ('TH','AF') predicate in the SELECT MAX inner join is redundant, since the inner join already tests WHERE C.BOLAGKOD = A.BOLAGKOD, and the inner join will only be executed if the WHERE A.BOLAGKOD IN ('TH','AF') predicates were met in the outer join.

Thirdly, DATE computations are costly and should be avoided, if possible. I'm not SURE, but I think that if you want both the year and the month from the same date computation, instead of saying YEAR(CURRENT DATE - FODELSEDATE) followed by MONTH(CURRENT DATE - FODELSEDATE), you could, instead, simply say SUBSTR(CHAR(CURRENT DATE - FODELSEDATE),1,4), thus reducing the number of date computations by half.

Fourthly, using an IN clause followed by a SELECT MAX clause is inefficient, since the MAX clause can only result in ONE value. The IN should be changed to a simple =.

And lastly, the WHERE clause should be tuned to eliminate matching rows as soon as possible, so you "could" eliminate the B.table access entirely if you eliminated A.rows early on. So, if it were me, I might consider rewriting the query as

[bauer]

Use EXPLAIN, check runstats data.

[suresh_d]

Hi,

Yes the problem si with first cursor kund1, I was able to understand your tuned queryt except the date part. I guess the above tuned date will result only year ? how to fetch the month part also ina single date command.

I am begineer in DB2 tuning, plz don't mistake if I didn't understand.

Thank you.
Suresh

[RonB]

Ah yes. My apologies. I see that I neglected to remember that DB2 formats the date in character mode as yyyy DASH mm DASH dd, while the YEAR function returns positions 1-4 and the MONTH returns positions 6-7. My SUBSTR(CHAR(*),1,4) was predicated on my poor brain's false idea that the returned date would be like COBOL returns it, namely YYMMDD.
If you can live with YYYY-MM, you could change the SUBSTR length from 4 to 7. If not, then you will have to leave the query with both YEAR(*) and MONTH(*), though, as I said, date calculations are quite expensive in queries. I have seen situations where it was much faster and far more efficient to return the entire date ( with the calculation ) in the original query, followed by a SORT step that copies the output, selecting only the required fields, to create the ultimate desired output. The savings becomes more evident as the number of rows returned increases. It's not such a big deal if there are only a few hundred rows, but at 100,000 rows, it is a big deal.
_________________
A computer once beat me at chess, but it was no match for me at kick boxing.