zOs/SQL/EXPLAIPV

set current sqlid = 'S100447';
--- versionen, compiles und explains eines packages -------------------
select *
    from S100447.plan_view0
    where   collid       = 'PV'
        and prog         = 'A5PO356'
    order by pcBi  desc, bind_Time desc
    with ur
;        -- 2013-06-05-09.38.09.869603
select *
    from S100447.plan_table
 -- from S100447.plan_view1
 -- from S100447.plan_view2
 -- from S100447.plan_view2Det
    where     collid     = 'PV'
        and progName     = 'A5PO356'
        and bind_time  in ('2013-06-21-14.27.22.673306'
                          ,'2013-06-04-16.09.18.365505'
                          )
        and queryno in (       50401)
    order by collid, progName, version, bind_time,
             queryno, qBlockNo, planno, mixopSeq
    with ur
;
--- temporary explain --------------------------------------------------
set current sqlid = 'A540769';
delete from A540769.plan_table;
delete from A540769.DSN_STATEMNT_TABLE;
delete from A540769.DSN_DetCost_TABLE ;
delete from A540769.dsn_filter_Table  ;
delete from A540769.dsn_predicat_table;
set current schema oa1a;
explain plan set queryno = 30991   for
INSERT INTO      PRWRK21 ( ORDART ) SELECT DISTINCT ORDART
  FROM      PRORDE QUERYNO 30991
;
explain plan set queryno = 50401   for
SELECT PRDOKT1 . ABRUFID
  FROM PRDOKT1
  WHERE PRDOKT1 . ABRUFID = ? AND EXISTS ( SELECT *
      FROM PRWRK23
      WHERE     PRWRK23 . POOL = PRDOKT1.POOL
            AND PRWRK23 . DATEI = PRDOKT1.DATEI
    ) FETCH FIRST 1 ROW ONLY QUERYNO 50401
;
explain plan set queryno = 50481   for
SELECT PRDOKT1 . ABRUFID
  FROM PRDOKT1
  WHERE PRDOKT1 . ABRUFID = ? AND EXISTS ( SELECT 1
      FROM PRWRK23
      WHERE     PRWRK23 . POOL = PRDOKT1.POOL
            AND PRWRK23 . DATEI = PRDOKT1.DATEI
    ) FETCH FIRST 1 ROW ONLY QUERYNO 50401
;
explain plan set queryno = 50491   for
SELECT PRDOKT1 . ABRUFID
  FROM PRDOKT1 join PRWRK23
      on        PRWRK23 . POOL = PRDOKT1.POOL+0
            AND PRWRK23 . DATEI = PRDOKT1.DATEI+0
  WHERE PRDOKT1 . ABRUFID = ?
  FETCH FIRST 1 ROW ONLY QUERYNO 50401
;
explain plan set queryno = 50493   for
SELECT PRDOKT1 . ABRUFID
  FROM PRDOKT1 join PRWRK23
      on        PRWRK23 . POOL + 0 = PRDOKT1.POOL
            AND PRWRK23 . DATEI + 0= PRDOKT1.DATEI
  WHERE PRDOKT1 . ABRUFID = ?
  FETCH FIRST 1 ROW ONLY QUERYNO 50401
;
explain plan set queryno = 50497   for
WITH DSN_INLINE_OPT_HINT(TABLE_NAME, join_seq) as
(
   values  ('TPV407A1',    1)
         , ('TPV423A1',    2)

)
SELECT PRDOKT1 . ABRUFID
  FROM PRDOKT1 join PRWRK23
      on        PRWRK23 . POOL  = PRDOKT1.POOL
            AND PRWRK23 . DATEI = PRDOKT1.DATEI
  WHERE PRDOKT1 . ABRUFID = ?
  FETCH FIRST 1 ROW ONLY QUERYNO 50401
;
set current schema A540769;
select * from  plan_view1
    order by -- collid, progName, version, bind_time,
             queryNo, qblockno, planno, mixOpSeq
;
select * from  plan_view2
    order by -- collid, progName, version, bind_time,
             queryNo, qblockno, planno, mixOpSeq
;
select * from  plan_view2det
    order by -- collid, progName, version, bind_time,
             queryNo, qblockno, planno, mixOpSeq
;
select *
    from plan_viewPred
    order by --collid, progName, applName, explain_time,
             queryNo, qBlockNo, planno,
             stage, predNo
    with ur
;
rollback
;;;;
set current schema oa1a;
insert into PRWRK23 values(0,0,0) ;
insert into PRWRK23 values(1,1,1) ;
insert into PRWRK23 values(2,2,2) ;
insert into PRWRK23 values(3,3,3) ;
insert into PRWRK23 values(4,4,4) ;
insert into PRWRK23 values(5,5,5) ;
insert into PRWRK23 values(6,6,6) ;
insert into PRWRK23 values(7,7,7) ;
insert into PRWRK23 values(8,8,8) ;
insert into PRWRK23 values(9,9,9) ;
select count(*) from PRWRK23 ;

select current timestamp from sysibm.sysdummy1;
WITH DSN_INLINE_OPT_HINT(TABLE_NAME, join_seq) as
(
   values  ('TPV407A1',    1)
         , ('TPV423A1',    2)

)
SELECT PRDOKT1 . ABRUFID
  FROM PRDOKT1 join PRWRK23
      on        PRWRK23 . POOL  = PRDOKT1.POOL
            AND PRWRK23 . DATEI = PRDOKT1.DATEI
  WHERE PRDOKT1 . ABRUFID = 820
  FETCH FIRST 1 ROW ONLY QUERYNO 50401
;
select current timestamp from sysibm.sysdummy1;
;
rollback
;x;
WITH DSN_INLINE_OPT_HINT(TABLE_NAME, join_seq) as
(
   values  ('TPV407A1',    1)
         , ('TPV423A1',    2)

)
SELECT PRDOKT1 . ABRUFID
  FROM PRDOKT1 join PRWRK23
      on        PRWRK23 . POOL  = PRDOKT1.POOL
            AND PRWRK23 . DATEI = PRDOKT1.DATEI
  WHERE PRDOKT1 . ABRUFID = ?
  FETCH FIRST 1 ROW ONLY QUERYNO 50401
;
set current schema oa1a;
insert into PRWRK23 values(0,0,0) ;
insert into PRWRK23 values(1,1,1) ;
insert into PRWRK23 values(2,2,2) ;
insert into PRWRK23 values(3,3,3) ;
insert into PRWRK23 values(4,4,4) ;
insert into PRWRK23 values(5,5,5) ;
insert into PRWRK23 values(6,6,6) ;
insert into PRWRK23 values(7,7,7) ;
insert into PRWRK23 values(8,8,8) ;
insert into PRWRK23 values(9,9,9) ;
select count(*) from PRWRK23 ;

select current timestamp from sysibm.sysdummy1;
SELECT PRDOKT1 . ABRUFID
  FROM PRDOKT1
  WHERE PRDOKT1 . ABRUFID = 820 AND EXISTS ( SELECT *
      FROM PRWRK23
      WHERE     PRWRK23 . POOL = PRDOKT1.POOL
            AND PRWRK23 . DATEI = PRDOKT1.DATEI
    ) FETCH FIRST 1 ROW ONLY QUERYNO 50401
    with ur
;
select current timestamp from sysibm.sysdummy1;
;
rollback
;xM;
select count(*), abrufid
    from OA1A.TPV407A1
    group by abrufid
    order by 1 desc
    fetch first 200  rows only
    with ur
;x;
set current schema oa1a;
SELECT count(*)
  FROM PRDOKT1
   where ABRUFID = 820
  ;
select current timestamp from sysibm.sysdummy1;
SELECT PRDOKT1 . ABRUFID
  FROM PRDOKT1
  WHERE PRDOKT1 . ABRUFID = 820 AND EXISTS ( SELECT *
      FROM PRWRK23
      WHERE     PRWRK23 . POOL = PRDOKT1.POOL
            AND PRWRK23 . DATEI = PRDOKT1.DATEI
    ) FETCH FIRST 1 ROW ONLY QUERYNO 50401
    with ur
;
select current timestamp from sysibm.sysdummy1;
;xM;
; x;
select * from sysibm.sysPackage WHERE NAME  = 'A5PO356'
;
select progName, version, count(*)
from S100447.plan_table WHERE progNAME  = 'A5PO356'
            group by progName, version
;
set current sqlid = 'S100447';
--- versionen, compiles und explains eines packages -------------------
select *
    from S100447.plan_view0
    where   collid       = 'PV'
        and prog         = 'A5PO356'
    order by pcBi  desc, bind_Time desc
    with ur
;x;
DELETE
FROM PLAN_TABLE
WHERE PROGNAME  = 'A5PO356'
  AND BIND_TIME <> '2013-06-04-16.09.18.365505'
;
DELETE
FROM PLAN_TABLE
WHERE PROGNAME  = 'A5PO356 '
  AND QUERYNO   = 50401
  AND TABNO    IN (0,3)
  AND BIND_TIME = '2013-06-04-16.09.18.365505'
;
UPDATE PLAN_TABLE
SET OPTHINT = 'PRIMA',
    PLANNO  = 1,
    METHOD  = 0
WHERE CREATOR   = 'OA1A'
  AND PROGNAME  = 'A5PO356 '
  AND QUERYNO   = 50401
  AND TABNO     = 1
  AND BIND_TIME = '2013-06-04-16.09.18.365505'
;
UPDATE PLAN_TABLE
SET OPTHINT         = 'PRIMA',
    QBLOCKNO        = 1,
    PLANNO          = 2,
    METHOD          = 1,
    MATCHCOLS       = 2,
    QBLOCK_TYPE     = 'SELECT',
    PARENT_QBLOCKNO = 0,
    PARENT_PLANNO   = 0
WHERE CREATOR   = 'OA1A'
  AND PROGNAME  = 'A5PO356 '
  AND QUERYNO   = 50401
  AND TABNO     = 2
  AND BIND_TIME = '2013-06-04-16.09.18.365505'
;
commit
;x;
--- ausgewählte Explains analysieren
select *
 -- from cmnbatch.plan_view1
    from cmnbatch.plan_view2
 -- from cmnbatch.plan_view2Det
    where     collid     = 'MF'
        and progName     = 'MF7010'
        and bind_time    = '2011-08-30-14.14.59.234601'
    order by collid, progName, version, bind_time,
             queryno, qBlockNo, planno, mixopSeq
    with ur
;
select *
    from cmnbatch.plan_viewPred
    where     collid     = 'MF'
        and progName     = 'MF7010'
        and bind_time    = '2011-08-30-14.14.59.234601'
    order by collid, progName, applName, bind_time,
             queryNo, qBlockNo, planno,
             stage, predNo
    with ur
;