zOs/SQL/PDBPKGRM

set current path oa1p;
select
       trunc_timestamp(timestamp, 'hh'),
       substr(PCK_ID, 1, 8) pkg,
       fosFmtE7(sum(CLASS7_ELAPSED ) /sum(SQL_STMTS_ISSUED))  "ela/stm",
       fosFmtE7(sum(CLASS7_CPU_TOTAL)/sum(SQL_STMTS_ISSUED))  "cpu/stm",
       fosFmtE7(sum(SQL_STMTS_ISSUED )) "sqls",
       fosFmtE7(sum(CLASS7_ELAPSED ))   "c7ela",
       fosFmtE7(sum(CLASS7_CPU_TOTAL )) "c7cpu",
       fosFmtE7(sum(CLASS8_LOCK_LATCH )) "c8loLa" ,
       fosFmtE7(sum(CLASS8_SYNC_IO ))    "c8synIO",
       fosFmtE7(sum(CLASS8_OTHER_READ )) "c8othRe",
       fosFmtE7(sum(CLASS8_OTHER_WRITE)) "c8othWr",
       fosFmtE7(sum(CLASS8_SERV_TASK ))  "c8SrvTa",
       fosFmtE7(sum(BP_GETPAGES ))       "getPg",
       fosFmtE7(sum(SYNC_READ ))         "synRead",
       fosFmtE7(sum(SEQ_PREFETCH ))      "seqPref",
       fosFmtE7(sum(LIST_PREFETCH ))     "listPre",
       fosFmtE7(sum(DYN_PREFETCH ))      "dynPref",
       fosFmtE7(sum(ASYNC_READ ))        "asyRead",
       fosFmtE7(sum(BUFFER_UPDATE ))     "bufUpd",
       fosFmtE7(sum(SYNC_WRITE ))        "synWrit",
       fosFmtE7(sum(PAR_GETPAGES_FAIL )) "gePgFai",
       fosFmtE7(sum(CLASS7_SU_CPU  ))        "c7SU",
       fosFmtE7(sum(OCCURRENCES ))   "occ",
       min(subsystem_id) subMin ,
       max(subsystem_id) subMax ,
       min(timestamp) tstMin,
       max(timestamp) tstMax
    from rz2hh.tacct_program
    where pck_id =   'YRMVER'
        and subsystem_id like 'DOF%'
        and       timestamp > current timestamp -  5  days
    group by pck_id, trunc_timestamp(timestamp, 'hh')
    having sum(SQL_STMTS_ISSUED) >= 1e3
    order by 1 desc
;;;