zOs/SQL/PATST1

sql=
select 'before', current timestamp from sysibm.sysdummy1
;
sql=
with c1 as
  ( select trunc_Timestamp(current_timestamp - 1 month, 'MON') von,
           trunc_Timestamp(current_timestamp          , 'MON') bis
    from sysibm.sysdummy1
  ) ,
 c  as
 (
    select c1.*, days(bis) - days(von) noDays,
        (days(bis)-days(von))*24*3600 secs
    from c1
  ) ,
e as
  ( select t.*,
        occurrences  * real(class2_cpu) / secs cpu,
        case
            when lower(plan_name) =  'asnapply'       then 'gAsnApply'
            when plan_name <> upper(plan_name)        then 'aSer'
            when left(plan_name, 3) = 'ACT'           then 'aBMC'
            when left(plan_name, 3) = 'ADM'           then 'aBMC'
            when left(plan_name, 3) = 'AEX'           then 'aBMC'
            when left(plan_name, 3) = 'ASU'           then 'aBMC'
            when left(plan_name, 3) = 'CM7'           then 'aBMC'
            when left(plan_name, 3) = 'ADB'           then 'aDBA'
            when left(plan_name, 3) = 'AFR'           then '|AFR'
            when left(plan_name, 3) = 'ALA'           then '|ALA'
            when left(plan_name, 3) = 'CBR'           then 'aCBri'
            when left(plan_name, 3) = 'CNT'           then '|CNT'
            when left(plan_name, 3) = 'CSQ'           then 'aMQ'
            when left(plan_name, 3) = 'DB2'           then 'aDBA'
            when plan_name          = 'DISTSERV'      then 'gDistServ'
            when plan_name          = 'DSNUTIL'       then 'aUtil'
            when left(plan_name, 3) = 'DSN'           then 'aDSN'
            when left(plan_name, 2) = 'DS'            then '|DS'
            when left(plan_name, 3) = 'FPE'           then 'aDBA'
            when left(plan_name, 3) = 'FRN'           then '|FRN'
            when plan_name          = 'FILEAID'       then 'aFileAid'
            when left(plan_name, 3) = 'HPS'           then 'aHPS'
            when left(plan_name, 3) = 'ISL'           then '|ISL'
            when left(plan_name, 3) = 'JSK'           then '|JSK'
            when left(plan_name, 3) = 'MB1'           then 'aSer'
            when left(plan_name, 3) = 'MB2'           then 'aSer'
            when left(plan_name, 3) = 'MQ1'           then 'aMQ'
            when left(plan_name, 3) = 'MSA'           then 'aSer'
            when left(plan_name, 3) = 'M24'           then '|M24'
            when left(plan_name, 3) = 'NTA'           then 'aCics'
            when left(plan_name, 3) = 'QMF'           then 'aQMF'
            when left(plan_name, 3) = 'SAS'           then 'aSAS'
            when left(plan_name, 3) = 'SMU'           then '|SMU'
            when left(plan_name, 3) = 'SPD'           then '|SPD'
            when left(plan_name, 3) = 'STR'           then '|STR'
            when left(plan_name, 2) = 'S6'            then '|S6'
            when left(plan_name, 3) = 'XIN'           then 'aXX'
            when connect_type = 'UTILITY'             then 'aUtil'
            when plan_name    = ''                    then '|Leer'
            else                              'g' || left(plan_name, 2)
        end gp
      from DB2PMPDB.TACCT_EXTRACT t, c
      where interval_time >= von and interval_time < bis
  )
select  gp    , left(subsystem_id, 3),
        sum(cpu), count(*), sum(occurrences)
    from e
    group by gp   , left(subsystem_id, 3)
    order by 3 desc
    with ur
;
sql=
select 'before', current timestamp from sysibm.sysdummy1
;
x
select gp, left(subsystem_id, 3), plan_name,  connect_type,
        sum(cpu), count(*), sum(occurrences)
    from e
    group by gp, left(subsystem_id, 3), plan_name, connect_type
    order by 1, 2, 3, 4
    with ur
;