zOs/JCL/WI801

//A540769W JOB (CP00,KE50),'DB2 REO',                                   00010000
//         MSGCLASS=T,TIME=1440,                                        00020000
//         NOTIFY=&SYSUID,REGION=0M,SCHENV=DB2                          00030003
//*MAIN CLASS=LOG                                                       00040003
//DEL      EXEC PGM=IEFBR14
//SQL        DD DISP=(MOD,DELETE,DELETE),DSN=DSN.JOBRUN.A540769W.SQL
//SQL      EXEC PGM=IKJEFT01,DYNAMNBR=200,TIME=99
//SYSTSIN    DD *
    DSN SYSTEM(DBAF)
   RUN PROGRAM(DSNTIAUL) PARMS('SQL')
//SYSTSPRT   DD SYSOUT=*
//SYSPRINT   DD SYSOUT=*
//SYSUDUMP   DD SYSOUT=*
//SYSTERM    DD SYSOUT=*
//SYSPUNCH   DD DUMMY
//SYSREC00   DD DISP=(NEW,CATLG),DSN=*.DEL.SQL,
//             DCB=(LRECL=80,RECFM=FB), KEIN RECLEN ANGEBEN,
//             SPACE=(CYL,(1,10)),MGMTCLAS=COM#A069
//SYSIN      DD *
with p    (tb, pa) as
(
  select 'twi801a100'||wi812_split, wi812_partition

    from oA1A.twi812a1
    where wi812_lastuse
           < trunc_timestamp(current timestamp - 2 years, 'ddd')
)
, pTst (tb, pa) as
(
  select  varchar( 'TWI801A1002', 30), 2 from sysibm.sysDummy1
  union all select 'TWI801A1002', 7 from sysibm.sysDummy1
  union all select 'TWI801A1004', 4 from sysibm.sysDummy1
)
, t (tb, pas) as
(
  select tb, count(*)
    from p
    group by tb
)
, a1 (tbs, pas) as
(
  select count(*), value(sum(pas), 0)
    from t
)
, a0 (tbs, pas) as
(
  select * from a1 where tbs > 0
)
,
i (tx, pt, pa, sq) as
(
  select           '    -- unload and load dummy'
                                        , '00title ' , -1,  1 from a1

  union all select '    -- ' || strip(char(tbs)) || ' tables and '
                   || strip(char(pas)) || ' partitions'
                                        , '00title ' , -1,  3 from a1
  union all select 'LISTDEF TLST'
                                        , '10listDef', -1,  1 from a0
  union all select '  INCLUDE TABLE OA1A.' || tb
                   || ' PARTLEVEL ' || strip(char(pa))
                                        , '12' || tb , pa,  1 from p
  union all select 'UNLOAD LIST TLST'   , '30unload' , -1,  1 from a0
  union all select '  PUNCHDDN TPUNCH UNLDDN TSRECD'
                                        , '30unload' , -1,  3 from a0
  union all select '  SHRLEVEL REFERENCE'
                                        , '30unload' , -1,  5 from a0
  union all select 'LOAD DATA LOG NO'   , '42' || tb , -1,  1 from t
  union all select '    WORKDDN(TSYUTS, TSOUTS)'
                                        , '42' || tb , -1,  2 from t

  union all select '    STATISTICS INDEX(ALL) REPORT NO UPDATE ALL'
                                        , '42' || tb , -1,  3 from t
  union all select '    IDENTITYOVERRIDE'
                                        , '42' || tb , -1,  4 from t
  union all select '  INTO TABLE OA1A.' || tb || ' PART '
                   || strip(char(pa))
                                        , '42' || tb , pa,  1 from p
  union all select '    IGNOREFIELDS YES'
                                        , '42' || tb , pa,  3 from p
  union all select '    RESUME NO REPLACE COPYDDN(TCOPYS) INDDN INDUMMY'
                                        , '42' || tb , pa,  5 from p
)
select char(tx, 80) /* , pt, pa, sq  */
   from i
   order by pt, pa, sq
;
//UTIL     EXEC PGM=DSNUTILB,PARM='DBAF,A540769W.LOAD'                  00020001
//SYSPRINT   DD SYSOUT=*
//UTPRINT    DD SYSOUT=*
//SYSTEMPL   DD DSN=DBAF.DBAA.LISTDEF(TEMPL),DISP=SHR
//INDUMMY    DD DUMMY
//SYSIN      DD DISP=SHR,DSN=*.DEL.SQL