Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2014
    Posts
    294

    Unanswered: problem with Global Temporary tables

    Hi,

    I have created a global temporary tables in my db2 stored procedure.Every thing is working fine & i can get the desired result.

    Problem is i can compile & run to get the result for the first time. If i tried to recompile for the second time, I am getting the below error;
    Identical error as SQLCODE=-601, SQLSTATE=42710.
    when after i disconnect from the database then only I can compile & execute my procedure.This is not working for the second time.

    According my programming knowledge temp tables will work till the job ends.on next compilation those will be refreshed.

    DECLARE GLOBAL TEMPORARY TABLE TEMP3(
    TABLE_ID INTEGER,
    TABLE_NAME VARCHAR(100),
    COL_NAME VARCHAR(1000)) on commit delete rows IN c_TBSPACE;
    DECLARE GLOBAL TEMPORARY TABLE TEMP4(
    DATABASE_NAME VARCHAR(100),
    TABLE_NAME VARCHAR(100),
    COL_NAME VARCHAR(1000)) on commit delete rows IN c_TBSPACE;

    what is happening here? & what should i do to be free from identical error on the next compilation.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Add "WITH REPLACE" on the end of the DECLARE GLOBAL TEMPORARY TABLE command.

    Andy

  3. #3
    Join Date
    Jul 2014
    Posts
    294
    Thanks Andy..

    It is working.

Tags for this Thread

Posting Permissions

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