Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004
    Posts
    76

    Unanswered: Check that Global Temporary Table Exists or Not

    System Information:

    OS: Windows XP Professional
    Database: DB2 UDB V8.1.3.132 Fixpack: 3

    Is there any way to find out whether any specific global temporary table alreay exists in the session or not?

    Thanks in advance. With Big Hope,

    Jai
    Dare to Dream!!!

  2. #2
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    Arrow

    You cannot list tables for schema session (I've tried)

    But just select the first row, and you'll know

    Cheers
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    The alternate solution :

    In the variable declaraion section
    DECALRE DGTT_FOUND SMALLINT DEFAULT 0 ;

    In the contine handler section, add an additional handler
    DECLARE CONTINUE HANDLER FOR 42710
    SET DGTT_FOUND=1 ;

    In the SP body
    DECLARE GLOBAL TEMPERORY TABLE SESSION.T1 ( I INT) ;
    IF (DGTT_FOUND=1) THEN
    ......
    ...
    ELSE
    .....
    .....
    END IF ;


    Then depending on the value of DGTT variable you can decide what you want to do ... For checking the existence of a dgtt, make sure you do not use the 'WITH REPLACE' option

    I have tried this on DB2 V8 on ZOS and works ... ( I had to code this continue handler because zos does not support the WITH REPLACE Option )... I don't see a reason why it will not work on LUW

    HTH

    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
  •