Re: Purging old data from table
On Fri, 10 Sep 1999, Kire Prostizenovski wrote:
> Guys,
>
> Thank you all for your help regarding the large *.unl file and long
> transactions. Now that I have only the data we need in this table, I would
> like to write some sort of script to purge the oldest data on a regular
> basis, maybe every weekend. Considering that we have on average about
> 40,000 rows per day, would it be feasible to do the following;
>
> 1. lock table parts_log in exclusive mode
> 2. <cutoff log_no> = select max(log_no) from parts_log where date_changed =
> <cutoff date>
> 3. delete from parts_log where log_no <= <cutoff log_no>
>
> *log_no is a serial key
>
> Or can anyone see a smarter way ?
>
> Again, I might run into long transaction problems here.
> (I think an upgrade to v7.31 might be in order ...)
>
> Thanks,
>
> Kire
>
> Kire Prostizenovski
> Business Analyst
> J Blackwood and Son Limited
> 13 Cooper Street
> Smithfield NSW 2164
> Australia
> Phone +61 2 9203 0133 Fax +61 2 9203 0160
>
>
>
How about a more direct:
delete from parts_log where date_changed <= <cutoff_date>
I would still lock the table in exclusive mode if your users can live
without the table for a few minutes. This will prevent you from running
out of locks.
As far as long transactions, you can put the delete statement in a script
or application program language that deletes a group of rows followed by a
commit, until it runs out of rows to delete. This solves both problems,
lock exaustion and long transactions, since the locks are released after
the commit, and the quantity data per transaction should not fill your
logs beyond the LTXHWM setting in the onconfig file. I have some
examples of grouping deletes in just this way, if you need them. We keep
log files on several key tables, and delete the log entries on a
routine basis, so we have the same problem as you.
Or, increase the size or number of your logical logs. This is done by
using the onparams utility, which is described in the Online Dynamic
Server Administrator's Guide.
====================================================================
Harold Luse Phone: (970) 491-4120
Veterinary Teaching Hospital Fax: (970) 491-4123
Colorado State University Pager: (970) 229-8173
Fort Collins, Colorado USA E-mail: hluse@vth.colostate.edu
====================================================================