Issues with Declare Global Temporary Tables

IBM's flagship relational database management system

Issues with Declare Global Temporary Tables

Postby upendra_water » Mon Dec 13, 2010 10:23 am

I am facing SQLCODE -905 with Declare Global Temporary Table (i.e. DGTT)
In a stored procedure we fetch data from multiple tables using a multifetch. This data is inserted into a DGTT and then it is passed to a different stored procedure for generating a report. We faced an issue when the number of records fetched were = 12597.
The stored procedure executed for a very long time and finally it failed with SQLCODE -905.

Has anyone faced this issue before? If yes,can you please suggest a work around?
Posts: 33
Joined: Wed Nov 25, 2009 10:58 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Issues with Declare Global Temporary Tables

Postby GuyC » Tue Dec 14, 2010 7:33 pm

yes, -905 is not so rare.
we solved it by reading the manual and taking the appropriate action:

Programmer Response: Determine why this SQL statement or stored procedure
took so long and take appropriate action. Consider simplifying the SQL
statement, restructuring tables and indexes, or contacting the
installation group responsible for maintaining the resource limit
specification tables.

in plain english : Tune your stored Procedure or set a higher limit.
I can explain it to you, but i can not understand it for you.
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Return to DB2


  • Related topics
    Last post