Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2011
    Posts
    24

    Unanswered: Check if global temporary table exists?

    System info:
    DB2 for Z/OS version 10.1

    I have a stored proc in which a declare global temporary table is used.
    This SP would be called repeatedly from the application with the same session id. Therefore, an error "table already exists" thrown from second call onwards.
    Not able to drop the table at the end of SP also as a dynamic cursor still references it.

    The WITH REPLACE option for table declaration did not work in Z/OS.

    Looks like error handlers are the way to go - to simply ignore the error and proceed to next statement.
    Found this post related to this but couldn't get it to work.
    http://www.dbforums.com/db2/1006759-...xists-not.html

    Can someone provide the exact statements that can check if a session.table exists and if so, handle error and proceed to next statement.
    (There is lot of syntax in google but getting it to work is tough in this case.)

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Do you have a "declared" or a "created" temp table? If it is a created global temporary table, you can query the DB2 catalog tables (SYSIBM.SYSTABLES) for it.

    If it is a declared table, the post you referenced demonstrates basically how the SP declares the table and handles the error situation with an exception handler. If you say "couldn't get it to work"... can you tell us exactly what you tried and what the error message was?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Nov 2011
    Posts
    24
    Thanks for the reply Stolze.
    Its a declare temp table that I'm using.
    I should have been clearer.
    I did get error when trying the handler given in that post - but that's probably because of incorrect syntax I used for the handlers. Iam new to DB2.
    So Iam asking is anyone can give an example with the exception statements to use (and where to place them) in the context of a procedure.

    Sample SP:
    My intention is to skip any error encountered for table declartion and proceed with insert.

    CREATE PROCEDURE sptest (out a decimal(19,0) )
    VERSION V1
    ISOLATION LEVEL CS
    RESULT SETS 1
    LANGUAGE SQL

    P1: BEGIN
    declare DGTT_FOUND int;

    DECLARE GLOBAL TEMPORARY TABLE LIST
    (KEYno DECIMAL(19 , 0) NOT NULL );


    insert into session.LIST
    values ('5');

    select KEYNO into a from session.list;
    ---DROP TABLE SESSION.LIST;


    END P1

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    What's the error message you got? From that you'll see the SQLSTATE and declare a continue handler for it. For example, if the SQLSTATE of the error would be 42710, this declaration will make sure that DB2 catches the error, sets the variable and then goes on as if nothing happened.
    Code:
    DECLARE CONTINUE HANDLER FOR '42710' SET DGTT_FOUND=1;
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Nov 2011
    Posts
    24
    Thanks Stolze! That worked.

    DECLARE CONTINUE HANDLER FOR SQLSTATE '42710' SET DGTT_FOUND=1;

    DECLARE GLOBAL TEMPORARY TABLE LIST
    (KEYno DECIMAL(19 , 0) NOT NULL );

    (Had to add SQLSTATE keyword in DB2 z/OS for it to work.Otherwise it showed a syntax error under FOR.)

Posting Permissions

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