I have a simple procedure of the following format:
create procedure insert();
Insert into (table.columns)
select * from table
where day >= 1
and day < 2
When I run this procedure in DBACCESS, using execute procedure insert(), and then monitor shared memory, I find that the blocks used column keeps increasing until the procedure completes. When it completes these blocks are not being freed until I exit DBACCESS.
If I run the insert...select in DBACCESS on its own, I notice the same increase in blocks used but when the query completes the memory is freed.
Blocks of resident shared memory are only freed during checkpoints.
What do you use to monitor the pool?
Blocks of virtual shared memory are freed whenever the server want.
For normal sql it 's usually freed after the query finishes.
For SPL, another pool is used: ralloc. check with onstat -g ses.
It is not freed immediately, because it could be reused. That is the whole idea of a stored procedure. You write the code once, and it can be executed several times.
Within the application I am using there is data for over 30 days.
The procedure is actually a function to which I pass in the start and end day to.
When the function is execute it is automated to do a day at a time.
I use onstat -g seg to monitor the memory usage and find that as it goes from one day to the next the blocksused column keeps growing and eventually a new segment is added.
This keeps happening and eventually Informix is running out of Virtual Shared memory.
I thought that when the function processed a day the blocksused memory would be re-allocated to blocksfree and then the same segment of memory would be used for the next day and so on.
Instead it just seems to be eating up virtual shared memory.