Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004
    Posts
    13

    Unanswered: Does declared temp table exist?

    Does anyone know of a way to check if a declared temporary table exists in DB2 v8?

    We have a table declared like so...
    DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_MSG(... [columns] ..)
    WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED ON ROLLBACK PRESERVE ROWS;

    Since declared temp tables aren't in the catalog, I'm not sure how to do it.

    I want to create the table only if it doesn't exist (but keep the existing one if it's already there).

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    All I can think of is to try to query the table like "select count(*) from session.temp_msg". If you do not get an error then it is already defined.

    HTH

    Andy

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Or just create it, and ignore any error message about "table already exists".
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    If you do not want to replace, why are you declaring WITH REPLACE ????

    Exclude the clause and follow Peter Vanroose's suggestion

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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