Breaking up transaction

I need to breakup a delete statement to prevent creating a long transaction (7.31.uc3). I need to do this for a couple dozen tables. begin work; delete from big_table; --where only the first 100 rowscommit; Any ideas on 1) using straight sql to limit the number of rows deleted on a pass 2) Doing it in a stored proc using cursors 3) writing a nasty shell script to count the number of rows and figure out an appropriate where statement or read key values from a file. I know I can get #3 to work, but it is ugly and not very flexible or dynamic. Ideas appreciated. I must be missing something easy. - Bob Carts

Robert Carts wrote: > > I need to breakup a delete statement to prevent creating a long transaction > (7.31.uc3). I need to do this for a couple dozen tables. > > begin work; > delete from big_table; --where only the first 100 rows> commit; > > Any ideas on 1) using straight sql to limit the number of rows deleted on a > pass 2) Doing it in a stored proc using cursors 3) writing a nasty shell > script to count the number of rows and figure out an appropriate where > statement or read key values from a file. I know I can get #3 to work, but > it is ugly and not very flexible or dynamic. > > Ideas appreciated. I must be missing something easy. - Bob Carts Get my dbdelete utility which is in the package utils2_ak available from the IIUG Software Repository, it does exactly what you want and at the very least you can 'steal' the method used there. -- Art S. Kagel & Family kagel@erols.com