Results 1 to 5 of 5
  1. #1
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221

    Unanswered: DB2 Temporary Tables

    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......
    Last edited by nick.ncs; 07-16-07 at 05:20.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Global temporary tables are not visible to all users. Just the user that DECLARE them.

    Andy

  3. #3
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    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....

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Or you add a condition handler that intercepts and ignores the error that DB2 raises on the second DECLARE statement...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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