Throttle Deletes
Dave wanted a way to delete thousands of rows in small batches (something like "DELETE FIRST 500") to limit lock counts and keep transactions short on Informix 11.50.FC6. Replies said there's no such SQL syntax, but suggested writing an SPL procedure using a hold cursor: select the rows to delete, loop with DELETE WHERE CURRENT OF, and COMMIT/BEGIN WORK every N rows. Art Kagel pointed to his dbdelete.ec program in the utils2_ak package on the IIUG Software Repository, which commits in batches (8192 rows by default). Another poster advised enlarging physlogbuff and logbuff. Dave thanked the responders.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Good Morning, We are running Informix 11.50.FC6. I was wondering if we have any informix SQL that will allow me to throttle a delete statement against a table. Similar to being able to use "Select first 500 * from mytable" - I would want to do "delete first 500 from mytable" and run that in a loop until there are no more records to delete. Monthly we need to delete many thousands of records from a table and I want to do it in smaller SQL steps so as not to cause too many locks and keep the transactions smaller and shorter. I know I can use other mechanisms like temp tables or where clauses to control the delete statement. Any ideas? --Dave
May be placing the delete into a SPL procedure that manages the portions to be deleted? regards Joerg Volz ------------------------------------------------------------------------ ----- -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of Dave Sent: Thursday, May 13, 2010 3:28 PM To: informix-list@iiug.org Subject: Throttle Deletes Good Morning, We are running Informix 11.50.FC6. I was wondering if we have any informix SQL that will allow me to throttle a delete statement against a table. Similar to being able to use "Select first 500 * from mytable" - I would want to do "delete first 500 from mytable" and run that in a loop until there are no more records to delete. Monthly we need to delete many thousands of records from a table and I want to do it in smaller SQL steps so as not to cause too many locks and keep the transactions smaller and shorter. I know I can use other mechanisms like temp tables or where clauses to control the delete statement. Any ideas? --Dave _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list IT Handel und Beratung Jorg Volz Bernhard-Fruh-Str. 7 77855 Achern GERMANY Tel: +49 (0)7841-681651 Fax: +49 (0)7841-681654 Mobil: +49 (0)170-2989757 VAT-ID: DE201383541 http://www.it-volz.de
On May 13, 8:27 am, Dave <in4mix...@gmail.com> wrote: > Good Morning, > > We are running Informix 11.50.FC6. I was wondering if we have any > informix SQL that will allow me to throttle a delete statement against > a table. Similar to being able to use "Select first 500 * from > mytable" - I would want to do "delete first 500 from mytable" and run > that in a loop until there are no more records to delete. Monthly we > need to delete many thousands of records from a table and I want to do > it in smaller SQL steps so as not to cause too many locks and keep the > transactions smaller and shorter. I know I can use other mechanisms > like temp tables or where clauses to control the delete statement. > > Any ideas? > > --Dave You could pretty easily write a stored procedure, using a hold cursor to do what you want. You just need to write a select that gets all the rows you want to delete, loop through the cursor and delete where current of, and lastly have a counter and then commit work and issue a new begin work after how ever many rows you want per transaction. Jacques Renaut IBM Informix Advanced Support APD Team
Thank you both Joerg and Jacques for the SPL ideas. --David On Thu, May 13, 2010 at 9:39 AM, jrenaut <jprenaut@yahoo.com> wrote: > On May 13, 8:27 am, Dave <in4mix...@gmail.com> wrote: > > Good Morning, > > > > We are running Informix 11.50.FC6. I was wondering if we have any > > informix SQL that will allow me to throttle a delete statement against > > a table. Similar to being able to use "Select first 500 * from > > mytable" - I would want to do "delete first 500 from mytable" and run > > that in a loop until there are no more records to delete. Monthly we > > need to delete many thousands of records from a table and I want to do > > it in smaller SQL steps so as not to cause too many locks and keep the > > transactions smaller and shorter. I know I can use other mechanisms > > like temp tables or where clauses to control the delete statement. > > > > Any ideas? > > > > --Dave > > You could pretty easily write a stored procedure, using a hold cursor > to do what you want. You just need to write a select that gets all > the rows you want to delete, loop through the cursor and delete where > current of, and lastly have a counter and then commit work and issue a > new begin work after how ever many rows you want per transaction. > > Jacques Renaut > IBM Informix Advanced Support > APD Team > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >
Download my utils2_ak package from the IIUG Software Repository and compile the dbdelete.ec program. This is exactly what you want. By default it delete 8192 rows at a time and commits that. It is also lightening fast. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, May 13, 2010 at 9:27 AM, Dave <in4mixdba@gmail.com> wrote: > Good Morning, > > We are running Informix 11.50.FC6. I was wondering if we have any > informix SQL that will allow me to throttle a delete statement against > a table. Similar to being able to use "Select first 500 * from > mytable" - I would want to do "delete first 500 from mytable" and run > that in a loop until there are no more records to delete. Monthly we > need to delete many thousands of records from a table and I want to do > it in smaller SQL steps so as not to cause too many locks and keep the > transactions smaller and shorter. I know I can use other mechanisms > like temp tables or where clauses to control the delete statement. > > Any ideas? > > --Dave > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >
and do not forget to make physlogbuff bigger and loglogbuf bigger. Superboer. On 13 mei, 19:33, Art Kagel <art.ka...@gmail.com> wrote: > Download my utils2_ak package from the IIUG Software Repository and compile > the dbdelete.ec program. This is exactly what you want. By default it > delete 8192 rows at a time and commits that. It is also lightening fast. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > IIUG Board of Directors (a...@iiug.org) > > See you at the 2010 IIUG Informix Conference > April 25-28, 2010 > Overland Park (Kansas City), KSwww.iiug.org/conf > > Disclaimer: Please keep in mind that my own opinions are my own opinions and > do not reflect on my employer, Advanced DataTools, the IIUG, nor any other > organization with which I am associated either explicitly, implicitly, or by > inference. Neither do those opinions reflect those of other individuals > affiliated with any entity with which I am affiliated nor those of the > entities themselves. > > On Thu, May 13, 2010 at 9:27 AM, Dave <in4mix...@gmail.com> wrote: > > Good Morning, > > > We are running Informix 11.50.FC6. I was wondering if we have any > > informix SQL that will allow me to throttle a delete statement against > > a table. Similar to being able to use "Select first 500 * from > > mytable" - I would want to do "delete first 500 from mytable" and run > > that in a loop until there are no more records to delete. Monthly we > > need to delete many thousands of records from a table and I want to do > > it in smaller SQL steps so as not to cause too many locks and keep the > > transactions smaller and shorter. I know I can use other mechanisms > > like temp tables or where clauses to control the delete statement. > > > Any ideas? > > > --Dave > > _______________________________________________ > > Informix-list mailing list > > Informix-l...@iiug.org > >http://www.iiug.org/mailman/listinfo/informix-list