i have a stored procedure A in which i create a temporary tables...say X
and has the following logic
procedure A

create temp table X;
insert into X (select from sometable where some criteria);

while (select count(*) from X ) > 0
do

some processing

delete one row from X

end while

i need the table to store some values and then run a while loop deleting a row each time.....

now hundreds of users will be accessing mt procedure at the same time so i cant use a global temp table......then how to go about it.....i need something on the lines of a local temporary table......

Global temporary tables are not visible to all users. Just the user that DECLARE them.

Andy

ok....got that after lot of peeking around......but it seems suppose a user fires procedure A then the temporary table will be created but the same user could not fire the same procedure in the same seesion........

basically what i found was have to open a seperated instance of command editor each time to fire the same procedure, the same instance would always give the error that the temporary table already exists....

If you add the clause "WITH REPLACE" on the "DECLARE GLOBAL TEMPORARY TABLE", DB2 will replace the old definition with the new. Alternately, you can issue a "DROP TABLE session.MyGlobalTemporaryTable" type command when you are finished with the table.

Andy

Or you add a condition handler that intercepts and ignores the error that DB2 raises on the second DECLARE statement...