zOs/SQL/EXPLAIDP

set current sqlid = 'A540769';
delete from A540769.plan_table;
delete from A540769.dsn_filter_table;
delete from A540769.dsn_predicat_table;
delete from A540769.dsn_detcost_table;
explain plan set queryno = 1   for
select *
     FROM   oa1t.VDP080A1 a
     WHERE a."orderId" = ?
     and A."requestTimestamp"= (select max(B."requestTimestamp")
                                     from oa1t.VDP080A1 B
                                where B."orderId" = ?)
;
explain plan set queryno = 2   for
select *
     FROM   oa1t.VDP080A1 a
     WHERE a."orderId" = ?
     order by A."requestTimestamp" desc
--   fetch first 1 row only
;
explain plan set queryno = 11   for
select *
     FROM   oa1t.VDP080A1 a
     WHERE a."orderId" = ?
     and A."requestTimestamp"= (select min(B."requestTimestamp")
                                     from oa1t.VDP080A1 B
                                where B."orderId" = ?)
;
explain plan set queryno = 12   for
select *
     FROM   oa1t.VDP080A1 a
     WHERE a."orderId" = ?
     order by A."requestTimestamp" asc
 --  fetch first 1 row only
;
select * from  plan_view2
    order by applname, progname, queryNo, qblockno, planno, mixOpSeq
;
select * from  plan_view2Det
    order by applname, progname, queryNo, qblockno, planno, mixOpSeq
;
select *
    from plan_viewPred
    order by collid, progName, applName, explain_time,
             queryNo, qBlockNo, planno, mixOpSeqNo,
             stage, orderNo
;
rollback
;;;
select * from  cmnBatch.plan_view1
 where progName in ('DP071@I')  and collid     = 'DP'
       and bind_time = '2011-01-11-14.16.02.848410'
       and  queryno in (554, 582)
  --   and  version = 'DBH000012659B91C5F'
    order by bind_time desc,
    applname, progname, queryNo, qblockno, planno,
             tname
;
select * from  cmnBatch.plan_view1
 where progName in ('DP071@I')  and collid     = 'DP'
       and bind_time = '2010-11-12-18.49.51.585790'
       and  queryno in (542, 570)
  --   and  version = 'DBH000012659B91C5F'
    order by bind_time desc,
    applname, progname, queryNo, qblockno, planno,
             tname
;  ;
set current sqlid = 'A540769';
delete from plan_table;
explain plan set queryno = 542 for
      select *
      from oa1p.tdp021a1 b
      where  b.dp02162    = ?
;
explain plan set queryno = 570 for
      select *
      from   oa1p.tdp020a1 c
      where  c.dp02005 = ?
;
select * from  plan_view2
    order by applname, progname, queryNo, qblockno, planno
;
rollback
;
select count(*), dp02005
      from oa1p.tdp020a1 b
      group by dp02005
      order by 1 desc
      with ur
;
select count(*), dp02162
      from oa1p.tdp021a1 b
      group by dp02162
      order by 1 desc
      with ur