zOs/SQL/PDBPKGHO

set current path oa1p;
select
       trunc_timestamp(timestamp, 'hh'), subsystem_id,
       fosFmtE7(sum(OCCURRENCES ))   "occ",
       fosFmtE7(sum(SQL_STMTS_ISSUED))   "stmts",
       fosFmtE7(sum(CLASS7_CPU_TOTAL )) "c7cpu",
       fosFmtE7(sum(CLASS7_CPU_TOTAL )/3600) "c7proc",
       fosFmtE7(sum(CLASS7_CPU_TOTAL)
           /max(1,sum(SQL_STMTS_ISSUED)))  "cpu/stm",
       fosFmtE7(sum(CLASS7_ELAPSED ))   "c7ela",
       fosFmtE7(sum(CLASS7_ELAPSED )
           /max(1, sum(SQL_STMTS_ISSUED)))  "ela/stm",
  --   substr(PCK_COLLECTION_ID, 1, 8) coll,
  --   substr(PCK_ID, 1, 8) pkg,
       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"
 --    min(subsystem_id) subV,
 --    max(subsystem_id) subB
    from rz2hh.tacct_program
  --where pck_id = 'YCKSCCA'
    where   subsystem_id like 'DOF%'
        and  timestamp  between '2012-05-29-00.00.00'
                            and '2012-05-29-23.59.00'
   --   and  dayofweek_iso(timestamp) <= 5
   --   and  hour(timestamp) between 9 and 11
             -- timestamp between '2011-07-25-01.00.00'
             --               and '2011-07-28-23.00.00'

    group by  trunc_timestamp(timestamp, 'hh'), subsystem_id
  --group by                       PCK_COLLECTION_ID,PCK_id
  --                ,   date(timestamp)
  --   case when timestamp < '2011-08-23-12.45.00' then 'alt'
  --                                               else 'neu' end
 -- order by  1 desc
 -- order by                                 PCK_COLLECTION_ID,PCK_id
 --              , min(TIMESTAMP) desc
    order by trunc_timestamp(timestamp, 'hh'), subsystem_id
    fetch first 200 rows only with ur
;;;
select
       date(timestamp),
       fosFmtE7(sum(OCCURRENCES ))   "occ",
    -- max(SUBSYSTEM_ID   )),
    -- date(TIMESTAMP),
    -- case when timestamp < '2011-08-23-12.45.00' then 'alt'
    --                                             else 'neu' end,
       PLAN_NAME ,
       substr(PCK_COLLECTION_ID, 1, 8) coll,
       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 )),
       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(GROUP_NAME ))
       fosFmtE7(sum(CLASS7_SU_CPU  ))        "c7SU",
       min(left(subsystem_id, 3)) sub
    from rz2dd.tacct_program
    where pck_id = 'YCKSCCA'
        and subsystem_id like 'DOF%'
        and       timestamp > '2011-08-01-00.00.00'
             -- timestamp between '2011-07-25-01.00.00'
             --               and '2011-07-28-23.00.00'

    group by            plan_name, PCK_COLLECTION_ID,PCK_id
                    ,   date(timestamp)
  --   case when timestamp < '2011-08-23-12.45.00' then 'alt'
  --                                               else 'neu' end
    order by                      plan_name, PCK_COLLECTION_ID,PCK_id
                 , min(TIMESTAMP) desc
 -- order by sum(CLASS7_CPU_TOTAL)/sum(SQL_STMTS_ISSUED)
;;;
set current path oa1p;
select
    -- max(SUBSYSTEM_ID   )),
    -- date(TIMESTAMP),
       PLAN_NAME ,
    -- substr(PCK_COLLECTION_ID, 1, 8) coll,
    -- 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(OCCURRENCES ))   "occ",
       fosFmtE7(sum(SQL_STMTS_ISSUED )) "sqls",
       fosFmtE7(sum(CLASS7_ELAPSED ))   "c7ela",
       fosFmtE7(sum(CLASS7_CPU_TOTAL )) "c7cpu",
    -- fosFmtE7(sum(CLASS8 )),
       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(GROUP_NAME ))
       fosFmtE7(sum(CLASS7_SU_CPU  ))        "c7SU",
       fosFmtE7(min(CLASS7_ELAPSED / SQL_STMTS_ISSUED)) "ela/st<",
       fosFmtE7(max(CLASS7_ELAPSED / SQL_STMTS_ISSUED)) "ela/st>"
    from rz2xx.tacct_program
    where pck_id = 'YCKSCCA'
        and subsystem_id like 'DOF%'
    --  and     timestamp > current timestamp - 7 day
            --  timestamp between '2010-01-10-00.00.00'
            --                and '2010-02-20-00.00.00'

    group by plan_name ,
              floor(3. * log10(
                   max(1e-9, CLASS7_ELAPSED / SQL_STMTS_ISSUED)))
    order by plan_name, min(CLASS7_ELAPSED / SQL_STMTS_ISSUED)
    with ur
;;;