zOs/TX/PER11UNL

$>.fEdit() $<=[
$=loTb=a540769.tper11
$=seTb=oa1p01.vbe010a1
$=step= 200
$=top = 130
$*+ call sqlstmt "delete from" $loTb "where verdtyp <> '?'"
$*+ call sqlcommit
$=from= -$step
$@do tx=0 to $top $@=[
    $=tx=-right(tx, 3, 0)
    $=j1=- tx // 10
    $=from=- $from+$step
    $=to=- $from + $step - 1
//A540769$j1 JOB (CP00,KE50),'DB2 REO',                                 00010000
//         MSGCLASS=T,TIME=1440,                                        00020000
//         NOTIFY=&SYSUID,REGION=0M                                     00030000
//*MAIN CLASS=LOG                                                       00040000
//DEL      EXEC PGM=IEFBR14                                             00020001
//SYSR$tx DD  DISP=(MOD,DELETE),DSN=A540769.PER11.UNL$tx
//S1       EXEC PGM=IKJEFT01,DYNAMNBR=200,TIME=99                       00020001
//SYSTSIN  DD *
    DSN SYSTEM(DBOF)
   RUN PROGRAM(DSNTIAUL) PARMS('SQL')
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTERM  DD SYSOUT=*
//SYSPUNCH DD  SYSOUT=*,RECFM=FB,LRECL=80
//SYSREC00  DD  DISP=(NEW,CATLG),DSN=*.DEL.SYSR$tx,
//             DCB=(LRECL=48,RECFM=FB),
//             SPACE=(CYL,(2,1000)),MGMTCLAS=COM#A069
//SYSIN      DD *
with s1 as
( select count(*) s1Rows,
        PROCBASE, TECHBEZ, DAT_BUCH, DAT_VAL, VERDTYP, sammel1
    from $seTb a
    where procBase between $from and $to
    group by PROCBASE, TECHBEZ, DAT_BUCH, DAT_VAL, VERDTYP, sammel1
)
, vr as
( select count(*) vrRows,
        PROCBASE, TECHBEZ, DAT_BUCH, DAT_VAL, VERDTYP
    from $seTb a
    where procBase between $from and $to
    group by PROCBASE, TECHBEZ, DAT_BUCH, DAT_VAL, VERDTYP
)
, sv as
( select s1Rows, vrRows,
        s1.PROCBASE, s1.TECHBEZ, s1.DAT_BUCH,
        s1.DAT_VAL, s1.VERDTYP, s1.sammel1
    from s1, vr
    where   s1.procBase = vr.procBase
        and s1.techBez  = vr.techBez
        and s1.dat_Buch = vr.dat_buch
        and s1.dat_Val  = vr.dat_val
        and s1.verdtyp  = vr.verdtyp
)
, va as
( select sum(s1Rows) s1Rows, sum(vrRows) vrRows,
        PROCBASE, TECHBEZ, DAT_BUCH, DAT_VAL, sammel1
    from sv
    group by PROCBASE, TECHBEZ, DAT_BUCH, DAT_VAL, sammel1
)
, bu as
( select count(*) buRows,
        sum(case when sammel1 < '1900-01-01-00:00:00'
                 then 1 else 0 end) buS0Rows,
        sum(case when sammel1 > '1900-01-01-00:00:00'
                 then 1 else 0 end) buS1Rows,
        sum(case when sammel2 > '1900-01-01-00:00:00'
                 then 1 else 0 end) buS2Rows,
        PROCBASE, TECHBEZ, DAT_BUCH
    from $seTb a
    where procBase between $from and $to
    group by PROCBASE, TECHBEZ, DAT_BUCH
)
, s as
( select s1Rows, vrRows, buRows, buS0Rows, buS1Rows, buS2Rows,
        va.PROCBASE, va.TECHBEZ, va.DAT_BUCH, va.DAT_VAL, va.sammel1
    from va, bu
    where   va.procBase = bu.procBase
        and va.techBez  = bu.techBez
        and va.dat_Buch = bu.dat_buch
)
select count(*) cnt,
       sum(s1Rows) s1Rows, min(s1Rows) s1RowF, max(s1Rows) s1RowT,
       sum(vrRows) vrRows, min(vrRows) vrRowF, max(vrRows) vrRowT,
       sum(buRows) buRows, min(buRows) buRowF, max(buRows) buRowT,
       varChar('u0322*$tx', 20) type
    from s
    group by floor(log(3*max(s1Rows, 0.1)))
           , floor(log(3*max(vrRows, 0.1)))
           , floor(log(3*max(buRows, 0.1)))
;
$]
$]