zOs/SQL/PDBPKGWA

set current path oa1p;
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_CPU_TOTAL)/sum(SQL_STMTS_ISSUED))  "cpu/stm",
       fosFmtE7(sum(CLASS7_ELAPSED ) /sum(SQL_STMTS_ISSUED))  "ela/stm",
       fosFmtE7(sum(SQL_STMTS_ISSUED )) "sqls",
       fosFmtE7(sum(CLASS7_CPU_TOTAL )) "c7cpu",
       fosFmtE7(sum(CLASS7_ELAPSED ))   "c7ela",
    -- 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 = 'YWAPCUR'
        and subsystem_id like 'DOF%'
        and       timestamp > '2012-05-01-00.00.00'
             -- timestamp between '2011-07-25-01.00.00'
             --               and '2011-07-28-23.00.00'

    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                                 PCK_COLLECTION_ID,PCK_id
                 , min(TIMESTAMP) desc
 -- order by sum(CLASS7_CPU_TOTAL)/sum(SQL_STMTS_ISSUED)
;;;
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
;;;