Search code examples
stored-proceduresdb2dynamic-programmingcobol

I have to delete 30 days old data based on timestamp in DB2

I have a requirement to delete 30 days old record in DB2. I used below code to delete, It worked fine but current timestamp keep changing. This cause my procedure to go on loop.

DELETE FROM TABLE 
WHERE TIMESTAMP_FIELD < CURRENT TIMESTAMP - 31 DAYS

I'm using my procedure as generic delete, So I will not able to use host variable. I tried subdate ADDDATE but no luck.

Please help me out.

Thanks

Solution

  • Current Timestamp is constantly changing so you're liable to chase your own tail if you use it for comparisons down to the microsecond level.

    If you need a less precise value to use for deletions you can cast the current date - 31 days together with '00:00:00' (midnight). This value will only change when the current date does so your results are more predictable.

      DELETE FROM TABLE 
  WHERE TIMESTAMP_FIELD < TIMESTAMP(CURRENT DATE - 31 DAYS, '00:00:00')