Hi All,

I have a complaint from DBA saying that one the queries takes 67% of the CPU. Query has left outer join. I copied this query from one of the existing packages to do the same function. This query is used in online program.

DBA has sent me the following information which i could not decode.

SQL_CALL STMTÆ SECTÆ SQL TIMEPCT CPUPCT INDB2_TIME INDB2_CPU
-------- ----- ----- ---------- ------- ------- ------------ ------------
SELECT 01451 00002 5476504 22.09% 39.64% 00:00.000232 00:00.000090
OPEN 01390 00001 12810 63.09% 31.13% 00:00.283133 00:00.030186
SELECT 01490 00004 5431293 6.23% 11.79% 00:00.000066 00:00.000027
SELECT 01473 00003 5476504 2.76% 10.13% 00:00.000029 00:00.000023
FETCH 01550 00001 5431746 1.22% 5.24% 00:00.000013 00:00.000012
SELECT 01519 00005 107072 4.58% 1.92% 00:00.002461 00:00.000223
CLOSE 01564 00001 12811 .00% .00% 00:00.000009 00:00.000008

I dont event know what those columns say. The query is pasted below. query uses the synonyms instead of the qulaified tables.

DECLARE ERID_CURSOR CURSOR FOR
SELECT DISTINCT B.INV_ID
FROM ORG_STRUKTUR_S A
LEFT OUTER JOIN IP_IP_REL_S B
ON SUBSTR(A.IP_ID,1,10) = B.INV_ID_2
WHERE B.IR_KD = 'PRIMAER'
AND (CURRENT TIMESTAMP <= B.TIL_TS OR B.TIL_TS IS NULL)
AND A.OEST_NV = 'RAADGIV'
AND A.OEST_REL_TP IN ('DIREKTE','SAMLAGT','STAB')
AND A.IP_ID_2 = :USER_ID;

DBA says its at the OPEN statement where the query is actually executed. EXPLAIN says it is going for Index scan with MATCHCOLS = 3 for first table and MATCHCOLS = 1 for the second table. COST_CATEGORY is B and i guess its because of RANGE predicates in the WHERE clause and Host variable. (Reason for COST CATEGORY is Host variable).

Do anyone of you has any idea about the data DBA has sent..?. Is that from any of the monitor tools..?. Does COST_TABLE, STRUCTURE_TABLE, PREDICATE_TABLE has something to do with EXPLAIN in DB2 V8 for Z/os..?.

Sorry for lots of questions. I still have lots of questions. Performance tuning is always been a difficult job for me. Please help me with this issue.

Here is the explain results:
PLANNO METHOD TNAME ACCESSTYPE MATCHCOLS INDEXONLY
------ ------ ------------------ ---------- --------- ---------
1 0 ORG_STRUKTUR_S N 3 Y
2 1 IP_IP_REL_S I 1 N
3 3 0 N

The no of rows in the tables are:

IP_IP_REL_S: 18450790 (Last stats gathered on Nov 4th 2007)
ORG_STRUKTUR_S: 68652(Last stats on DEC 19 2007)

Apparently, the "open cursor" takes 63% of the program execution.
Not exceptional, I would say: it just means that the "real work" is done during "open cursor" and not during the subsequent fetches (since they only take 1% of the execution time).
This is always the case when "materialization" takes place: the full result set has to be generated (at "open" time) and stored by DB2 before the first line can be returned (by the first FETCH) because sorting is needed for a certain reason.
The "DISTINCT" requires a sort, since that's the most efficient way to remove duplicates.
Remove the DISTINCT and the percentages will be totally different.

Certainly if your FETCH iteration does not continue until SQLCODE=100, materialization is suboptimal. But if you retrieve all rows from the result set, materialization might even be preferred, since it will e.g. release locks earlier (i.e., after the OPEN) than without materialization.

By removing the DISTINCT, the third line (METHOD=3) of explain output will disappear.

B.t.w., the JOIN condition (using SUBSTR) is potentially suboptimal ("stage-2" predicate); are you sure you need to substring to the first 10 positions? If the positions from 11 on are guaranteed to be blanks, you should better write "A.IP_ID = B.INV_ID_2".

The join is a nested one, i.e., the indexed access to table B is repeated for every matching row of table A. This need not be suboptimal, though; certainly not if the conditions on A (like A.IP_ID_2 = :USER_ID) are well filtering.

Hi Peter,

Thanks for your help.

I can remove SUBSTR. All are spaces after 11 position however we have an in house tool which doesn't allow if there is mismatch in the columns or host variables. (It doenst allow to promote the code if there is mismatch is size for the columns however we can force it to do). In this case
A.IP_ID is CHAR(20) and B.INV_ID_2 is CHAR(10).

I can remove DISTINCT and put some programming logic for this.

How do I materialize the query..?. Is that a MQT Concept..?

The table on the right side contains 18.5 Million rows.

Dont you think changing the tables order to the other side in the join will do better. (leftone to right and right one to left). So that ORG Table will go for Nested loop join.

The filtering factor of the predicate A.IP_ID_2 = :USER_ID is very less.

This query is from Online program. This issue arised during the peak business hours. There were 68,000 transations with 25 million SQL CALLS in 2 hours and 45 minutes. So executing this query 68,000 times in approx 3 hours will be COSTLY. The right table IP_IP has HUGE data.

You don't: DB2 does.
From the moment you see a "METHOD=3" line in the explain output, DB2 is materializing. (It's also materializing when you see a "Y" in one of the "SORT*" columns.)

Again, that's up to DB2 which table is chosen as left (= outer) table.
That's OK with the chosen access path (indexed access); its size is not too relevant. That explains why it is not taken as outer table in the nested loop join.

If this is DB2 for z/OS v8 or higher, you will see a substantial benefit from removing the SUBSTR; with DB2 v7 for z/OS, comparing two unequal length columns ("non-matching datatypes") was equally costy as using the SUBSTR.

Thanks a lot peter.

Wish you Happy new year to all..!!