How can old records be deleted from IIR database tables? (e.g. to remove records older than 180 days from a table)
This SQL statement can be used to delete old table entries from an IIR database table:
DELETE FROM tabname WHERE DATE(RUNSTAMP)<(CURRENT DATE - n DAYS);
e.g.
DELETE FROM DBA001 WHERE DATE(RUNSTAMP)<(CURRENT DATE - 180 DAYS);
Execute program COB430 to process the above SQL statement, using this job control (please change dataset names accordingly):
//* //EXECSQL PROC BDCHLQ='qual', // BDTHLQ='qual', // SYSOUT='*' /* INST STANDARD SYSOUT CLASS //* //COBEXEC EXEC PGM=COB430,COND=(5,LT),REGION=4M //STEPLIB DD DSN=&BDCHLQ..CUSLIB,DISP=SHR // DD DSN=&BDTHLQ..CAILIB,DISP=SHR //SYSUDUMP DD SYSOUT=&SYSOUT //SYSOUT DD SYSOUT=&SYSOUT //SYSDBOUT DD SYSOUT=&SYSOUT //SYSPRINT DD SYSOUT=&SYSOUT //LISTER DD SYSOUT=&SYSOUT //ERRORL DD SYSOUT=&SYSOUT //CARDIN DD DUMMY // PEND //* //COB430 EXEC EXECSQL //* //COBEXEC.CARDIN DD * DELETE FROM DBA001 WHERE DATE(RUNSTAMP)<(CURRENT DATE - 180 DAYS);