zOs/SQL/PDBPKGKC

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 = 'YKCLOGS'
        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
;;;
set current path oa1p;
select
       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 rz2xx.tacct_program
    where pck_id = 'YCKSCCA'
        and subsystem_id like 'DOF%'
        and       timestamp > '2011-08-23-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,
       case when timestamp < '2011-08-23-12.45.00' then 'alt'
                                                   else 'neu' end
 -- order by TIMESTAMP desc, plan_name, PCK_COLLECTION_ID,PCK_id
 -- order by sum(CLASS7_CPU_TOTAL)/sum(SQL_STMTS_ISSUED)
;;;