Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Error -904 in a stored procedure with global temporary table

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
angelalpe
Warnings : 1

New User


Joined: 22 Sep 2005
Posts: 32

PostPosted: Fri Feb 02, 2007 6:01 pm    Post subject: Error -904 in a stored procedure with global temporary table
Reply with quote

I have a program batch that calls ?many times? to a procedure stored DB2.

In this procedure a temporary table is declared (DECLARE GLOBAL TEMPORARY TABLE SESSION.TABLA) where are inserted registries and in the end a cursor with the registries of the temporary table is given back. Before declaring the temporary table always drop is executed table. The registries that are inserted in the temporary table are not many, between 100 and 200 and the size of each registry is of 400 bytes.

The procedure does not have commit because I need that it is the program batch the one that does commit.

The problem is that after the program batch calls 300 times to the stored procedure, the procedure gives to an error sqlcode=-904 and sqlstate = 57011 when executing the sentence DECLARE GLOBAL TEMPORARY TABLE

Reason why I have seen, the error can be because the temporary storage or the resource of the data base is not available.

It seems that the problem is that whenever the procedure finishes not it releases space (memory) and since I make many calls to the procedure, the occupied memory is growing

Is some way of which when it finishes my procedure frees memory or the resources are freed that use?

Is necessary to increase the size to declare temporary tables?

Which can be my problem and as I can solve it?

Thank you very much and greetings
Back to top
View user's profile Send private message

prav_06
Warnings : 1

Active User


Joined: 13 Dec 2005
Posts: 154
Location: The Netherlands

PostPosted: Sat Feb 03, 2007 9:23 pm    Post subject:
Reply with quote

angelalpe,

Quote:
Reason why I have seen, the error can be because the temporary storage or the resource of the data base is not available.


U have confined to the rite point, -904 can also occur when a commit is not given especially in store proc., look the issue in this angle, and let us know reg. this

And one more way to look at your req, u are creating a temp.table when ever u r callin the SP and for each and every execution of the SP a temp.table is created this may also increse the memory size of your load module , try to look at the column called STAYRESIDENT inside SYSIBM.SYSROUTINES for your SP, this option can take values 'Y' or 'N', this would release the Load module of your SP once the SP has executed. one more soln. that can be is to drop the created Temp. table at the fag end of yoor SP.

Please Correct me if I am Wrong.

Cheer's,

Thamilzan.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts File Tailoring -- Temporary dataset i... samzee71 CLIST & REXX 3 Fri Dec 01, 2017 3:12 am
No new posts -502 Error - Cursor already open anand jeyapaul DB2 7 Sun Oct 29, 2017 4:22 am
No new posts IEW2456E error when link-editing a C ... Senthilraj JCL & VSAM 0 Fri Oct 13, 2017 3:12 pm
No new posts Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Table(Unicode(Graphic) table) loading... muralikrishnan_new DB2 0 Thu Oct 05, 2017 5:10 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us