angelalpe
Warnings : 1
New User
Joined: 22 Sep 2005
Posts: 32
|
|
|
|
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 |
|
prav_06
Warnings : 1
Active User
Joined: 13 Dec 2005
Posts: 154
Location: The Netherlands
|
|
|
|
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. |
|
