zOs/SQL/EXPLAIBP

set current sqlid = 'A540769';
delete from plan_table;
explain plan set queryno = 713 for
SELECT A.BP20401, A.BP20404, A.BP20410, A.BP20411, B.BP20004,
    B.BP20017, B.BP20013, B.BP20022, C.BP20501, C.BP20504, C.BP20509,
    C.BP20510, C.BP20512
  FROM oa1p.TBP204A1 A, oa1p.TBP200A1 B, oa1p.TBP205A1 c
  WHERE A.BP20401 = ? AND A.BP20402 <= ? AND A.BP20403 >= ?
      AND A.BP20405 = ?  AND A.BP20409 = 'A' AND
      B.BP20001 = A.BP20411 AND B.BP20002 <= ? AND B.BP20005 = ?
      AND B.BP20003 >= ? AND C.BP20501 = A.BP20411 AND
      C.BP20502 <= ? AND C.BP20503 >= ? AND C.BP20505 = ?
      AND ( ( C.BP20508 = 1 AND C.BP20513 <> 'LAE ' ) OR (
            C.BP20508 = 3 AND C.BP20513 = 'LAE ' ) )
;
explain plan set queryno = 1 for
    select *
        FROM
            oa1t.TBP200A1 A,
            oa1t.TBP204A1 B,
            oa1t.TBP205A1 C
        WHERE
            BP20001=BP20411
            AND BP20501=BP20411
            AND BP20002<=?
            AND BP20003>=?
            AND BP20005=?
            AND BP20402<=?
            AND BP20403>=?
            AND BP20405=?
            AND BP20502<=?
            AND BP20503>=?
            AND BP20505=?
            AND BP20401 BETWEEN ? AND ?
            AND BP20409=?
            AND BP20508=?
            AND BP20509=X'000E55C8B07D169CF6B85C8360409021C8E90001'
        ORDER BY
            BP20401,
            BP20508 FOR FETCH ONLY WITH UR
;
select * from  plan_view2
    order by applname, progname, queryNo, qblockno, planno
;
rollback
;
set current path oa1t;
select n, max(c) max, fosFmtE7(avg(real(c))) avg,
    sum(c) card, count(*) dist
from
(
          select 'bp205.5.9' n,  count(*) c
    from oa1t.TBP205A1 C
    group by BP20505, BP20509
union all select 'bp200.1' n, count(*) c
    from oa1t.TBP200A1
    group by BP20001
union all select 'bp204.11.5' n, count(*) c
    from oa1t.TBP204A1 C
    group by BP20411, BP20405
)   x
    group by n
    with ur
;;;
;;;
select count(*), BP20505, BP20509
    from oa1t.TBP205A1 C
    group by BP20505, BP20509
    order by 1 desc
    fetch first 10 rows only
    with ur
;;;
select count(*), BP20411, BP20405
    from oa1t.TBP204A1 C
    group by BP20411, BP20405
    order by 1 desc
    fetch first 10 rows only
    with ur
;;;