Archive records

[sourcecode language=”sql”]
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)
)
[/sourcecode]

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

[sourcecode language=”sql”]
DELETE FROM MYSCHEMA.TABLETOARCHIVE
WHERE DATEFIELD < (year(current date))||right (’00’ || month(current date)-1,2)||right (’00’ || day(current date),2)
[/sourcecode]