Can you commit after x deletes. 1

[wdellin]

Is there a way to issue a commit during a delete. Trying to delete 150000 rows from a table using dbaccess and it not only uses all locks, but it takes forever(6 hours before lock problem). Is there a way that I can issue a commit after deleting say 10000 rows? When I break the deletes down to multiple jobs I deleted all rows in 25 minutes.

table_a
group_id serial


delete from table_a where group_id < 1350000;

 

[vpshriyan]

Hi,

You can resolve this problem in different way.

1. One of the easiest way, if feel is to lock the table exclusively and do the delete operation, assuming that you can cease table in this mode and have ample logical log area to record the changes.

begin;
set lock mode to wait;
lock table table_a in exclusive mode;
delete from table_a where group_id < 1350000;
select 'Rows deleted:', dbinfo('sqlca.sqlerrd2') from systables where tabid=1;
commit;

2. Identify the rowids and delete single row at a time.
output to 'deljob.sql' without headings select 'delete from table_a where rowid=',rowid,' ;' from table_a ;
dbaccess testdb deljob.sql

3. Your business logic comes handy this time. Select rows for deletion for lesser range in multiplicity.
delete from table_a where group_id between 1 and 200000;
select 'Rows deleted:', dbinfo('sqlca.sqlerrd2') from systables where tabid=1;
delete from table_a where group_id between 200000 and 400000;
select 'Rows deleted:', dbinfo('sqlca.sqlerrd2') from systables where tabid=1;
delete from table_a where group_id between 400000 and 600000;
select 'Rows deleted:', dbinfo('sqlca.sqlerrd2') from systables where tabid=1;
so on ...

Regards,
Shriyan

 

[vpshriyan]

Oops..

2. Identify the rowids and delete single row at a time.

output to 'deljob.sql' without headings
select 'delete from table_a where rowid=',rowid,' ;'
from table_a where group_id < 1350000 ;

dbaccess testdb deljob.sql

 

[wdellin]

Thanks for the suggestions. I think the lock maybe my best bet.

One more question...
within dbaccess
dbaccess ustar_m - << !
delete from stmt_print_queue where
(request_id >= ${minnu1} and request_id <= ${maxnu1});
delete from stmt_print_queue where
(request_id >= ${minnu2} and request_id <= ${maxnu2});
!

when the first delete completes, will that commit since there is another delete executing behind it?

 

[vpshriyan]

Hi Dellin,

In the example you have quoted, you need to select the database, to execute the delete job.

dbaccess ustar_m - << !
database testdb;
delete from stmt_print_queue where
(request_id >= ${minnu1} and request_id <= ${maxnu1});
delete from stmt_print_queue where
(request_id >= ${minnu2} and request_id <= ${maxnu2});
!

or

dbaccess ustar_m testdb << !
delete from stmt_print_queue where
(request_id >= ${minnu1} and request_id <= ${maxnu1});
delete from stmt_print_queue where
(request_id >= ${minnu2} and request_id <= ${maxnu2});
!

Since a transaction is not marked explicitly via begin ; or begin work; statement, the dbserver follows the default mode of commit transaction. Thus when the first delete statement is over the manipulation would have been committed, thereby releasing the withheld resources like locks, pointers to shared memory and disk, memory etc. The subsequent delete statement will start afresh.

The example below marks the transactions exclusively. So, both the delete statements will be considered as a set.

dbaccess ustar_m - << !
database testdb;
begin;
delete from stmt_print_queue where
(request_id >= ${minnu1} and request_id <= ${maxnu1});
delete from stmt_print_queue where
(request_id >= ${minnu2} and request_id <= ${maxnu2});
commit;
!

Regards,
Shriyan

 

[wdellin]

Shriyan - Thank you very much for your help.

 

[vpshriyan]

Hi Dellin,

You are welcome.

Sorry for overlooking the SQL you have quoted as an example. Your database seems to be &quot;ustar_m&quot; and you are syntactically right when you have coded:

dbaccess ustar_m - << !
...
!

Regards,
Shriyan