Hi,

I'm looking for a way to make a stored procedure in DB2 9.1 create a temp table that is local only to that call of the stored procedure. Then, with the same sproc, I need to insert rows to it and then have another query select from the temp table.

Once the procedure is done, I'd like it to be like that table never existed so if I run the procedure again, its like its working on a brand new table.

------------

Is this possible?

The DECLARE GLOBAL TEMPORARY TABLE statement looks like its close, but it seems to me that it won't fulfill the requirement that once leave the procedure, its gone. I suppose I could drop the table when I'm done.

I've seen other examples where other DBMS' do this but I can't find the db2 equivalent and figured the experts here might have an idea.

Thanks in advance!

Looks like there are some options in the GLOBAL TEMPORARY TABLE for replacing the table.

Just curious, does anyone know if the global temp table that is available to your session will be affected by one of the same name on another session? In other words, if I declare Global temp table HIFF in one connection, then log in on another connection, will the 2nd connection clobber the one from the first if the global temp table has the same name?

I assume no because these are supposedly constrained to session.

As soon as someone creates a default temp tablespace for me I can test all this :-) .

Thanks again in advance!

Looks like this answers one of my questions:

Hi, I asked some questions about GTT's before.. check this previous post out.. I think it will answer all of your questions --

Global Temporary Tables

Thanks db2user. I was looking around and wasn't coming up with anything. I did run some tests on a db I created locally and got a lot of answers. Now I just need to figure out how I'm going to put this in a stored procedure :-) . I mean, I've never actually created one. I'll get there though.

If you need a temporary table for a single query there is also thhe "with table as" construct

An example:
Temp1 and Temp2 are created as temporary tables and they can be used in the select

with
temp1 as (select pub_name, max(c_price) as maxprice from book group by pub_name),
temp2 as (select pub_name, maxprice from temp1 where maxprice = (select max(maxprice) from temp1) )
select case when t.maxprice = t2.maxprice then '*' else '' END, k.pub_name, isbn, author, title, c_price
from book k, temp1 t , temp2 t2
where k.pub_name = t.pub_name and k.c_price = t.maxprice
order by k.pub_name;

Remember, you need to have a UserTemp tablespace declared.
Here is an example declaration for one:
declare global temporary table example(ssn character(9) not null, employer varchar(30) not null, empr_id bigint) in tsusertemp;
When you refer to it, the schema is always session, as in session.example.
You can explicitly drop it at the end of your procedure: drop table session.example;