Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2010
    Posts
    10

    Unanswered: SQLCODE -204 due to Declare Global Temporary Table

    Hi,

    In my project a Declare Global Temporary Table is used in a stored procedure.
    This DGTT is created in procedure "A" and used in procedures "B" and "C". These 3 SPs are called from dotNET application one by one i.e. first A then B then C.
    Procedures "A" and "B" complete successfully but "C" fails with SQLCODE -204 because DGTT is getting deleted.
    I have 2 questions:
    1) Why DGTT is getting dropped.
    2) Is there any way to prevent it from getting dropped?

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by upendra_water View Post
    This DGTT is created in procedure "A"
    A declared temporary table is never created - that's why it is called "declared". It needs to be declared, subsequently, in every routine that references it.

  3. #3
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Lightbulb

    Quote Originally Posted by upendra_water View Post
    Hi,

    In my project a Declare Global Temporary Table is used in a stored procedure.
    This DGTT is created in procedure "A" and used in procedures "B" and "C". These 3 SPs are called from dotNET application one by one i.e. first A then B then C.
    Procedures "A" and "B" complete successfully but "C" fails with SQLCODE -204 because DGTT is getting deleted.
    I have 2 questions:
    1) Why DGTT is getting dropped.
    2) Is there any way to prevent it from getting dropped?
    I beleive somewhere COMMIT happened.

    Try to use:
    Code:
    Declare global temporary table DGTT (.....)
    on commit preserve rows;
    Lenny

  4. #4
    Join Date
    Dec 2010
    Posts
    10
    Hi,
    We have already used ON COMMIT PRESERVE ROWS option while declaring TEMP table. When the number of rows in TEMP table are very few, all stored procedures execute successfully. The problem occurs only when there are thousands of rows in TEMP table. In 1 case we found that there were 12000 rows in TEMP table and the stored procedure failed i.e. "A" and "B" execute successfully but "C" fails.

  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by upendra_water View Post
    Hi,
    We have already used ON COMMIT PRESERVE ROWS option while declaring TEMP table. When the number of rows in TEMP table are very few, all stored procedures execute successfully. The problem occurs only when there are thousands of rows in TEMP table. In 1 case we found that there were 12000 rows in TEMP table and the stored procedure failed i.e. "A" and "B" execute successfully but "C" fails.
    Because of it's happen when global table is huge, maybe you have
    sqlcode = -904 (resourse unavailabe) which you do not catch, and than sqlcode = -204.

    Lenny

  6. #6
    Join Date
    Dec 2010
    Posts
    10
    Hi,

    We are getting SQLCODE -204 only. Please see screenshot from DB2 logs.
    Attached Thumbnails Attached Thumbnails SQLCODE -204.JPG  

  7. #7
    Join Date
    Dec 2010
    Posts
    10
    Attaching 1 more screenshot which shows the exact error.
    It shows that the temp table is undefined.
    Attached Thumbnails Attached Thumbnails SQLCODE -204.JPG  

  8. #8
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by upendra_water View Post
    Attaching 1 more screenshot which shows the exact error.
    It shows that the temp table is undefined.
    Undefined after destroyed in SP "B"....

  9. #9
    Join Date
    Dec 2010
    Posts
    10
    Hi Lenny,
    That's what is puzzling me. Why this table is getting dropped in third SP?
    When the number of rows are less in temp table all three SPs execute successfully and we get proper reports from front end. Only when the number of rows are huge we face this error.

Posting Permissions

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