Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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 OO COBOL compile error Virendra Shambharkar COBOL Programming 3 Tue Jan 10, 2017 6:05 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts Is the 'prompt' data for the DSLIST p... Willy Jensen TSO/ISPF 2 Tue Dec 06, 2016 4:38 am
No new posts S922 Error yuvan ABENDS & Debugging 3 Fri Dec 02, 2016 6:58 pm
No new posts Invoke Webservice Fails with DFHPI100... divated CICS 2 Thu Nov 24, 2016 5:57 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us