RE: 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?
> >
>
If your *only* concern is about locks, not logs and/or other users needing
access to the table you can:
BEGIN WORK;
LOCK TABLE tabname IN EXCLUSIVE MODE;
DELETE FROM tabname WHERE where_condition;COMMIT WORK;
Otherwise, you will have to
- Declare a Select cursor with hold.
- Begin a transaction
- While more rows
- Fetch a row.
- Delete the row.
- Count a number of rows (say 1000) Commit the transaction, Begin a new
transaction.
- Commit the Transaction
HTH
Tino
Thanks to everyone who replied to my question. I'm new to ESQL/C, and I've
never used a cursor to delete rows, but that is what I'll like do, since I want
to maximize the access other processes have to the table as well.
- Randy Galbraith
Spammers please note: UCE/Spam is not welcome at my inbox.
Tino Cremidis 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?
> > >
> >
> If your *only* concern is about locks, not logs and/or other users needing
> access to the table you can:
>
> BEGIN WORK;
> LOCK TABLE tabname IN EXCLUSIVE MODE;
> DELETE FROM tabname WHERE where_condition;> COMMIT WORK;
>
> Otherwise, you will have to
> - Declare a Select cursor with hold.
> - Begin a transaction
>
> - While more rows
> - Fetch a row.
> - Delete the row.
> - Count a number of rows (say 1000) Commit the transaction, Begin a new
> transaction.
>
> - Commit the Transaction
>
> HTH
>
> Tino