zOs/SQL/TXBD491

set current sqlid = 'A540769';
delete from plan_table;
explain plan set queryno = 3 for
  select enpid, count(*)
    from bua.txbd491
    where enSasm = 30087   -- family
        and etStart >='2010-05-01-00.00.00'
        and etEnd   <='2010-07-01-00.00.00'
        and eyStep   in ('S', 'R')
        and ezItem not in (-323, -325)
    group by enPid
    with ur
;
explain plan set queryno = 5 for
  select enpid, count(*)
    from bua.txbd491
    where enSasm = 30087   -- family
        and etStart >='2010-05-01-00.00.00'
        and etEnd   <='2010-07-01-00.00.00'
        and eyStep   in ('S', 'R')
        and ezItem not in (-323, -325)
    group by enPid
    order by 2 desc
    with ur
;
explain plan set queryno = 9 for
select g.* from
  ( select enpid, count(*) cnt
    from bua.txbd491
    where enSasm = 30087   -- family
        and etStart >='2010-05-01-00.00.00'
        and etEnd   <='2010-07-01-00.00.00'
        and eyStep   in ('S', 'R')
        and ezItem not in (-323, -325)
    group by enPid
  ) g, A540769.pids s
    where g.enPid = s.enPid
    order by 2 desc
    with ur
;
select current timestamp from sysibm.sysdummy1;
select * from plan_view2
    where queryno in ( 9  , 222)
    order by applname, progname, queryNo, qblockno, planno
;
select * from plan_table
    where queryno in ( 9  , 222)
    order by applname, progname, queryNo, qblockno, planno
;
rollback;
 x
select current timestamp from sysibm.sysDummy1;
select n, sum(c) tot, min(c) min, max(c) max, count(*) distinct
from
    ( select 'enPid' n , count(*) c, enPid g
          from bua.txbd491 group by enPid
      union all select 'enSasm' n , count(*) c, char(enSasm) g
          from bua.txbd491 group by enSasm
      union all select 'eyStep' n , count(*) c, eyStep g
          from bua.txbd491 group by eyStep
      union all select 'ezItem' n , count(*) c, char(ezItem) g
          from bua.txbd491 group by ezItem
      union all select 'pidSasm' n , count(*) c, enPid || char(enSasm) g
          from bua.txbd491 group by enPid, enSasm
    ) g
    group by n
    order by n
    with ur
;
select current timestamp from sysibm.sysDummy1;
xelect sum(c) tot, min(c) min, max(c) max, count(*) distinct
from
    ( select count(*) c, enpid g
          from bua.txbd491
          group by enpid
    ) g
    with ur
;
xelect current timestamp from sysibm.sysDummy1;
xelect sum(c) tot, min(c) min, max(c) max, count(*) distinct
from
    ( select count(*) c, ENSASM g
          from bua.txbd491
          group by ENSASM
    ) g
    with ur
;
-----------------------------------------------------------------
--  test 1: query as is with dsnTep2
--       elapsed time  6'38, cpu 65s (explain: 98s)
select current timestamp from sysibm.sysDummy1;
select enpid, count(*)
  from bua.txbd491
  where enSasm = 30087   -- family
      and etStart >='2009-01-01-00.00.00'
      and etEnd   < '2010-01-01-00.00.00'
      and eyStep   in ('S', 'R')
      and ezItem not in (-323, -325)
  group by enPid
  order by 2 desc
  with ur
;
select current timestamp from sysibm.sysDummy1;
--
-- output 7503 rows
--       S104527    |        1306799 |
--       P064505    |           3999 |
--       P092258    |           3469 |
-----------------------------------------------------------------
--  test 2: query with join to temporary key table is with dsnTep2
--       elapsed time   51s, cpu  9s (explain: 98s)
select current timestamp from sysibm.sysDummy1;
create table A540769.pids
    as (select enPid, count(*) cnt from bua.txbd491
        group by enPid) with no data in DA540769.DUMMY;
insert into A540769.pids
    select enPid, count(*) from bua.txbd491 group by enPid with cs;
commit;
select count(*) from A540769.pids;
select current timestamp from sysibm.sysDummy1;

select g.* from
  ( select enpid, count(*) cnt
    from bua.txbd491
    where enSasm = 30087   -- family
        and etStart >='2009-01-01-00.00.00'
        and etEnd   < '2010-01-01-00.00.00'
        and eyStep   in ('S', 'R')
        and ezItem not in (-323, -325)
    group by enPid
  ) g, A540769.pids s
    where g.enPid = s.enPid
    order by 2 desc
    with ur
;
select current timestamp from sysibm.sysDummy1;
--
-- outpjut 7503 rows
--       S104527    |        1306799
--       P064505    |           3999
--       P092258    |           3469
--       A301780    |           2771