Monthly Archives: January 2012

DB2 SQL to Archive records and delete records

Archive records

INSERT INTO MYSCHEMA.ARCHIVETABLE (
  SELECT FIELD1,FIELD2,FIELD3
  FROM MYSCHEMA.TABLETOARCHIVE
  WHERE DATEFIELD < (year(current date))||right ('00' || month(current date)-1,2)||right ('00' || day(current date),2)
)

The above query will archive records from TABLETOARCHIVE to ARCHIVETABLE where the  DATEFIELD is before the past month (DATEFIELD  is in YYYYMMDD format).

Delete archived records

DELETE FROM MYSCHEMA.TABLETOARCHIVE
WHERE DATEFIELD < (year(current date))||right ('00' || month(current date)-1,2)||right ('00' || day(current date),2)