Purging or Archiving Database Information
Novell Cool Solutions: Tip
By Jason Doering
Digg This -
Posted: 2 Jun 2005
How do I purge old data, say more than a month old, from the database? Or how would I archive the same data?
First you need to decide whether to archive the older records or just delete them entirely. Then you edit your MySQL channel in the Secure Logging Server configuration in iManager.
To archive the older records, set the SQL Expiration command to this:
create table newtable ($T) $e;RENAME TABLE $l TO l$n, newtable TO $l;
and set the "Expire at interval" field to a time (for daily), a day (for weekly), or the month. This will start a new table and move the old records to a new table named lyyyymmdd, (such as l20050601 for June 1, 2005).
To delete the entries entirely, set the SQL Expiration command to something like this:
DELETE FROM $l WHERE clienttimestamp<(unix_timestamp()-43200);
The 43,200 number is a value in seconds, which equals 12 hours. So if you want to expire after say 7 days, set this number to 604800 (7 days x 24 hours x 60 minutes x 60 seconds).
After you make these changes, you must reload lengine in order for it to use the new settings.
Novell Cool Solutions (corporate web communities) are produced by WebWise Solutions. www.webwiseone.com