zOs/SQL/PDBSQLOP

set current path oa1x;
with p as
(
SELECT  CLASS7_ELAPSED / SQL_STMTS_ISSUED c7St,
        int(trunc(log10(CLASS7_ELAPSED / SQL_STMTS_ISSUED), 0))  log,
        p.*
    FROM RZ2DD.TACCT_PROGRAM p
    where SQL_STMTS_ISSUED > 0
)
SELECT  fmte8(min(c7St)) "elaFrom", fmte8(max(c7St)) "elaTo"
      , fmte8(sum(SQL_STMTS_ISSUED)) "totStmts"
      , fmte8(sum(CLASS7_ELAPSED))  "totEla"
      , fmte8(sum(CLASS7_ELAPSED)/24/3600/31)  "totEla/mon"
     --    fmte8(sum(CLASS7_ELAPSED) / sum(SQL_STMTS_ISSUED)) "avgTime",
     -- log
    from p
    WHERE TIMESTAMP >= '2009-08-01-00.00.00.000000'
      AND TIMESTAMP <  '2009-09-01-00.00.00.000000'
    group by log
    order by log
    with ur
    fetch first 100 rows only
;
        left(SUBSYSTEM_ID, 3),
        SUM(OCCURRENCES) OCCURRENCES,
        SUM(SQL_STMTS_ISSUED)  stmts,
        SUM(CLASS7_ELAPSED )   elaps,
        SUM(CLASS7_CPU_TOTAL ) cpu,
        SUM(CLASS7_ELAPSED )/SUM(SQL_STMTS_ISSUED) "elaps/stmt"     ,
        SUM(CLASS7_CPU_TOTAL )/SUM(SQL_STMTS_ISSUED) "cpu/stmt"
    FROM RZ2DD.TACCT_PROGRAM p
    WHERE TIMESTAMP >= '2009-09-09-00.00.00.000000'
      AND TIMESTAMP <= '2099-09-10-00.00.00.000000'
    AND PCK_ID = 'BU021@I'
    AND SUBSYSTEM_ID LIKE 'DOF%'
    GROUP BY trunc_TIMESTAMP(timestamp, 'day'), left(SUBSYSTEM_ID, 3)
    ORDER BY 1 DESC
    with ur
;
SELECT * from p
    with ur
    fetch first 100 rows only
;
x
SELECT      timestamp, count(*),
            sum(case when PCK_ID = 'BU021@I' then 1 else 0 end)
    FROM RZ2DD.TACCT_PROGRAM
    where subsystem_id like 'DOF%'
    group by timestamp
    order by 1 desc
    with ur
    ;
    x