zOs/JCL/WSHS

//A540769X JOB (CP00,KE50),'DB2 REO',                                   00010000
//         MSGCLASS=T,TIME=1440,                                        00020000
//         NOTIFY=&SYSUID,REGION=0M                                     00030000
//*MAIN CLASS=LOG                                                       00040000
//*
//S1       EXEC PGM=IKJEFT01,DYNAMNBR=200,TIME=99,                      00020001
//            PARM='%WSH3 s'
//SYSPROC   DD DSN=A540769.WK.REXX,DISP=SHR
//SYSPRINT  DD SYSOUT=*
//SYSUDUMP  DD SYSOUT=*
//SYSTSPRT  DD SYSOUT=*
//SYSTSIN   DD DUMMY
//OUT       DD DISP=SHR,DSN=A540769.TMP.TEXV(WSH3OUT)
//WSH       DD *
select current server from sysibm.sysDummy1;
--- plan pro Tag, Normierung variable mit div
set current path oa1p ;
with s as
(
  select
      trunc_timestamp(datetime, 'iw') tst
    , g.p2Commits + g.aborts + g.selects + g.inserts
      + g.updates + g.deletes + g.describes + g.prepares + g.opens
      + g.fetches + g.closes + g.setcurprec + g.dclglobaltt
      + g.sqlcrgtt div0
    , left(subsystem, 3) g
  --  , corrname
  --  , g.SYSTEMID
 -- , g.SUBSYSTEM
--  , g.PLANNAME
--  , g.CORRID
    , g.*
  from pbdd.tacct_general g
  where dateTime > trunc_timestamp('2014-06-01-00.00.00', 'iw')
)
, g as
( select case when div0 > 0 then div0 else null end div
      , s.*
    from s
)
select tst, g
    , fosFmtE7(sum(g.ELAPSETOD    )) "totElap"
    , fosFmtE7(sum(g.ELAPSETCB    )) "totCPU"
    , fosFmtE7(sum(g.EDB2TOD    )) "db2Elap"
    , fosFmtE7(sum(g.EDB2TCB    )) "db2CPU"
    , fosFmtE7(sum(g.div        )) "div"
-- begin prb insert gen esum(~)/sum(div)
-- tacct_general g, fsoFmtE7, all, expr sum(~)/sum(div)
    , fosFmtE7(sum(g.ELAPSETOD)/sum(div)) "totElap"
    , fosFmtE7(sum(g.ELAPSETCB)/sum(div)) "totCPU"
    , fosFmtE7(sum(g.EDB2TOD)/sum(div)) "db2Elap"
    , fosFmtE7(sum(g.EDB2TCB)/sum(div)) "db2CPU"
    , fosGeWait(sum(g.eWaitIO)/sum(div), sum(g.waitReadIO)/sum(div)
      , sum(g.waitWriteIO)/sum(div), sum(g.openClsElap)/sum(div)
      , sum(g.datasetElap)/sum(div), sum(g.eWaitLAL)/sum(div)
      , sum(g.sysLgRngElap)/sum(div), sum(g.logWrtElap)/sum(div)
      , sum(g.waitArcLog)/sum(div), sum(g.archReadWar)/sum(div)
      , sum(g.drainLkWDR)/sum(div), sum(g.claimRlWcl)/sum(div)
      , sum(g.gblLokElap)/sum(div), sum(g.wtelawtk)/sum(div)
      , sum(g.wtelawtm)/sum(div), sum(g.wtelawtn)/sum(div)
      , sum(g.wtelawto)/sum(div), sum(g.wtelawtq)/sum(div)
      , sum(g.gblMsgElap)/sum(div), sum(g.waitSyncEvent)/sum(div)
      , sum(g.otherSWElap)/sum(div), sum(g.spWaitElap)/sum(div)
      , sum(g.funcWait)/sum(div)
      , sum(g.lobWaitElap)/sum(div)) "wait    % 1.   % 2.   % 3."
    , fosFmtE7(sum((g.p2Commits + g.aborts + g.selects + g.inserts
      + g.updates + g.deletes + g.describes + g.prepares + g.opens
      + g.fetches + g.closes + g.setcurprec + g.dclglobaltt
      + g.sqlcrgtt))/sum(div)) "sqls"
    , fosFmtE7(sum(g.P2COMMITS)/sum(div)) "commit"
    , fosFmtE7(sum(g.ABORTS)/sum(div)) "abort"
    , fosFmtE7(sum(g.SELECTS)/sum(div)) SELECTS
    , fosFmtE7(sum(g.INSERTS)/sum(div)) INSERTS
    , fosFmtE7(sum(g.UPDATES)/sum(div)) UPDATES
    , fosFmtE7(sum(g.DELETES)/sum(div)) DELETES
    , fosFmtE7(sum(g.DESCRIBES)/sum(div)) DESCRIBES
    , fosFmtE7(sum(g.PREPARES)/sum(div)) PREPARES
    , fosFmtE7(sum(g.OPENS)/sum(div)) OPENS
    , fosFmtE7(sum(g.FETCHES)/sum(div)) FETCHES
    , fosFmtE7(sum(g.CLOSES)/sum(div)) CLOSES
    , fosFmtE7(sum(g.LOGWRTELAP)/sum(div)) "logEla"
    , fosFmtE7(sum(g.LOGRECORDS)/sum(div)) "logRecs"
    , fosFmtE7(sum(g.LOGBYTES)/sum(div)) "logByte"
    , fosFmtE7(sum(g.BPGETPAGE)/sum(div)) BPGETPAGE
    , fosFmtE7(sum(g.BPPGUPDAT)/sum(div)) BPPGUPDAT
    , fosFmtE7(sum(g.BPSYNCRD)/sum(div)) BPSYNCRD
    , fosFmtE7(sum(g.BPPREFET)/sum(div)) BPPREFET
    , fosFmtE7(sum(g.BPSYNCWR)/sum(div)) BPSYNCWR
    , fosFmtE7(sum(g.BPLISTPREF)/sum(div)) BPLISTPREF
    , fosFmtE7(sum(g.BPDPF)/sum(div)) BPDPF
    , fosFmtE7(sum(g.BPNGT)/sum(div)) BPNGT
    , fosFmtE7(sum(g.BPSIO)/sum(div)) BPSIO
    , REAL(sum((g.eWaitIO + g.waitReadIO + g.waitWriteIO
      + g.openClsElap + g.datasetElap + g.eWaitLAL + g.sysLgRngElap
      + g.logWrtElap + g.waitArcLog + g.archReadWar + g.drainLkWDR
      + g.claimRlWcl + g.gblLokElap + g.wtelawtk + g.wtelawtm
      + g.wtelawtn + g.wtelawto + g.wtelawtq + g.gblMsgElap
      + g.waitSyncEvent + g.otherSWElap + g.spWaitElap + g.funcWait
      + g.lobWaitElap))/sum(div)) "wait"
    , fosFmtE7(sum(g.OCCURRENCES)/sum(div)) OCCURRENCES
    , fosFmtE7(sum(g.LUWIDCOMIT)/sum(div)) LUWIDCOMIT
    , fosFmtE7(sum(g.TRANSCNT)/sum(div)) TRANSCNT
    , fosFmtE7(sum(g.CLASS2CNT)/sum(div)) CLASS2CNT
    , fosFmtE7(sum(g.CLASS3CNT)/sum(div)) CLASS3CNT
    , fosFmtE7(sum(g.IFCIDSEQ#)/sum(div)) IFCIDSEQ#
    , fosFmtE7(sum(g.ELAPSESRB)/sum(div)) ELAPSESRB
    , fosFmtE7(sum(g.EDB2SRB)/sum(div)) EDB2SRB
    , fosFmtE7(sum(g.EWAITIO)/sum(div)) "synIOWait"
    , fosFmtE7(sum(g.EWAITLAL)/sum(div)) "locLoLaWai"
    , fosFmtE7(sum(g.ENTEXEVNT)/sum(div)) ENTEXEVNT
    , fosFmtE7(sum(g.WAITEVNT)/sum(div)) WAITEVNT
    , fosFmtE7(sum(g.WAITREADIO)/sum(div)) "othReaWait"
    , fosFmtE7(sum(g.WAITWRITEIO)/sum(div)) "othWriWait"
    , fosFmtE7(sum(g.WAITSYNCEVENT)/sum(div)) "uniSwiWait"
    , fosFmtE7(sum(g.WAITARCLOG)/sum(div)) "arcLogWait"
    , fosFmtE7(sum(g.WEVLOCK)/sum(div)) WEVLOCK
    , fosFmtE7(sum(g.WEVREAD)/sum(div)) WEVREAD
    , fosFmtE7(sum(g.WEVWRITE)/sum(div)) WEVWRITE
    , fosFmtE7(sum(g.WEVSYNCH)/sum(div)) WEVSYNCH
    , fosFmtE7(sum(g.CLASS1CPU_ZIIP)/sum(div)) CLASS1CPU_ZIIP
    , fosFmtE7(sum(g.CLASS2CPU_ZIIP)/sum(div)) CLASS2CPU_ZIIP
    , fosFmtE7(sum(g.TRIGGERCPU_ZIIP)/sum(div)) TRIGGERCPU_ZIIP
    , fosFmtE7(sum(g.CPUZIIPELIGIBLE)/sum(div)) CPUZIIPELIGIBLE
    , fosFmtE7(sum(g.ARCLOG)/sum(div)) ARCLOG
    , fosFmtE7(sum(g.DRAINLKRND)/sum(div)) DRAINLKRND
    , fosFmtE7(sum(g.DRAINLKWDR)/sum(div)) "drainWait"
    , fosFmtE7(sum(g.CLAIMRLWCL)/sum(div)) "claimWait"
    , fosFmtE7(sum(g.CLAIMRLRNC)/sum(div)) CLAIMRLRNC
    , fosFmtE7(sum(g.ARCHREADWAR)/sum(div)) "arcReaWait"
    , fosFmtE7(sum(g.ARCHREADNAR)/sum(div)) ARCHREADNAR
    , fosFmtE7(sum(g.OPENCLSELAP)/sum(div)) "opeCloWait"
    , fosFmtE7(sum(g.SYSLGRNGELAP)/sum(div)) "sysLgRaWai"
    , fosFmtE7(sum(g.DATASETELAP)/sum(div)) "datSetWait"
    , fosFmtE7(sum(g.OTHERSWELAP)/sum(div)) "othSwiEla"
    , fosFmtE7(sum(g.OPENCLSEVNT)/sum(div)) OPENCLSEVNT
    , fosFmtE7(sum(g.SYSLGRNGEVNT)/sum(div)) SYSLGRNGEVNT
    , fosFmtE7(sum(g.DATASETEVNT)/sum(div)) DATASETEVNT
    , fosFmtE7(sum(g.OTHERSWEVNT)/sum(div)) OTHERSWEVNT
    , fosFmtE7(sum(g.LATCHCNTWTP)/sum(div)) LATCHCNTWTP
    , fosFmtE7(sum(g.LATCHCNTRNH)/sum(div)) LATCHCNTRNH
    , fosFmtE7(sum(g.GBLMSGELAP)/sum(div)) "gblMsgWait"
    , fosFmtE7(sum(g.GBLMSGEVNT)/sum(div)) GBLMSGEVNT
    , fosFmtE7(sum(g.GBLLOKELAP)/sum(div)) "gblConWait"
    , fosFmtE7(sum(g.GBLLOKEVNT)/sum(div)) GBLLOKEVNT
    , fosFmtE7(sum(g.SPTCB)/sum(div)) "stoProCpu"
    , fosFmtE7(sum(g.SPTCBINDB2)/sum(div)) "stoProDb2"
    , fosFmtE7(sum(g.SPEVNT)/sum(div)) SPEVNT
    , fosFmtE7(sum(g.SPWAITELAP)/sum(div)) "stoProWait"
    , fosFmtE7(sum(g.SPWAITCNT)/sum(div)) SPWAITCNT
    , fosFmtE7(sum(g.PARATASKS)/sum(div)) PARATASKS
    , fosFmtE7(sum(g.PARALLTASKS)/sum(div)) PARALLTASKS
    , fosFmtE7(sum(g.CPUSUCONV)/sum(div)) CPUSUCONV
    , fosFmtE7(sum(g.LOGWRTEVNT)/sum(div)) LOGWRTEVNT
    , fosFmtE7(sum(g.FUNCTCB)/sum(div)) "funcCpu"
    , fosFmtE7(sum(g.FUNCSQLTCB)/sum(div)) "funcD2Cpu"
    , fosFmtE7(sum(g.FUNCSQLEVNT)/sum(div)) FUNCSQLEVNT
    , fosFmtE7(sum(g.LOBWAITCNT)/sum(div)) LOBWAITCNT
    , fosFmtE7(sum(g.FUNCWAIT)/sum(div)) "funcWait"
    , fosFmtE7(sum(g.FUNCELAP)/sum(div)) "funcEla"
    , fosFmtE7(sum(g.FUNCSQLELAP)/sum(div)) "funcD2Ela"
    , fosFmtE7(sum(g.TRIGGERTCB)/sum(div)) "triD2Cpu"
    , fosFmtE7(sum(g.TRIGGERELAP)/sum(div)) "triD2Ela"
    , fosFmtE7(sum(g.PREENCTCB)/sum(div)) "???"
    , fosFmtE7(sum(g.PREENCSQLTCB)/sum(div)) "???"
    , fosFmtE7(sum(g.SPROCELAP)/sum(div)) "stoProToEl"
    , fosFmtE7(sum(g.SPROCSQLELAP)/sum(div)) "stoProD2El"
    , fosFmtE7(sum(g.ENCTRIGGERTCB)/sum(div)) "triNesToCp"
    , fosFmtE7(sum(g.ENCTRIGGERELAP)/sum(div)) "triNesToEl"
    , fosFmtE7(sum(g.LOBWAITELAP)/sum(div)) LOBWAITELAP
    , fosFmtE7(sum(g.SPNFCPUZIIP)/sum(div)) "???"
    , fosFmtE7(sum(g.SPNFCPU)/sum(div)) "???"
    , fosFmtE7(sum(g.SPNFELAP)/sum(div)) "???"
    , fosFmtE7(sum(g.UDFNFCPUZIIP)/sum(div)) UDFNFCPUZIIP
    , fosFmtE7(sum(g.UDFNFCPU)/sum(div)) UDFNFCPU
    , fosFmtE7(sum(g.UDFNFELAP)/sum(div)) UDFNFELAP
    , fosFmtE7(sum(g.SVPOINTREQ)/sum(div)) SVPOINTREQ
    , fosFmtE7(sum(g.SVPOINTREL)/sum(div)) SVPOINTREL
    , fosFmtE7(sum(g.SVPOROLLBK)/sum(div)) SVPOROLLBK
    , fosFmtE7(sum(g.WTELAWTK)/sum(div)) "gblChiWait"
    , fosFmtE7(sum(g.WTELAWTM)/sum(div)) "gblOtLWait"
    , fosFmtE7(sum(g.WTELAWTN)/sum(div)) "gblPrPWait"
    , fosFmtE7(sum(g.WTELAWTO)/sum(div)) "gblPgPWait"
    , fosFmtE7(sum(g.WTELAWTQ)/sum(div)) "gblOtPWait"
    , fosFmtE7(sum(g.WTEVARNK)/sum(div)) WTEVARNK
    , fosFmtE7(sum(g.WTEVARNM)/sum(div)) WTEVARNM
    , fosFmtE7(sum(g.WTEVARNN)/sum(div)) WTEVARNN
    , fosFmtE7(sum(g.WTEVARNO)/sum(div)) WTEVARNO
    , fosFmtE7(sum(g.WTEVARNQ)/sum(div)) WTEVARNQ
    , fosFmtE7(sum(g.WTELAWFC)/sum(div)) "???"
    , fosFmtE7(sum(g.WTEVFCCT)/sum(div)) WTEVFCCT
    , fosFmtE7(sum(g.WTELIXLT)/sum(div)) WTELIXLT
    , fosFmtE7(sum(g.WTEVIXLE)/sum(div)) WTEVIXLE
    , fosFmtE7(sum(g.SETCURPREC)/sum(div)) SETCURPREC
    , fosFmtE7(sum(g.DCLGLOBALTT)/sum(div)) DCLGLOBALTT
    , fosFmtE7(sum(g.PARAGLOBALTT)/sum(div)) PARAGLOBALTT
    , fosFmtE7(sum(g.PARAMAXDEG)/sum(div)) PARAMAXDEG
    , fosFmtE7(sum(g.PARAREDGRP)/sum(div)) PARAREDGRP
    , fosFmtE7(sum(g.SQLCALLAB)/sum(div)) SQLCALLAB
    , fosFmtE7(sum(g.SQLCALLTO)/sum(div)) SQLCALLTO
    , fosFmtE7(sum(g.SQLCRGTT)/sum(div)) SQLCRGTT
    , fosFmtE7(sum(g.REOPTIMIZE)/sum(div)) REOPTIMIZE
    , fosFmtE7(sum(g.DIRECTROWIX)/sum(div)) DIRECTROWIX
    , fosFmtE7(sum(g.DIRECTROWTS)/sum(div)) DIRECTROWTS
    , fosFmtE7(sum(g.FUNC)/sum(div)) FUNC
    , fosFmtE7(sum(g.FUNCAB)/sum(div)) FUNCAB
    , fosFmtE7(sum(g.FUNCTO)/sum(div)) FUNCTO
    , fosFmtE7(sum(g.FUNCRJ)/sum(div)) FUNCRJ
    , fosFmtE7(sum(g.DEADLOCKS)/sum(div)) DEADLOCKS
    , fosFmtE7(sum(g.SUSPENDS)/sum(div)) SUSPENDS
    , fosFmtE7(sum(g.TIMEOUTS)/sum(div)) TIMEOUTS
    , fosFmtE7(sum(g.LOCKESHR)/sum(div)) LOCKESHR
    , fosFmtE7(sum(g.LOCKEXCL)/sum(div)) LOCKEXCL
    , fosFmtE7(sum(g.MAXPGLOCKS)/sum(div)) MAXPGLOCKS
    , fosFmtE7(sum(g.SUSPLATCH)/sum(div)) SUSPLATCH
    , fosFmtE7(sum(g.SUSPOTHER)/sum(div)) SUSPOTHER
    , fosFmtE7(sum(g.LOCKREQS)/sum(div)) LOCKREQS
    , fosFmtE7(sum(g.CLAIMREQ)/sum(div)) CLAIMREQ
    , fosFmtE7(sum(g.CLAIMREQUN)/sum(div)) CLAIMREQUN
    , fosFmtE7(sum(g.DRAINREQ)/sum(div)) DRAINREQ
    , fosFmtE7(sum(g.DRAINREQUN)/sum(div)) DRAINREQUN
    , fosFmtE7(sum(g.GBPREADINVBD)/sum(div)) GBPREADINVBD
    , fosFmtE7(sum(g.GBPREADINVBR)/sum(div)) GBPREADINVBR
    , fosFmtE7(sum(g.GBPREADNOPGD)/sum(div)) GBPREADNOPGD
    , fosFmtE7(sum(g.GBPREADNOPGR)/sum(div)) GBPREADNOPGR
    , fosFmtE7(sum(g.GBPREADNOPGN)/sum(div)) GBPREADNOPGN
    , fosFmtE7(sum(g.GBPWRITCHG)/sum(div)) GBPWRITCHG
    , fosFmtE7(sum(g.GBPWRITCLEAN)/sum(div)) GBPWRITCLEAN
    , fosFmtE7(sum(g.GBPUNREGPG)/sum(div)) GBPUNREGPG
    , fosFmtE7(sum(g.GBPEXPLICITXI)/sum(div)) GBPEXPLICITXI
    , fosFmtE7(sum(g.GBPWRITCHK2)/sum(div)) GBPWRITCHK2
    , fosFmtE7(sum(g.GBPASYNPRIM)/sum(div)) GBPASYNPRIM
    , fosFmtE7(sum(g.GBPASYNSEC)/sum(div)) GBPASYNSEC
    , fosFmtE7(sum(g.GBPDEPGETPG)/sum(div)) GBPDEPGETPG
    , fosFmtE7(sum(g.GBPPLKSPMAP)/sum(div)) GBPPLKSPMAP
    , fosFmtE7(sum(g.GBPPLKDATA)/sum(div)) GBPPLKDATA
    , fosFmtE7(sum(g.GBPPLKIDX)/sum(div)) GBPPLKIDX
    , fosFmtE7(sum(g.GBPPLKUNLK)/sum(div)) GBPPLKUNLK
    , fosFmtE7(sum(g.GBPPSUSSPMAP)/sum(div)) GBPPSUSSPMAP
    , fosFmtE7(sum(g.GBPPSUSDATA)/sum(div)) GBPPSUSDATA
    , fosFmtE7(sum(g.GBPPSUSIDX)/sum(div)) GBPPSUSIDX
    , fosFmtE7(sum(g.GBPWARMULTI)/sum(div)) GBPWARMULTI
    , fosFmtE7(sum(g.GBPWAR)/sum(div)) GBPWAR
    , fosFmtE7(sum(g.GLPLOCKLK)/sum(div)) GLPLOCKLK
    , fosFmtE7(sum(g.GLPLOCKCHG)/sum(div)) GLPLOCKCHG
    , fosFmtE7(sum(g.GLPLOCKUNLK)/sum(div)) GLPLOCKUNLK
    , fosFmtE7(sum(g.GLXESSYNCLK)/sum(div)) GLXESSYNCLK
    , fosFmtE7(sum(g.GLXESSYNCCHG)/sum(div)) GLXESSYNCCHG
    , fosFmtE7(sum(g.GLXESSYNCUNLK)/sum(div)) GLXESSYNCUNLK
    , fosFmtE7(sum(g.GLSUSPIRLM)/sum(div)) GLSUSPIRLM
    , fosFmtE7(sum(g.GLSUSPXES)/sum(div)) GLSUSPXES
    , fosFmtE7(sum(g.GLSUSPFALSE)/sum(div)) GLSUSPFALSE
    , fosFmtE7(sum(g.GLINCOMPAT)/sum(div)) GLINCOMPAT
    , fosFmtE7(sum(g.GLNOTFYSENT)/sum(div)) GLNOTFYSENT
    , fosFmtE7(sum(g.GLFALSECONT)/sum(div)) GLFALSECONT
    , fosFmtE7(sum(g.RLFCPULIMITU)/sum(div)) RLFCPULIMITU
    , fosFmtE7(sum(g.RLFCPUUSEDU)/sum(div)) RLFCPUUSEDU
    , fosFmtE7(sum(g.UNLOCKREQS)/sum(div)) UNLOCKREQS
    , fosFmtE7(sum(g.QUERYREQS)/sum(div)) QUERYREQS
    , fosFmtE7(sum(g.CHNGREQS)/sum(div)) CHNGREQS
    , fosFmtE7(sum(g.IFIELAPSED)/sum(div)) IFIELAPSED
    , fosFmtE7(sum(g.IFITCBCPU)/sum(div)) IFITCBCPU
    , fosFmtE7(sum(g.IFIELAPDTC)/sum(div)) IFIELAPDTC
    , fosFmtE7(sum(g.IFIELAPEXT)/sum(div)) IFIELAPEXT
    , fosFmtE7(sum(g.PROGRAMS)/sum(div)) PROGRAMS
-- end   prb insert gen esum(~)/sum(div)
  from g
  group by g, tst
  order by g, tst desc