file browser

zOs/SQL/EXPLAIUU

set current sqlid = 'A540769';
set current schema = 'OA1T';
select count(*), UU100020
    from tuu100a2
    group by UU100020
    order by 1 desc
    fetch first 10 rows only
    with ur
;;;
delete from  A540769.plan_table;
explain plan set queryno =   1 for
    select CURRENT DATE - (?+0) DAYS
        from sysibm.sysDummy1
        ;
explain plan set queryno = 991 for
                                SELECT UU290090, UU100010,
 UU100020,    UU100060, '1' AS UEUE_ART from TUU140A2 B,
 TUU100A2 C, TUU290A2 D,                tUU130A2 E,
 TUU135A2 F WHERE
 UU140040 IN (?, ?, ?, ?, ?, ?, ?,          ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?) AND UU130100 IN              (?,
 ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
 ?,     ?, ?, ?) AND UU130110 IN (?, ?, ?, ?, ?, ?,
 ?, ?, ?, ?,        ?, ?, ?, ?, ?) AND UU140010 =
 UU100010 AND UU130020 LIKE ? AND     UU130260 LIKE ? AND
 UU130270 LIKE ? AND UU100030 < CURRENT DATE - (?+0)    DAYS AND
 UU100030 > CURRENT DATE - (?+0) DAYS AND UU100060 < 5 AND
UU100240 IN (?, ?, ?) AND UU100020 = UU130010 AND
 UU100010 =         UU290010 AND UU135010 = UU100010 AND
 UU135060 = 'A' AND UU135100 IN     ('1', '3') AND UU135170
= ? AND UU290090 >= ? AND UU290090 <= ? AND     UU290020 =
 (SELECT MAX (UU290020) from TUU290A2 WHERE UU290010 =
C.UU100010)

UNION SELECT UU290090, UU100010, UU100020,
 UU100060, '2' AS UEUE_ART from TUU140A2 B, TUU100A2 C,
 tUu290A2 D, tUu130A2 E, tUu135A2 F
WHERE UU140040 IN (?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,               ?, ?, ?,
 ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
AND UU130100 IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
 ?, ?,      ?, ?, ?, ?, ?, ?, ?) AND UU130110 IN (?,
 ?, ?, ?, ?, ?,         ?, ?, ?, ?, ?, ?, ?, ?, ?)
AND UU140010 = UU100010 AND         UU130020 LIKE ? AND
 UU130260 LIKE ? AND UU130270 LIKE ? AND UU100030 <
 CURRENT DATE - (?+0) DAYS AND UU100030 > CURRENT DATE - (?+0)
 DAYS AND      UU100060 < 5 AND UU100240 IN (?, ?, ?) AND
 UU100020 = UU130010 AND   UU100010 = UU290010 AND UU135010
= UU100010 AND UU135060 = 'A' AND      UU135100 IN ('1',
 '3') AND UU135170 = ? AND UU290090 >= ? AND
UU290090 <= ? AND UU290020 = (SELECT MAX (UU290020) from
 TUU290A2      WHERE UU290010 = C.UU100010) UNION SELECT
 UU290090, UU100010, UU100020, UU100060, '3' AS UEUE_ART
 from TUU140A2 B, tUu100A2 C, tUu290A2 D,
 tUu130A2 E,
 tUu135A2 F WHERE UU140040 IN (?, ?, ?, ?, ?, ?, ?,
?, ?, ?) AND UU130100 IN (?, ?, ?, ?, ?, ?, ?, ?,
 ?, ?,      ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) AND
 UU130110 IN (?, ?, ?,       ?, ?, ?, ?, ?, ?, ?, ?,
 ?, ?, ?, ?) AND UU140010 = UU100010 AND UU130020 LIKE
 ? AND UU130260 LIKE ? AND UU130270 LIKE ? AND
UU100030 < CURRENT DATE - (?+0) DAYS AND UU100030 > CURRENT
 DATE - (?+0) DAYS AND UU100060 < 5 AND UU100240 IN (?, ?,
 ?) AND UU100020 = UU130010    AND UU100010 = UU290010 AND
 UU135010 = UU100010 AND UU135060 = 'A' AND  UU135100 IN
 ('1', '3') AND UU135170 = ? AND UU290090 >= ? AND
UU290090 <= ? AND UU290020 = (SELECT MAX (UU290020) from
 TUU290A2      WHERE UU290010 = C.UU100010) UNION SELECT
 UU290090, UU100010, UU100020, UU100060, '4' AS UEUE_ART
 from TUU140A2 B, tUu100A2 C, tUu290A2 D,
 tUu130A2 E,
 tUu135A2 F WHERE UU140040 IN (?, ?, ?, ?, ?, ?, ?,
?, ?, ?) AND UU130100 IN (?, ?, ?, ?, ?, ?, ?, ?,
 ?, ?,      ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) AND
 UU130110 IN (?, ?, ?,       ?, ?, ?, ?, ?, ?, ?, ?,
 ?, ?, ?, ?) AND UU140010 = UU100010 AND UU130020 LIKE
 ? AND UU130260 LIKE ? AND UU130270 LIKE ? AND
UU100030 < CURRENT DATE - (?+0) DAYS AND UU100030 > CURRENT
 DATE - (?+0) DAYS AND UU100060 < 5 AND UU100240 IN (?, ?,
 ?) AND UU100020 = UU130010    AND UU100010 = UU290010 AND
 UU135010 = UU100010 AND UU135060 = 'A' AND  UU135100 IN
 ('1', '3') AND UU135170 = ? AND UU290090 >= ? AND
UU290090 <= ? AND UU290020 = (SELECT MAX (UU290020) from
 TUU290A2      WHERE UU290010 = C.UU100010) UNION SELECT
 UU290090, UU100010, UU100020, UU100060, '5' AS UEUE_ART
 from TUU140A2 B, tUu100A2 C, tUu290A2 D,
 tUu130A2 E,
 tUu135A2 F WHERE UU140040 IN (?, ?, ?, ?, ?, ?, ?,
?, ?, ?) AND UU130100 IN (?, ?, ?, ?, ?, ?, ?, ?,
 ?, ?,      ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) AND
 UU130110 IN (?, ?, ?,       ?, ?, ?, ?, ?, ?, ?, ?,
 ?, ?, ?, ?) AND UU140010 = UU100010 AND UU130020 LIKE
 ? AND UU130260 LIKE ? AND UU130270 LIKE ? AND
UU100030 < CURRENT DATE - (?+0) DAYS AND UU100030 > CURRENT
 DATE - (?+0) DAYS AND UU100060 < 5 AND UU100240 IN (?, ?,
 ?) AND UU100020 = UU130010    AND UU100010 = UU290010 AND
 UU135010 = UU100010 AND UU135060 = 'A' AND  UU135100 IN
 ('1', '3') AND UU135170 = ? AND UU290090 >= ? AND
UU290090 <= ? AND UU290020 = (SELECT MAX (UU290020) FROM
 tUu290A2 WHERE UU290010 = C.UU100010) FOR FETCH ONLY
;
select * from A540769.plan_view2
    order by applname, progname, queryNo, qblockno, planno, mixOpSeq
;
rollback
; original ;
                            FOR SELECT UU290090, UU100010, UU100020,
UU100060, '1' AS UEUE_ART FROM TUU140A2 B, TUU100A2 C, TUU290A2 D,
TUU130A2 E, TUU135A2 F WHERE UU140040 IN (?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) AND UU130100 IN
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?) AND UU130110 IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?) AND UU140010 = UU100010 AND UU130020 LIKE ? AND
UU130260 LIKE ? AND UU130270 LIKE ? AND UU100030 < CURRENT DATE - ?
DAYS AND UU100030 > CURRENT DATE - ? DAYS AND UU100060 < 5 AND
UU100240 IN (?, ?, ?) AND UU100020 = UU130010 AND UU100010 =
UU290010 AND UU135010 = UU100010 AND UU135060 = 'A' AND UU135100 IN
('1', '3') AND UU135170 = ? AND UU290090 >= ? AND UU290090 <= ? AND
UU290020 = (SELECT MAX (UU290020) FROM TUU290A2 WHERE UU290010 =
C.UU100010) UNION SELECT UU290090, UU100010, UU100020, UU100060, '2' AS
UEUE_ART FROM TUU140A2 B, TUU100A2 C, TUU290A2 D, TUU130A2 E, TUU135A2
F WHERE UU140040 IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
AND UU130100 IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?) AND UU130110 IN (?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?) AND UU140010 = UU100010 AND
UU130020 LIKE ? AND UU130260 LIKE ? AND UU130270 LIKE ? AND UU100030
< CURRENT DATE - ? DAYS AND UU100030 > CURRENT DATE - ? DAYS AND
UU100060 < 5 AND UU100240 IN (?, ?, ?) AND UU100020 = UU130010 AND
UU100010 = UU290010 AND UU135010 = UU100010 AND UU135060 = 'A' AND
UU135100 IN ('1', '3') AND UU135170 = ? AND UU290090 >= ? AND
UU290090 <= ? AND UU290020 = (SELECT MAX (UU290020) FROM TUU290A2
WHERE UU290010 = C.UU100010) UNION SELECT UU290090, UU100010, UU100020,
UU100060, '3' AS UEUE_ART FROM TUU140A2 B, TUU100A2 C, TUU290A2 D,
TUU130A2 E, TUU135A2 F WHERE UU140040 IN (?, ?, ?, ?, ?, ?, ?,
?, ?, ?) AND UU130100 IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?) AND UU130110 IN (?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) AND UU140010 = UU100010
AND UU130020 LIKE ? AND UU130260 LIKE ? AND UU130270 LIKE ? AND
UU100030 < CURRENT DATE - ? DAYS AND UU100030 > CURRENT DATE - ? DAYS
AND UU100060 < 5 AND UU100240 IN (?, ?, ?) AND UU100020 = UU130010
AND UU100010 = UU290010 AND UU135010 = UU100010 AND UU135060 = 'A' AND
UU135100 IN ('1', '3') AND UU135170 = ? AND UU290090 >= ? AND
UU290090 <= ? AND UU290020 = (SELECT MAX (UU290020) FROM TUU290A2
WHERE UU290010 = C.UU100010) UNION SELECT UU290090, UU100010, UU100020,
UU100060, '4' AS UEUE_ART FROM TUU140A2 B, TUU100A2 C, TUU290A2 D,
TUU130A2 E, TUU135A2 F WHERE UU140040 IN (?, ?, ?, ?, ?, ?, ?,
?, ?, ?) AND UU130100 IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?) AND UU130110 IN (?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) AND UU140010 = UU100010
AND UU130020 LIKE ? AND UU130260 LIKE ? AND UU130270 LIKE ? AND
UU100030 < CURRENT DATE - ? DAYS AND UU100030 > CURRENT DATE - ? DAYS
AND UU100060 < 5 AND UU100240 IN (?, ?, ?) AND UU100020 = UU130010
AND UU100010 = UU290010 AND UU135010 = UU100010 AND UU135060 = 'A' AND
UU135100 IN ('1', '3') AND UU135170 = ? AND UU290090 >= ? AND
UU290090 <= ? AND UU290020 = (SELECT MAX (UU290020) FROM TUU290A2
WHERE UU290010 = C.UU100010) UNION SELECT UU290090, UU100010, UU100020,
UU100060, '5' AS UEUE_ART FROM TUU140A2 B, TUU100A2 C, TUU290A2 D,
TUU130A2 E, TUU135A2 F WHERE UU140040 IN (?, ?, ?, ?, ?, ?, ?,
?, ?, ?) AND UU130100 IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?) AND UU130110 IN (?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) AND UU140010 = UU100010
AND UU130020 LIKE ? AND UU130260 LIKE ? AND UU130270 LIKE ? AND
UU100030 < CURRENT DATE - ? DAYS AND UU100030 > CURRENT DATE - ? DAYS
AND UU100060 < 5 AND UU100240 IN (?, ?, ?) AND UU100020 = UU130010
AND UU100010 = UU290010 AND UU135010 = UU100010 AND UU135060 = 'A' AND
UU135100 IN ('1', '3') AND UU135170 = ? AND UU290090 >= ? AND
UU290090 <= ? AND UU290020 = (SELECT MAX (UU290020) FROM TUU290A2
WHERE UU290010 = C.UU100010) FOR FETCH ONLY
;