zOs/SQL/EXPLAINS

with s as
(
  select substr(program_name, 1, 8) prg, lines
    ,       cast(substr(stmt_text, 1, 2000) as varchar(2000)) stmt
    , cached_ts, stat_execB, literal_repl
    from dsn_statement_cache_table
    where program_name = 'YVV3240'
)
, t as
(
  select s.*
      , case when posStr(stmt, '''') > 0 then posStr(stmt, '''')
             when posStr(stmt, '= 1') > 0 then posStr(stmt, '= 1')
             when posStr(stmt, '= 2') > 0 then posStr(stmt, '= 2')
             when posStr(stmt, '= 3') > 0 then posStr(stmt, '= 3')
             when posStr(stmt, '= 4') > 0 then posStr(stmt, '= 4')
             when posStr(stmt, '= 5') > 0 then posStr(stmt, '= 5')
             when posStr(stmt, '= 6') > 0 then posStr(stmt, '= 6')
             when posStr(stmt, '= 7') > 0 then posStr(stmt, '= 7')
             when posStr(stmt, '= 8') > 0 then posStr(stmt, '= 8')
             when posStr(stmt, '= 9') > 0 then posStr(stmt, '= 9')
             else 200 end firstA
    from s
)
select prg, lines, count(*)
       , avg((days(current date) - days(cached_ts)) * 86400
          +  midnight_seconds(current timestamp)
            -midnight_seconds(cached_ts)) cacheAgeSes
       , avg(real(stat_execB)) execB
       , min(literal_repl), max(literal_repl)
       , firstA, min(stmt) stmtMin
    from t
    group by prg, lines, firstA, substr(stmt,1 , firstA)
    order by prg, lines, min(stmt)
;x;
--- explain dyn statement cache ----------------------------------------
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;
delete from A540769.dsn_statement_cache_table;
explain stmtCache all;
commit;
select count(*) from dsn_statement_cache_table;
select count(*), collid
    from dsn_statement_cache_table
    group by collid
;
select count(*), program_name, lines
    from dsn_statement_cache_table
    group by program_name, lines
;
select substr(program_name, 1, 8) prg, lines, count(*)
       , avg((days(current date) - days(cached_ts)) * 86400
          +  midnight_seconds(current timestamp)
            -midnight_seconds(cached_ts)) cacheAgeSes
       , avg(real(stat_execB)) execB
       , min(literal_repl), max(literal_repl)
    from dsn_statement_cache_table
    where program_name = 'YVV3240'
    group by collid, program_name, lines
;x;
;x;
select count(*), collid
    from dsn_statement_cache_table
    group by collid
;x;
select count(*), group_member
    from dsn_statement_cache_table
    group by group_member;
;x;
    select *
        from cmnbatch.plan_view2Det
        where     collid     = 'MF'
            and progName     = 'MF7010'
            and explain_time = '2011-08-30-14.14.59.234601'
        order by collid, progName, version, explain_time,
                 queryno, qBlockNo, planno, mixopSeq
        with ur
;
select * from  plan_view1
    order by -- collid, progName, version, explain_time,
             queryNo, qblockno, planno, mixOpSeq
;
select * from  plan_view2
    order by -- collid, progName, version, explain_time,
             queryNo, qblockno, planno, mixOpSeq
;
select * from  plan_view2det
    order by -- collid, progName, version, explain_time,
             queryNo, qblockno, planno, mixOpSeq
;
select *
    from plan_viewPred
    order by collid, progName, explain_time,
             queryNo, qBlockNo, predNo, orderNo, mixOpSeqNo
    with ur
;
rollback
;;;;