Temp part of my data area is increasing

[Former Member]

Hello,

I'm using MaxDb 7.6.03.07 on WinXp HE SP2.

My service is calling stored procedure which is returning cursor (mostly empty).

In DbManagerGUI I see that my "Data Area" grows very fast.

When I'm starting my service it is looking like:

Total: 4 095,98 MB Perm: 4,34 MB Temp: 0,13 MB ....

But when my service is running 1day:

Total: 4 095,98 MB Perm: 4,34 MB Temp: 170,56 MB ....

When the service was runing some days there was 2GB temp part of data.

When I stop my service, kernel.exe process gets 100% from my CPU and after some hours my Temp part goes back down to 0.13MB.

What I'm doing wrong ? Can you help me, please ?

Dusan

d.kolesar@gmail.com

[Former Member]

Hi,

you wrote that the number of userresults is increasing.

Then it is clear, that the 1. problem occurs: you do not close your cursors.

--> You have to close them explicitly with the SQL statement close <cursorname>.

And you checked the other types and did not see anything with temp inv?

I am not really sure how it was written, maybe tempinv written together. If this or something similar (with temp and inv in the type) cannot be found, no change to the installation parameter will help.

Elke

[Former Member]

Hello,

I wrote that it is case 2, because I'm closing all my named cursors explicitly when I do not need them more. But maybe I'm doing somthing wrong.

Here is fragmet of my code :

[code]

CREATE DBPROC GET_RADIO_EVENT (IN ServerId Integer, IN SrvRetryCnt Integer,

IN SecDelay Integer) RETURNS CURSOR AS

VAR

id Integer;

outId Integer;

retryCnt Integer;

BEGIN

/* create result cursor */

$CURSOR = 'GETRADIOEVENT_CURSOR';

/* find radio event with max priority */

DECLARE RADIOEVENT_CUR CURSOR FOR

SELECT ID, OUT_ID, RETRYCNT FROM ADMIN.RADIO_EVENT

WHERE SERVER_ID IN (-1, :ServerId)

AND RETRYTSTAMP <= Timestamp

ORDER BY PRIORITY DESC, ID ASC;

IF ($rc = 100) THEN

BEGIN

CLOSE RADIOEVENT_CUR;

/* result cursor - empty */

DECLARE :$CURSOR CURSOR FOR

SELECT * FROM ADMIN.RADIO_OUT WHERE False;

RETURN;

END;

FETCH FIRST RADIOEVENT_CUR INTO :id, :outId, :retryCnt;

CLOSE RADIOEVENT_CUR;

...

DECLARE :$CURSOR CURSOR FOR

SELECT * FROM ADMIN.RADIO_OUT WHERE ...;

END;

[/code]

Or should I close my output cursor too ? How ?

Thank you for support.

[Former Member]

Hi,

there are two known possibilities:

1. you forgot to close the cursors and you should include a CLOSE <cursorname> into your application after having fetched this cursor

2. you re-found the bug which is corrected in 7.6.03.09 (not yet available):

internally prepared temp-inv-files are not dropped after usage.

To find out which problem occurs on your site, you should check the systemview files and check the names/types of the files returned. The number of userresult (1. problem) should be slow, the number of temp-inv (2. problem) should be 0 if the cursors are closed.

In case of the 2. problem, you may try to set installation parameter OPTIM_MAX_MERGE to 0. That should help.

Elke

[Former Member]

Hello,

when I do

SELECT * FROM files where SESSIONID = <ServiceSessionId>

there are rows with type "USERRESULT" and number of this rows is increasing.

There are no rows with type "TEMP-INV". So it should be 2nd case, if I understood.

Should I set OPTIM_MAX_MERGE to 0 ?

Thank you for support.

Dusan