zOs/SQL/PDBGENPW

set current path = oa1p;
set current path = oa1p;
with s as(
  select real(select+open+fetch+insert+update+delete) sqls,
         real(max(1, commit+rollback)) cr,
         g.*
    from RZ2XX.TACCT_GENERAL g
    where plan_name in ('PW5200', 'PW5220')
      and subsystem_id like 'DOF%'
      and timestamp between '2012-01-05-00.00.00'
                        and '2012-12-02-00.00.00'
)
select plan_name,
      fosFmte7(min(class1_elapsed/cr)                ) "c1ela min",
      fosFmte7(max(class1_elapsed/cr)                ) "c1ela max",
      fosFmte7(sum(class1_elapsed)/sum(cr)           ) "c1ela/coR",
      fosFmte7(sum(class2_elapsed)/sum(cr)           ) "c2ela/coR",
      fosFmte7(sum(class1_cpu_total)/sum(cr)) "c1cpu/coR",
      fosFmte7(sum(class2_cpu_total)/sum(cr)) "c2cpu/coR",
      fosFmte7(sum(CLASS3_ASYNCH_IXL)  /sum(cr)           ) "c3IXL/coR",
      int(sum(occurrences))  "occur",
      int(sum(commit))  "commit",
      int(sum(rollback))  "rollback",
      fosFmte7(sum(sqls)/sum(cr)           ) "sqls/coR",
          fosFmte7(sum(class3_global_cont)/sum(cr)) "c2gloCon",
          fosFmte7(sum(class3_DB_IO)/sum(cr)) "c3dbIo",
          fosFmte7(sum(class3_LOG_WRT_IO)/sum(cr)) "c2logIo",
          fosFmte7(sum(real(OPEN_CLOSE_SUSP))/sum(cr)) "opClo",
          fosFmte7(sum(CLASS3_OPEN_CLOSE)/sum(cr)) "c3opClo",
          fosFmte7(sum(real(SERV_TASK_SUSP))/sum(cr)) "serTa",
          fosFmte7(sum(CLASS3_SERV_TASK)/sum(cr)) "c3serTa",
          fosFmte7(sum(real(commit))/sum(cr)) "commit",
          fosFmte7(sum(real(rollback))/sum(cr)) "rollback",
          fosFmte7(sum(real(select))/sum(cr)) "select",
          fosFmte7(sum(real(open) )/sum(cr)) "open",
          fosFmte7(sum(real(fetch) )/sum(cr)) "fetch",
          fosFmte7(sum(real(insert))/sum(cr)) "insert",
          fosFmte7(sum(real(update))/sum(cr)) "update",
          fosFmte7(sum(real(delete))/sum(cr)) "delete",
          fosFmte7(sum(real(INCREMENTAL_BIND))/sum(cr))"incrBi",
          fosFmte7(sum(real(PROGRAMS))      /sum(cr)) "pkgs",
          min(timestamp), max(timestamp)
    from s
    group by plan_name, floor(log10(class1_elapsed/max(cr, 1))*3)
    order by plan_name, min(class1_elapsed/max(cr, 1))
;;;
??????????????
with s as(
  select real(select+open+fetch+insert+update+delete) sqls,
         real(max(1, commit+rollback)) cr,
         g.*
    from RZ2HH.TACCT_GENERAL g
    where plan_name in ('PW5200', 'PW5220')
      and subsystem_id like 'DOF%'
      and timestamp between '2012-01-01-00.00.00'
                        and '2012-12-02-00.00.00'
)
select trunc_timestamp(timestamp,'HH'),
      plan_name,
      fosFmte7((class1_elapsed)/(cr)                 ) "c1ela/coR",
      fosFmte7((class2_elapsed)/(cr)                 ) "c2ela/coR",
      fosFmte7((class1_cpu_total)/(cr)) "c1cpu/coR",
      fosFmte7((class2_cpu_total)/(cr)) "c2cpu/coR",
      fosFmte7((CLASS3_ASYNCH_IXL)     /(cr)              ) "c3IXL/coR",
      int((occurrences))     "occ",
      int((commit))     "commit",
      int((rollback))     "rollBack",
      fosFmte7((sqls)/(cr)                 ) "sqls/coR",
          fosFmte7((class3_global_cont)/(cr)) "c2gloCon",
          fosFmte7((class3_DB_IO)/(cr)) "c3dbIo",
          fosFmte7((class3_LOG_WRT_IO)/(cr)) "c2logIo",
          fosFmte7((real(OPEN_CLOSE_SUSP))/(cr)) "opClo",
          fosFmte7((CLASS3_OPEN_CLOSE)/(cr)) "c3opClo",
          fosFmte7((real(SERV_TASK_SUSP))/(cr)) "serTa",
          fosFmte7((CLASS3_SERV_TASK)/(cr)) "c3serTa",
          fosFmte7((real(commit))/(cr)) "commit",
          fosFmte7((real(rollback))/(cr)) "rollback",
          fosFmte7((real(select))/(cr)) "select",
          fosFmte7((real(open) )/(cr)) "open",
          fosFmte7((real(fetch) )/(cr)) "fetch",
          fosFmte7((real(insert))/(cr)) "insert",
          fosFmte7((real(update))/(cr)) "update",
          fosFmte7((real(delete))/(cr)) "delete",
        fosFmte7((real(INCREMENTAL_BIND))/(cr))"incrBi",
          fosFmte7((real(PROGRAMS))         /(cr)) "pkgs"
    from s
 -- group by trunc_timestamp(timestamp,'HH'), plan_name
    order by plan_name, (class1_elapsed)/(cr) desc
    fetch first 1000 rows only
;;;
;;;
select count(*), plan_name
    from rz1xx.tacct_program
    where pck_id = 'YWPR2PX' and group_Name = 'DE0G'
    group by plan_name
    with ur
set current path = oa1p;
with i as
(
select plan_name
     , sum(class2_elapsed) ela
     , sum(class2_cpu_total) cpu
     , sum(commit) com
     , sum(select) sel
     , sum(open) op
     , sum(fetch) fet
     , min(timestamp) von
     , max(timestamp) bis
    from rz2mm.tacct_general
    where timestamp > '2011-01-01 00:00:00' and commit > 0
        and plan_name in ('PW0602', 'PW0408')
    group by plan_name, floor( 2*log10(class2_elapsed / commit))
)
select plan_name
     , fosFmte7(ela / com)   "ela/com"
     , fosFmte7(com) commit
     , fosFmtE7(cpu / com)   "cpu/com"
     , fosFmtE7(sel / com)   "sel/Com"
     , fosFmtE7(op  / com)   "ope/Com"
     , fosFmtE7(fet / com)   "fet/Com"
     , von , bis
    from i
    order by plan_name, ela/com desc
    with ur