zOs/SQL/PDBPRGCT

set current path oa1p;
select
       fosFmtE7(sum(OCCURRENCES ))   "occ",
    -- max(SUBSYSTEM_ID   )),
       PLAN_NAME plan,
       date(min(TIMESTAMP)) "von", -- date(max(TIMESTAMP)),
 --    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"
    from rz2dd.tacct_program
    where pck_id = 'YCTS250'
        and subsystem_id like 'DOF%'
        and     timestamp > current timestamp - 20 day
            --  timestamp between '2010-01-10-00.00.00'
            --                and '2010-02-20-00.00.00'

    group by                plan_name, PCK_COLLECTION_ID,PCK_id,
             case when TIMESTAMP > '2011-06-10-24.00.00'
                  then 2 else 1 end
    having sum(CLASS7_CPU_TOTAL) >= 10
    order by                       plan_name, PCK_COLLECTION_ID,PCK_id
      , min(TIMESTAMP) desc -- , plan_name, PCK_COLLECTION_ID,PCK_id
;;;
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
;;;