zOs/TX/PER11INS

//A540769L 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='WSH'
//SYSPROC   DD DSN=A540769.WK.REXX,DISP=SHR
//ISPPLIB  DD  DSN=TSO.ISPF.ISPPLIB,DISP=SHR
//ISPMLIB  DD  DSN=TSO.ISPF.ISPMLIB,DISP=SHR
//*                TSO.RZ1.P0.INST.MSGS
//         DD  DSN=ISP.SISPMENU,DISP=SHR
//*                TSO.RZ1.P0.PROD.MSGS
//*                ORG.RZ1.B0106.KPCO4.MSGS
//*                TSO.RZ1.P0.USER.MSGS
//ISPSLIB  DD  DSN=TSO.ISPF.ISPSLIB,DISP=SHR
//ISPTLIB  DD  DSN=TSO.ISPF.ISPTLIB,DISP=SHR
//ISPPROF   DD DISP=(,PASS),UNIT=VIO,
//             SPACE=(CYL,(1,1,30)),RECFM=FB,LRECL=80
//*
//*            DSORG=PO
//ISPLOG    DD SYSOUT=*,RECFM=VA,LRECL=125
//ISPLIST   DD SYSOUT=*,RECFM=FBA,LRECL=121
//SYSPRINT  DD SYSOUT=*
//SYSUDUMP  DD SYSOUT=*
//SYSTSPRT  DD SYSOUT=*
//OUT       DD SYSOUT=*
//SYSTSIN   DD DUMMY
//WSH       DD *  DISP=SHR,DSN=A540769.TX.CASE(XC200DEL)
$#@
call sqlConnect dbaf
$=loTb=A540769.tper11
$=seTb=Oa1a01.VBE010A1
$=step= 4
$** call sqlStmt "delete from" $loTb "where verdTyp <> '?'"
$** call sqlCommit
$@do from=123 to 131 by $step $@[
    $=to=- $from + $step - 1
    say 'inserting' $from '-' $to
    $;
call sqlStmt $<=[
insert into $loTb
with g as
(
select  count(*) rows,
        sum(case when sammel1 < '1900-01-01-00:00:00'
                 then 0 else 1 end) sam1,
        sum(case when sammel2 < '1900-01-01-00:00:00'
                 then 0 else 1 end) sam2,
        sum(case when sammel1 < '1900-01-01-00:00:00'
                   or sammel2 < '1900-01-01-00:00:00'
                 then 0 else 1 end) sam12,
        PROCBASE, TECHBEZ, DAT_BUCH, DAT_VAL, verdTyp
        , sammel1
    from $seTb
    where procBase between $from and $to
    group by PROCBASE, TECHBEZ, DAT_BUCH, DAT_VAL, verdTyp
       , sammel1
)
, k as
( select count(*) gCnt,
         sum(rows) rows, min(rows) rowsMin, max(rows) rowsMax,
         sum(sam1) sam1, min(sam1) sam1Min, max(sam1) sam1Max,
         sum(sam2) sam2, min(sam2) sam2Min, max(sam2) sam2Max,
         sum(sam12) sam12, min(sam12) sam12Min, max(sam12) sam12Max,
         verdTyp
      from g
      group by PROCBASE, TECHBEZ, DAT_BUCH, DAT_VAL, verdTyp
)
, s  as
( select count(*) kCnt,
         sum(gCnt) gCnt, min(gCnt) gCntF, max(gCnt) gCntT,
     sum(rows) rows, min(rows) rowsF, max(rows) rowsT,
     sum(rowsMin) rowsMin, min(rowsMin) rowsMinF, max(rowsMin) rowsMinT,
     sum(rowsMax) rowsMax, Max(rowsMax) rowsMaxF, max(rowsMax) rowsMaxT,
     sum(sam1) sam1, min(sam1) sam1F, max(sam1) sam1T,
     sum(sam1Min) sam1Min, min(sam1Min) sam1MinF, max(sam1Min) sam1MinT,
     sum(sam1Max) sam1Max, Max(sam1Max) sam1MaxF, max(sam1Max) sam1MaxT,
     sum(sam2) sam2, min(sam2Min) sam2MinF, max(sam2Max) sam2MaxT,
     sum(sam12) sam12, min(sam12Min) sam12MinF, max(sam12Max) sam12MaxT,
     verdTyp
      from k
      group by verdTyp
             , floor(log(3*max(rowsMax, 0.1)))
             , floor(log(3*max(sam1Max, 0.1)))
             , floor(log(3*max(sam1Min, 0.1)))
)
select *
    from s
    $]
    $;
    call sqlCommit
$]
call sqlDisconnect
;