Results 1 to 8 of 8
  1. #1
    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
    ;

  2. #2
    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

  3. #3
    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
    ;

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,446
    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;
    ...

  5. #5
    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

  6. #6
    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;

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,446
    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.

  8. #8
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •