How to delete old records from IIR tables?

Document ID : KB000054667
Last Modified Date : 14/02/2018
Show Technical Document Details

Description

How can old records be deleted from IIR database tables ? (e.g. to remove records older than 180 days from a table)

Solution

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);