zOs/SQL/WI020

delete
  FROM  oa1t.TWI020A1
  where (  WI02001C
        ,  WI02001A
        ,  WI02001S
        )
      in (select  WI02001C
               ,  WI02001A
               ,  WI02001S
              FROM  oa1t.TWI020A1
              WHERE WI02040T < TIMESTAMP(CURRENT TIMESTAMP - 11 DAYS)
                  AND WI02001S IN ('D','P','R')
              fetch first 100 rows only
         )
;
  ROLLBACK
  ;      :::;;;
select WI02001S
    , count(*) total
    , sum(case when WI02040T < TIMESTAMP(CURRENT TIMESTAMP - 21 DAYS)
               then 1 else 0 end) old
    , sum(case when WI02040T < TIMESTAMP(CURRENT TIMESTAMP - 21 DAYS)
               then 0 else 1 end) new
    from oa1p.twi020a1
    group by WI02001S
    order by 1 asc
    with ur
    ;;;
select count(*) , WI02001S
    from oa1p.twi020a1
    group by WI02001S
    order by 2 desc
    with ur
    ;;;
select count(*) , date(WI02040T)
    from oa1p.twi020a1
    group by date(WI02040T)
    order by 2 desc
    with ur
;;;;
SET CURRENT SQLID='s100447';
CREATE        INDEX OA1P.IWI020A$
  ON OA1P.TWI020A1
   (WI02001S              ASC,
    WI02040T              ASC)
  USING STOGROUP GSMS
  PRIQTY -1 SECQTY -1
  ERASE  NO
  FREEPAGE 15 PCTFREE 10
  GBPCACHE CHANGED
  not CLUSTER
  COMPRESS NO
  BUFFERPOOL BP1
  CLOSE YES
  COPY NO
  DEFER NO
  DEFINE YES
  PIECESIZE 2 G;
COMMIT;