Minimizing lock usage with $DELETE

Consider these ESQL/C statement: $BEGIN WORK; $DELETE FROM foo WHERE (a_date < :ctl_jdate) AND (b_date < :ctl_jdate); $COMMIT WORK; Table "foo" has 1.2 million rows. Our DBA has warned me that I may blow out the available number of locks if I attempt this statement. How does one deal with this? - Randy Galbraith Spammers please note: UCE/Spam is not welcome at my inbox.

Randy Galbraith <rgalbrai@rez.com> wrote: >Consider these ESQL/C statement: > >$BEGIN WORK; >$DELETE FROM foo WHERE (a_date < :ctl_jdate) AND (b_date < :ctl_jdate); >$COMMIT WORK; > >Table "foo" has 1.2 million rows. Our DBA has warned me that I may blow >out the available number of locks if I attempt this statement. How does >one deal with this? > >- Randy Galbraith > Spammers please note: UCE/Spam is not welcome at my inbox. > > You can lock the table before beginning the delete. This will only use a few locks as opposed to the number that will be used otherwise. Another way is to "flutter" the transaction so that only a few rows are locked at a time. ___________________________________________________________ Jay Aymond EXE Technologies jay_aymond@exe.com

>Consider these ESQL/C statement: > >$BEGIN WORK; >$DELETE FROM foo WHERE (a_date < :ctl_jdate) AND (b_date < :ctl_jdate); >$COMMIT WORK; > >Table "foo" has 1.2 million rows. Our DBA has warned me that I may blow >out the available number of locks if I attempt this statement. How does >one deal with this? > Considering that you are in ESQL/C and not doing this with DBACCESS, why not simply manage the transaction to limit the number of rows affected at any one time? Preparing the select for update cursor and delete where current statement will take a few lines of code but will allow the process to work with any number of rows. Keep the transaction size down just in case there is another you on the system at the same time.

You can write: $BEGIN WORK; $LOCK TABLE foo IN EXCLUSIVE MODE $DELETE FROM foo WHERE (a_date < :ctl_jdate) AND (b_date < :ctl_jdate); $COMMIT WORK; Marco Randy Galbraith <rgalbrai@rez.com> escribi' en el mensaje de noticias 370A980E.8C3071C7@rez.com... > Consider these ESQL/C statement: > > $BEGIN WORK; > $DELETE FROM foo WHERE (a_date < :ctl_jdate) AND (b_date < :ctl_jdate); > $COMMIT WORK; > > Table "foo" has 1.2 million rows. Our DBA has warned me that I may blow > out the available number of locks if I attempt this statement. How does > one deal with this? > > - Randy Galbraith > Spammers please note: UCE/Spam is not welcome at my inbox. > >