If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > While loop in Procedure for a batch delete

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Jan 2004
Posts: 70
While loop in Procedure for a batch delete

Is there any way something like this would work?


Code:
create procedure myschema.myproc(in invar integer)
	modifies sql data
	not deterministic
	language sql
begin
	declare rowcount integer; 
	set rowcount = select count(*) from myschema.mytable where var = invar;
	while ( rowcount > 0 ) do
		delete
			from myschema.mytable
				where id_col in
				(select id_col
					from myschema.mytable
					where var = invar
					fetch first 250000 rows only
				);
		commit;
		set rowcount = select count(*) from myschema.mytable where var = invar;
	end while;
end
;
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: May 2003
Location: USA
Posts: 5,734
What you should do is:

1. Delecare a cursor with the appropriate where clause (use hold option so it will not close if you do commits)

2. Open the cursor

3. In a while loop:
a. fetch the next row into variables
b. If return code = 100 (no more rows in cursor) exit the loop
c. If you have reached the maximum number of rows you want to update, then exit the loop
d. If you want to delete the row, then delete where current of cursor
e. Every 1000 rows do a commit
4. Commit one more time at the end.
5. Close the cursor.
__________________
M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Jan 2004
Posts: 70
Is this correct? Any chance that'd cause an infinite loop?

Code:
create procedure myschema.myproc (in in_col1,
                                  in in_commitcount integer)
    modifies sql data
    not deterministic
    language sql

begin

	declare sqlcode integer default 0;
	declare var_key bigint;
	declare var_col1 integer;
	declare deletedcount integer;
	
	
	declare mycursor cursor with hold for
	 select key,col1
	 from myschema.mytable
	 where col1 = in_col1;
	 
	open mycursor;
	  
	set deletedcount = 0; 
	while ( sqlcode = 0 ) do
		fetch mycursor into var_key, var_col1;
		if sqlcode <> 100 then
			delete from myschema.mytable where current of mycursor;
			set deletedcount = deletedcount + 1; 
			if deletedcount = in_commitcount then
				commit;
				set deletedcount = 0; 
			end if;
		end if;
	end while;
	
	commit;
	
	close mycursor;

end
;
Reply With Quote
  #4 (permalink)  
Old
:-)
 
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 5,399
Quote:
Originally Posted by JamesAvery22
Is this correct? Any chance that'd cause an infinite loop?
There's actually a pretty good chance of that. SQLCODE is set by each statement. You should save it to a local variable after each statement in whose status you're interested.

Code:
...
declare sqlcode int;
declare mysqlcode int;
...
open ...
set mysqlcode = sqlcode;
...
while (mysqlcode = 0) do
   fetch ...
   set mysqlcode = sqlcode;
   ...
end while;
...
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Jan 2004
Posts: 70
Quote:
Originally Posted by n_i
There's actually a pretty good chance of that. SQLCODE is set by each statement. You should save it to a local variable after each statement in whose status you're interested.

Code:
...
declare sqlcode int;
declare mysqlcode int;
...
open ...
set mysqlcode = sqlcode;
...
while (mysqlcode = 0) do
   fetch ...
   set mysqlcode = sqlcode;
   ...
end while;
...
Thought I remembered hearing that somewhere =) Thanks
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Jan 2004
Posts: 70
One more question,
One of the tables this query will be running on has referential integrity turned on and deleting from it is very slow. It has around 8mil rows and it takes longer to delete it than another table with 30mil. Will this slow it down any more?

As apposed to running this a bunch of times
Code:
delete
	from myschema.mytable
		where id_col in
		(select id_col
			from myschema.mytable
			where var = invar
			fetch first 250000 rows only
		);
commit;
Reply With Quote
  #7 (permalink)  
Old
:-)
 
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 5,399
I don't think this will affect performance. Make sure there are indexes on the columns involved in the foreign key relationship on all tables. You may want to try dropping the FK temporarily and deleting child rows yourself.
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Dec 2005
Posts: 273
also consider using the
LOCK TABLE myschema.mytable IN EXCLUSIVE MODE

for both (the main and the dependent) tables
to avoid a lot of page locks
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On