Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2007
    Posts
    50

    Unanswered: Check existence of temporary table

    How can I check using SQL (in a SQL stored procedure) if a temporary table exists?

  2. #2
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    are you refering GTT
    i suspect there would be any info about them on system level tables as they are valid only for session
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You can set up an appropriate CONDITION HANDLER for SQLSTATE 42704.

    Then you can just issue a select on the temp table.

    Andy

  4. #4
    Join Date
    Sep 2007
    Posts
    50
    Quote Originally Posted by rahul_s80
    are you refering GTT
    i suspect there would be any info about them on system level tables as they are valid only for session
    yes, it is a global temporary table. I have been browsing though the sys tables and couldn't find anything like it. However, maybe I missunderstand the concept of session but I do connect from a .NET client to call a stored procedure that creates a table and it seems that the table is persistent between calls.
    Is there any way that I can check if it exists already so that I don't try to redeclare it? (as that fails).
    Also, is there anything wrong in this pattern?
    Basically I declare a temp table to create some data, open a cursor with return on it (as I need to return that data to the caller) and therefore I cannot drop the table (as I have an open cursor on it). I thought it would dissapear in between calls but it doesn't.

  5. #5
    Join Date
    Sep 2007
    Posts
    50
    Quote Originally Posted by ARWinner
    You can set up an appropriate CONDITION HANDLER for SQLSTATE 42704.

    Then you can just issue a select on the temp table.

    Andy
    I actually need to know of its existence when I declare it, not when I select from it.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Your first post did not explain why you wanted to check if it existed.

    When you declare it, you can use the clause "WITH REPLACE' which will either create a new one if it does not exist or replace the definition if it exists. All Global Temp Tables will persist until it is either explicitly dropped (DROP TABLE SESSION.MyTempTable), or the connection is terminated.

    Andy

  7. #7
    Join Date
    Sep 2007
    Posts
    50
    that makes sense.
    Thanks

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Global temp tables are "declared", which indicates that information about those tables is not stored in the system catalog. Things are different with "created" temp tables (which DB2 LUW does not yet provide).
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by iskander
    I actually need to know of its existence when I declare it, not when I select from it.
    Still, you could first select from it, and it that *fails* all is fine (since the table does not exist) but if it *succeeds* you know that the declaration will fail.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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