zOs/SQL/CATCOPWT

//A5407698 JOB (CP00,KE50),
//       'CATALOG',MSGCLASS=T,TIME=1440,
//         NOTIFY=&SYSUID,REGION=0M
//*MAIN CLASS=LOG,SYSTEM=S12
//P01     EXEC PGM=IKJEFT01,
//             DYNAMNBR=200,
//             TIME=99
//SYSTSIN   DD *
   DSN SYSTEM(DBTF)
   RUN PROGRAM(DSNTIAUL) PARMS('SQL')
   END
//SYSTSPRT  DD SYSOUT=*
//SYSPRINT  DD SYSOUT=*
//SYSUDUMP  DD SYSOUT=*
//SYSTERM   DD DUMMY
//*YSPUNCH  DD DUMMY
//SYSPUNCH  DD DSN=A540769.TMPUL.CATCOPWT.PU,
//             DISP=(NEW,CATLG,CATLG),
//             SPACE=(CYL,(1,1)),
//             DATACLAS=NULL30,MGMTCLAS=S005Y000,
//             RECFM=FB,LRECL=80
//SYSREC00  DD DSN=A540769.TMPUL.CATCOPWT.UN,
//             DISP=(NEW,CATLG,CATLG),
//             SPACE=(CYL,(1000,500)),
//             DATACLAS=NULL30,MGMTCLAS=S005Y000,
//             RECFM=FB,LRECL=80
//*            DCB=(LRECL=5000,RECFM=VB,BLKSIZE=32760)
//SYSREC01  DD SYSOUT=*,
//             RECFM=VB,LRECL=200
//SYSIN     DD *
 SELECT
     C.DBNAME, C.TSNAME, C.DSNUM, C.ICTYPE, C.DSNAME, C.TIMESTAMP,
               C.COPYPAGESF * 1024 * S.PGSIZE COPIED,
     CASE WHEN C.ICTYPE <> 'F'     THEN 'IN'
          WHEN C.TIMESTAMP = X.NEW THEN 'NW'
          WHEN C.TIMESTAMP = X.LAST THEN 'LA'
          ELSE 'FU' END MYTYPE
     FROM
 (
 SELECT DBNAME, TSNAME, DSNUM, MAX(TIMESTAMP) NEW,
         MAX(CASE WHEN TIMESTAMP <= '2008-03-07-11.11.11'
                  THEN TIMESTAMP ELSE NULL END) LAST
     FROM A540769.SYSCOPY
     WHERE ICTYPE = 'F'
     GROUP BY DBNAME, TSNAME, DSNUM
 ) X ,
     A540769.SYSCOPY C,
     SYSIBM.SYSTABLESPACE S
     WHERE C.ICTYPE IN ('F', 'I')
         AND C.DBNAME = X.DBNAME
         AND C.TSNAME = X.TSNAME
         AND C.DSNUM  = X.DSNUM
         AND C.TIMESTAMP <= X.NEW
         AND C.TIMESTAMP >= X.LAST
         AND S.DBNAME = X.DBNAME
         AND S.NAME = X.TSNAME
     WITH UR
 ;