Purging old data from table
A user wanted a regular (weekly) purge of ~40,000 old rows/day from a log table without hitting long-transaction errors, and asked whether locking the table and deleting by serial key below a cutoff was sensible. Art Kagel recommended his dbdelete.ec utility (IIUG repository, package utils2_ak), which deletes in ROWID-based blocks of ~800 rows, committing each block, so it avoids long transactions and is restartable. Caveats noted: ROWIDs aren't usable on fragmented tables unless created WITH ROWIDS, and composite-key tables aren't supported. Users without ESQL/C were pointed to the free Client SDK; a link error for the missing itoa symbol was fixed by compiling with esql -Dnoitoa=1.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
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
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 ...) Just the kind of job I wrote dbdelete.ec for. You can use the ROWID based delete option with the filter on date_changed. Dbdelete should be able to clean out the 40,000 rows in about a minute assuming you have an index that starts with date_changed. Dbdelete.ec is part of the package utils2_ak in the IIUG Software Repository. Dbdelete deletes, based on ROWID, in blocks of about 800 rows per transaction and commits each block so there is no long transaction problem and it is a) fully restartable and b) you can run multiple copies if needed (though this cleanup probably will perform fine with only one copy). Art S. Kagel
I downloaded the file utils2_ak, but we don't have ESQL/C on our system for me to compile the utilities.
Kire Prostizenovski wrote: > I downloaded the file utils2_ak, but we don't have ESQL/C on our > system for me to compile the utilities. That's sad. Why don't you go and get ClientSDK? I believe it is free... http://www.intraware.com -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>
Kire Prostizenovski wrote: > > I downloaded the file utils2_ak, but we don't have ESQL/C on our system for > me to compile the utilities. You can also compile my utilities with C4GL V7.xx if you have that or you can just order the SDK 2.20 or 2.30 from IntraWare and download it. The Client SDK is FREE so noone has any excuse for not having ESQL/C handy anymore for creating utilities. Art S. Kagel
Isn't the rowid no longer valid for fragmented tables? Art S. Kagel wrote: > > 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 ...) > > Just the kind of job I wrote dbdelete.ec for. You can use the ROWID based > delete option with the filter on date_changed. Dbdelete should be able to > clean out the 40,000 rows in about a minute assuming you have an index > that starts with date_changed. Dbdelete.ec is part of the package > utils2_ak in the IIUG Software Repository. Dbdelete deletes, based on > ROWID, in blocks of about 800 rows per transaction and commits each block > so there is no long transaction problem and it is a) fully restartable and > b) you can run multiple copies if needed (though this cleanup probably will > perform fine with only one copy). > > Art S. Kagel
Maurice Fontaine wrote: > > Isn't the rowid no longer valid for fragmented tables? I did not remember Kire mentioning the table as being fragmented. Anyway, ROWIDs are not valid for fragmented tables unless the table was created "WITH ROWIDS", correct. It should be noted that, as currently coded, dbdelete.ec is NOT as fast at deleting if it cannot use rowids. HOWEVER, it would be trivial to recode it to use the same method it uses for rowids for a single column unique key, like a serial number. For tables whose only unique (or at least sufficiently unique) key is composite the method does not work at all. Art S. Kagel > Art S. Kagel wrote: > > > > 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 ...) > > > > Just the kind of job I wrote dbdelete.ec for. You can use the ROWID based > > delete option with the filter on date_changed. Dbdelete should be able to > > clean out the 40,000 rows in about a minute assuming you have an index > > that starts with date_changed. Dbdelete.ec is part of the package > > utils2_ak in the IIUG Software Repository. Dbdelete deletes, based on > > ROWID, in blocks of about 800 rows per transaction and commits each block > > so there is no long transaction problem and it is a) fully restartable and > > b) you can run multiple copies if needed (though this cleanup probably will > > perform fine with only one copy). > > > > Art S. Kagel
> Just the kind of job I wrote dbdelete.ec for. You can use the ROWID based > delete option with the filter on date_changed. Dbdelete should be able to > clean out the 40,000 rows in about a minute assuming you have an index > that starts with date_changed. Dbdelete.ec is part of the package > utils2_ak in the IIUG Software Repository. Dbdelete deletes, based on > ROWID, in blocks of about 800 rows per transaction and commits each block > so there is no long transaction problem and it is a) fully restartable and > b) you can run multiple copies if needed (though this cleanup probably will > perform fine with only one copy). > > Art S. Kagel Art, I am trying to compile the dbdelete program using the gnu compiler and get the following error: collect2: ld returned 1 exit status /usr/ccs/bin/ld: Unsatisfied symbols: itoa (code) I am not a C programmer, so I was hoping you could help me out. The dostats (which is a great tool) compiles without a problem. Any thoughts? Thanks. Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.
jsmith112469@my-deja.com wrote: >[Previous post SNIPPED] > I am trying to compile the dbdelete program using the gnu compiler and > get the following error: > > collect2: ld returned 1 exit status > /usr/ccs/bin/ld: Unsatisfied symbols: > itoa (code) > > I am not a C programmer, so I was hoping you could help me out. The > dostats (which is a great tool) compiles without a problem. Any > thoughts? I already have. Dbdelete.ec includes an #if defined( noitoa ) for systems that do not provide this useful function (on Sun itoa is automatically #defined to the Solaris numtos() function) which declares an inline function to perform itoa's functionality using sprintf(). Just compile Thus: esql -Dnoitoa=1 -o dbdelete dbdelete.ec Art S. Kagel