zOs/SQL/PMGP

--  Abfrage aus Perfomance Datenbank                    W.Keller 09.2007
--      Total pro GebietsPointer und GesamtTotal
--
--  Achtung: die Perfomance DB ist jetzt im DBOC für alle Systeme
--           TabellenName, Subsystem Selektion etc.
--               müssen angepasst werden
--
select current timestamp "before" 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 - 12 days , 'DD') von,
           trunc_Timestamp(current_timestamp - 11 days , 'DD') bis
    from sysibm.sysdummy1
  ),
de1 as
  ( select SUBSYSTEM_ID    subsys,
           CONNECT_TYPE    conn,
           PLAN_NAME       plan,
           sum(occurrences * real(CLASS2_ELAPSED)) ela,
           sum(occurrences * real(CLASS2_CPU))     cpu,
           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
  ) ,
geb as
  ( select gp, sum(cpu) cpu, sum(ela) ela, sum(occ) occ,
               sum(comm) comm, sum(reads) reads, sum(upd) upd
      from det
      group by gp
  ) ,
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 '*' || strip(gp) || '*' gp, cpu, ela, occ, comm, reads, upd
        from typ
    union select *
        from geb
  )
select  u.gp "Gebiets Pointer",
        decimal(round(u.cpu * 100 / g.cpu, 2), 5, 2)  "cpu %",
                                u.cpu "cpu secs", u.ela "elapsed secs",
        u.occ "occurrences", u.comm "commits",
        u.reads "reads", u.upd "updates"
    from uni u, typGP g
    order by left(u.gp,1), 2 desc
    with ur
;
select current timestamp "after" 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
;