Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2008
    Posts
    23

    Thumbs up Unanswered: DB2 Temporary tables inside a stored procedure (only)?

    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!

  2. #2
    Join Date
    Oct 2008
    Posts
    23
    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!

  3. #3
    Join Date
    Oct 2008
    Posts
    23
    Looks like this answers one of my questions:

    If a DECLARE GLOBAL TEMPORARY TABLE statement is specified within the SQL procedure compound statement (defined by BEGIN and END), the scope of the declared global temporary table is the connection, not just the compound statement, and the table is known outside of the compound statement. The table is not implicitly dropped at the END of the compound statement. A declared global temporary table cannot be defined multiple times by the same name in other compound statements in that session, unless the table has been explicitly dropped.

  4. #4
    Join Date
    Dec 2002
    Posts
    123
    Hi, I asked some questions about GTT's before.. check this previous post out.. I think it will answer all of your questions --

    http://www.dbforums.com/showthread.p...hlight=db2user

  5. #5
    Join Date
    Oct 2008
    Posts
    23
    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.

  6. #6
    Join Date
    Jun 2009
    Posts
    3
    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;

  7. #7
    Join Date
    Dec 2008
    Posts
    76
    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;
    RD

Posting Permissions

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