zOs/SQL/PLANCLE1

//A540769R JOB (CP00,KE50),'DB2 REO',
//         MSGCLASS=T,TIME=1440,CLASS=M1,
//         NOTIFY=&SYSUID,REGION=0M,SCHENV=DB2ALL
//*********************************************************************
//*
//*   planCle1: alter cluster on plan_table and reorg
//*        use: chg all DP4G db2SubSystem; sub
//*
//*   plan table / explain table cleanup
//*       planCle0 sql to select/count explains to delete
//*       planCle1 alter clustering, reorg plantable
//*       planCle2 delete rows from plantable
//*       planCle3 delete rows from all other explain tables
//*********************************************************************
//ALTER    EXEC PGM=IKJEFT01,DYNAMNBR=200,TIME=99
//SYSTSIN    DD *
    DSN SYSTEM(DP4G)
    RUN PROGRAM(DSNTEP2) PARMS('ALIGN(LHS)') PLAN(DSNTEP2)
//SYSTSPRT   DD SYSOUT=*
//SYSPRINT   DD SYSOUT=* DSN=A540769.TMP.TEXV(GRENZE)
//SYSUDUMP   DD SYSOUT=*
//SYSTERM    DD DUMMY
//SYSIN      DD *
alter index cmnbatch.PLAN_TABLE_IDX1 not cluster;
alter index cmnbatch.PLAN_TABLE_PROG_IX cluster;
  CREATE INDEX cmnBatch.DSN_FUNCTION_Table_idx1
         ON cmnBatch.DSN_FUNCTION_Table
         ( "QUERYNO"
          ,"EXPLAIN_TIME"
         )
      USING STOGROUP GSMS PRIQTY -1 SECQTY -1 ERASE  NO
      FREEPAGE 0 PCTFREE 10
      GBPCACHE CHANGED
      NOT PADDED
      DEFER YES
      COMPRESS NO
      BUFFERPOOL BP1
      CLOSE YES
      COPY NO
      DEFINE YES
      PIECESIZE 2 G
      CLUSTER
;
CREATE INDEX cmnbatch.DSN_STAT_FEEDBACK_idx1
       ON cmnbatch.DSN_STAT_FEEDBACK
       ( "QUERYNO"
        ,"EXPLAIN_TIME"
       )
    USING STOGROUP GSMS PRIQTY -1 SECQTY -1 ERASE  NO
    FREEPAGE 0 PCTFREE 10
    GBPCACHE CHANGED
    NOT PADDED
    DEFER YES
    COMPRESS NO
    BUFFERPOOL BP1
    CLOSE YES
    COPY NO
    DEFINE YES
    PIECESIZE 2 G
    CLUSTER
;
//       IF ALTER.RUN AND (ALTER.RC = 0 OR ALTER.RC = 4) THEN
//REO      EXEC PGM=DSNUTILB,TIME=1440,
//             PARM=(DP4G,'A540769R.REORG'),
//             REGION=0M
//SYSPRINT   DD SYSOUT=*
//SYSUDUMP   DD SYSOUT=*
//SYSTEMPL   DD DISP=SHR,DSN=DP4G.DBAA.LISTDEF(TEMPL)
//UTPRINT    DD SYSOUT=*
//RNPRIN01   DD SYSOUT=*
//STPRIN01   DD SYSOUT=*
//INDUMMY    DD DUMMY
//SYSIN      DD *
-- OPTIONS PREVIEW
  LISTDEF TPLIST
    INCLUDE TABLE CMNBATCH.PLAN_TABLE PARTLEVEL
    INCLUDE TABLE CMNBATCH.DSN_FUNCTION_TABLE PARTLEVEL
    INCLUDE TABLE CMNBATCH.DSN_STAT_FEEDBACK
---- reorg -------------------------------------------------------------
  REORG TABLESPACE  LIST TPLIST
    LOG NO
    SORTDATA
    COPYDDN(TCOPYD)
    SHRLEVEL CHANGE
    DRAIN_WAIT 20
      RETRY 20
      RETRY_DELAY 180
      MAXRO 20
      DRAIN ALL
      LONGLOG CONTINUE
      DELAY 600
      TIMEOUT TERM
    UNLDDN TSRECD
    UNLOAD CONTINUE
    PUNCHDDN TPUNCH
    DISCARDDN TDISC
    SORTKEYS
    SORTDEVT DISK
    STATISTICS
      INDEX ALL KEYCARD
      UPDATE ALL
//       ENDIF