How to delete old records from IIR tables?

Products

• Database Analyzer (IMS Tools)
• Mainframe Configuration Manager for IMS for z/OS
• IMS TOOLS - MISC
• Compress Data Compression (IMS Tools)
• Database Analyzer for IMS for z/OS
• Database Copier for IMS for z/OS
• Database Organizer for IMS for z/OS
• Mainframe Extended Terminal Manager (IMS Tools)
• High Performance Recovery for IMS for z/OS
• Database Organizer (IMS Tools)
• Mainframe Program Restart Manager for IMS for z/OS
• Secondary Index Builder for IMS for z/OS
• Secondary Index for IMS for z/OS
• DATABASE MANAGEMENT SOLUTIONS FOR IMS FOR Z/OS

Issue/Introduction

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

Resolution

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