zOs/SQL/PDBGENAV

set current path = oa1p;
with s as(
  select real(select+open+fetch+insert+update+delete) sqls, g.*
    from RZ2XX.TACCT_GENERAL g
    where plan_name = 'AV0142'  and subsystem_id like 'DOF%'
      and timestamp between '2012-09-05-00.00.00'
                        and '2012-12-02-00.00.00'
)
select
      fosFmte7(min(class1_elapsed)                   ) "c1ela min",
      fosFmte7(max(class1_elapsed)                   ) "c1ela max",
      fosFmte7(sum(class1_elapsed)/sum(Occurrences)  ) "c1ela/occ",
      fosFmte7(sum(class2_elapsed)/sum(Occurrences)  ) "c2ela/occ",
      fosFmte7(sum(class1_cpu_total)/sum(Occurrences)) "c1cpu/occ",
      fosFmte7(sum(class2_cpu_total)/sum(Occurrences)) "c2cpu/occ",
      fosFmte7(sum(CLASS3_ASYNCH_IXL)  /sum(Occurrences)  ) "c3IXL/occ",
      int(sum(occurrences))  "occ",
      fosFmte7(sum(sqls)/sum(Occurrences)  ) "sqls/occ",
          fosFmte7(sum(class3_global_cont)/sum(Occurrences)) "c2gloCon",
          fosFmte7(sum(class3_DB_IO)/sum(Occurrences)) "c3dbIo",
          fosFmte7(sum(class3_LOG_WRT_IO)/sum(Occurrences)) "c2logIo",
          fosFmte7(sum(real(OPEN_CLOSE_SUSP))/sum(Occurrences)) "opClo",
          fosFmte7(sum(CLASS3_OPEN_CLOSE)/sum(Occurrences)) "c3opClo",
          fosFmte7(sum(real(SERV_TASK_SUSP))/sum(Occurrences)) "serTa",
          fosFmte7(sum(CLASS3_SERV_TASK)/sum(Occurrences)) "c3serTa",
          fosFmte7(sum(real(commit))/sum(Occurrences)) "commit",
          fosFmte7(sum(real(rollback))/sum(Occurrences)) "rollback",
          fosFmte7(sum(real(select))/sum(Occurrences)) "select",
          fosFmte7(sum(real(open) )/sum(Occurrences)) "open",
          fosFmte7(sum(real(fetch) )/sum(Occurrences)) "fetch",
          fosFmte7(sum(real(insert))/sum(Occurrences)) "insert",
          fosFmte7(sum(real(update))/sum(Occurrences)) "update",
          fosFmte7(sum(real(delete))/sum(Occurrences)) "delete",
        fosFmte7(sum(real(INCREMENTAL_BIND))/sum(Occurrences))"incrBi",
          fosFmte7(sum(real(PROGRAMS))      /sum(Occurrences)) "pkgs"
    from s
    group by floor(log10(class1_elapsed)*3)
    order by min(class1_elapsed)
;;;
  select real(select+open+fetch+insert+update+delete) sqls, g.*
    from RZ2XX.TACCT_GENERAL g
    where plan_name = 'AV0142'  and subsystem_id like 'DOF%'
      and timestamp between '2012-07-01-00.00.00'
                        and '2012-12-02-00.00.00'
    order by timestamp desc
;x;
with s as(
  select real(select+open+fetch+insert+update+delete) sqls, g.*
    from RZ2XX.TACCT_GENERAL g
    where plan_name = 'AV0142'  and subsystem_id like 'DOF%'
      and timestamp between '2012-09-01-00.00.00'
                        and '2012-12-02-00.00.00'
)
select  connect_id, date(timestamp) ,
      fosFmtE7(class1_cpu_total/sqls) "c1cpu/sql",
      fosFmtE7(class2_cpu_total/sqls) "c2cpu/sql",
      fosFmtE7(class1_elapsed/sqls) "c1ela/sql",
      fosFmtE7(class2_elapsed/sqls) "c2ela/sql",
      int(occurrences)   "occ",
      fosfmte7(class1_cpu_total) "c1cpu",
      fosfmte7(class2_cpu_total) "c2cpu",
      fosFmtE7(sqls) sqls,
          fosFmtE7(class2_elapsed) "c2ela",
          fosFmtE7(class2_cpu_total) "c2cpu",
          fosFmtE7(class3_global_cont) "c2gloCon",
          fosFmtE7(class3_DB_IO) "c3dbIo",
          fosFmtE7(class3_LOG_WRT_IO) "c2logIo",
          fosFmtE7(OPEN_CLOSE_SUSP) "opClo",
          fosFmtE7(CLASS3_OPEN_CLOSE) "c3opClo",
          fosFmtE7(SERV_TASK_SUSP) "serTa",
          fosFmtE7(CLASS3_SERV_TASK) "c3serTa",
          fosFmtE7(select)   "select",
          fosFmtE7(select)   "select",
          fosFmtE7(open )    "open",
          fosFmtE7(fetch )   "fetch",
          fosFmtE7(insert)   "insert",
          fosFmtE7(update)   "update",
          fosFmtE7(delete)   "delete",
          s.*
    from s
    order by connect_id, timestamp
;;;
select *
    from rz2xx.tacct_general
    where plan_name = 'AV8465'  and subsystem_id like 'DOF%'
    order by timestamp desc
    with ur
;;;
select count(*), plan_name
    from rz1xx.tacct_program
    where pck_id = 'YWPR2PX' and group_Name = 'DE0G'
    group by plan_name
    with ur