deletion of records as smaller chunks in DB2???

ramakanthboga · #1 (**permalink**) 06-30-06, 10:32

I have a table consists of 10000 records. i want to delete these records, if i tried to delete those records at a time, database fail out of log space.
So, I want to delete these 10000 records as smaller chunks.
i am using DB2.

Can anyone help me to delete the records in smaller chunks?

thanks,
ramakanth

JAYANTA_DATTA · #2 (**permalink**) 06-30-06, 11:23

Please use WHERE clause in your DELETE statement.

Thank You,

Peter.Vanroose · #3 (**permalink**) 06-30-06, 13:32

Quote:

Originally Posted by ramakanthboga

... if i tried to delete those records at a time, database fail out of log space.

Do you mean lock space?
The following could work in this case:

Code:

LOCK TABLE tablename IN EXCLUSIVE MODE ;
DELETE FROM tablename ;
COMMIT ;

or something similar.
The essential idea is to avoid row-at-a-time locking by forcing a table lock before starting the delete process.

ramakanthboga · #4 (**permalink**) 07-01-06, 10:03

hi JAYANTA_DATTA,
thanks for the reply, if i use the where clause it can able to delete the records certain amount of records. i need something which can able to delete the records in loop(as a smaller chunks, say 100records) till the end.
if you could tell me the syntax for this then it would be great.
thanks,
ramakanth boga

hi Peter.Vanroose,
i am sorry to tell you its not lock space. its log space i.e database fail out of trasaction log space.
i need something which can able to delete the records in loop(as a smaller chunks, say 100records) till the end.
thanks
ramakanth boga

Peter.Vanroose · #5 (**permalink**) 07-01-06, 10:26

In that case you can write a loop around a cursor fetch, with a COMMIT every 100 rows, something like:

Code:

DECLARE c CURSOR FOR
SELECT 1 FROM my_table WHERE [*condition*] FOR UPDATE ;
OPEN c

and then in a loop:

Code:

FETCH c;
DELETE FROM my_table WHERE CURRENT OF c

and issue a COMMIT every 100th iteration.

Alternatively, in DB2 version 8 and beyond, you may use multi-row fetch and positioned delete, with a commit after every delete:

Code:

FETCH NEXT ROWSET FROM c FOR 100 ROWS ;
DELETE FROM my_table WHERE CURRENT OF c ;
COMMIT WORK

ramakanthboga · #6 (**permalink**) 07-02-06, 01:56

Peter.Vanroose,
thanks for the response. i'll try to do this, if any problems comes i'll c u.

ramakanth

Internet Services

Web Development

Digital Marketing

Consumer Tech