Novell Home

Purging or Archiving Database Information

Novell Cool Solutions: Tip
By Jason Doering

Digg This - Slashdot This

Posted: 2 Jun 2005
 

Problem

How do I purge old data, say more than a month old, from the database? Or how would I archive the same data?

Solution

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.

Archiving Records

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

Deleting Records

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

© 2014 Novell