zOs/SQL/PMPLAN

--  Abfrage aus Perfomance Datenbank    W.Keller 09.2007
--      Total pro Plan und GesamtTotal
--
--  Achtung: die Perfomance DB ist jetzt im DBOC für alle SubSysteme
--           TabellenName, Subsystem Selektion etc.
--               müssen angepasst werden
--
select 'before', current timestamp from sysibm.sysdummy1
;
with c1 as
  ( select trunc_Timestamp(current_timestamp - 1 month, 'MON') von,
           trunc_Timestamp(current_timestamp          , 'MON') bis
    from sysibm.sysdummy1
  ) ,
c  as
  ( select trunc_Timestamp(current_timestamp - 1 days , 'DD') von,
           trunc_Timestamp(current_timestamp          , 'DD') bis
    from sysibm.sysdummy1
  ),
de1 as
  ( select SUBSYSTEM_ID    subsys,
           CONNECT_TYPE    conn,
           PLAN_NAME       plan,
           sum(occurrences * real(CLASS2_CPU))     cpu,
           sum(occurrences * real(CLASS2_ELAPSED)) ela,
           sum(real(OCCURRENCES)) occ,
           sum(real(COMMITS))    comm,
           sum(real(occurrences * READS))   reads,
           sum(real(occurrences * UPDATES)) upd
      from DB2PMPDB.TACCT_EXTRACT t, c
      where interval_time >= von and interval_time < bis
      group by subsystem_id, connect_type, plan_name
  ),
det as
  ( select
        case
            when lower(plan) =       'asnapply'       then 'aAsnApply'
            when plan <> upper(plan)                  then 'aSer'
            when left(plan, 3) = 'ACT'                then 'aBMC'
            when left(plan, 3) = 'ADM'                then 'aBMC'
            when left(plan, 3) = 'AEX'                then 'aBMC'
            when left(plan, 3) = 'ASU'                then 'aBMC'
            when left(plan, 3) = 'CM7'                then 'aBMC'
            when left(plan, 3) = 'ADB'                then 'aDBA'
            when left(plan, 3) = 'AFR'                then '?AFR'
            when left(plan, 3) = 'ALA'                then '?ALA'
            when left(plan, 3) = 'CBR'                then 'aCBri'
            when left(plan, 3) = 'CNT'                then '?CNT'
            when left(plan, 3) = 'CSQ'                then 'aMQ'
            when left(plan, 3) = 'DB2'                then 'aDBA'
            when plan               = 'DISTSERV'      then 'aDistServ'
            when plan               = 'DSNUTIL'       then 'aUtil'
            when left(plan, 3) = 'DSN'                then 'aDSN'
            when left(plan, 2) = 'DS'                 then '?DS'
            when left(plan, 3) = 'FPE'                then 'aDBA'
            when left(plan, 3) = 'FRN'                then '?FRN'
            when plan               = 'FILEAID'       then 'aFileAid'
            when left(plan, 3) = 'HPS'                then 'aHPS'
            when left(plan, 3) = 'ISL'                then '?ISL'
            when left(plan, 3) = 'JSK'                then '?JSK'
            when left(plan, 3) = 'MB1'                then 'aSer'
            when left(plan, 3) = 'MB2'                then 'aSer'
            when left(plan, 3) = 'MQ1'                then 'aMQ'
            when left(plan, 3) = 'MSA'                then 'aSer'
            when left(plan, 3) = 'M24'                then '?M24'
            when left(plan, 3) = 'NTA'                then 'aCics'
            when left(plan, 3) = 'QMF'                then 'aQMF'
            when left(plan, 3) = 'SAS'                then 'aSAS'
            when left(plan, 3) = 'SMU'                then '?SMU'
            when left(plan, 3) = 'SPD'                then '?SPD'
            when left(plan, 3) = 'STR'                then '?STR'
            when left(plan, 2) = 'S6'                 then '?S6'
            when left(plan, 3) = 'XIN'                then 'aXX'
            when conn = 'UTILITY'                     then 'aUtil'
            when plan         = ''                    then '?Leer'
            else                              'g' || left(plan, 2)
        end gp,
        de1.*
      from de1
  ) ,
typ as
  ( select left(gp, 1) gp,
           sum(cpu) cpu, sum(ela) ela, sum(occ) occ,
               sum(comm) comm, sum(reads) reads, sum(upd) upd
      from det
      group by left(gp, 1)
  ) ,
typGP as
  ( select *
      from typ
      where gp = 'g'
  ) ,
uni as
  ( select * from det where left(gp, 1) <> 'g'
    union select '*' || strip(gp) || '*' gp,
             '*' subsys, '*' conn, '*' plan,
             cpu, ela, occ, comm, reads, upd
        from typ
  )
select decimal(round(uni.cpu * 100 / typGP.cpu, 2), 5, 2)  cpuPer, uni.*
    from uni, typGP
    order by left(gp,1), cpuPer desc
    with ur
;
select 'after', current timestamp from sysibm.sysdummy1
;
x
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
;
select gp, left(subsystem_id, 3), plan_name,  conn,
        sum(cpu), count(*), sum(occurrences)
    from e
    group by gp, left(subsystem_id, 3), plan_name, conn
    order by 1, 2, 3, 4
    with ur
;