Hello,

From same time I have such a problem like in subject.
Parameters of the system are:
DB2 - 8.1.5
AIX - 5.2 ML 5

There are two application in java which are using database where is a problem. One is inserting records and other which is processing/updating.
After some normal work of those applications there is one moment when this error occure - let say after one or two weeks. From that moment only restarting database instance is solving this error.
I'm using JDBC interface in the both application to connect to database and it call some stored procedures on the database.

Could give me some hints what could be the reason of this problem or when I should to look for in the code/database settings.

Thank you for any help in advance
Rafal

The error you are getting:

SQL0973N Not enough storage is available in the "<heap-name>"
heap to process the statement.

Explanation:

All available memory for this heap has been used. The statement
cannot be processed.

User Response:

Terminate the application on receipt of this message (SQLCODE).
Modify the "<heap-name>" configuration parameter to
increase the heap size.

For example, to update a database configuration parameter, issue
the following command:


db2 update db cfg
for "<db-name>"
using "<heap-name>" "<heap-size>"

To view a list of the database configuration parameters, use the
GET DATABASE CONFIGURATION command.

To update a database manager configuration parameter, issue the
following command:


db2 update dbm cfg
for "<db-name>"
using "<heap-name>" "<heap-size>"

To view a list of the database manager configuration parameters,
use the GET DATABASE MANAGER CONFIGURATION command.

For application group shared heap size, the following three
database configuration parameters control its size and usage:
APPGROUP_MEM_SZ, GROUPHEAP_RATIO, and APP_CTL_HEAP_SZ. The
number of applications in one application group is calculated by:
APPGROUP_MEM_SZ / APP_CTL_HEAP_SZ. The application group shared
heap size is calculated by: APPGROUP_MEM_SZ * GROUPHEAP_RATIO /
100.

sqlcode : -973

sqlstate : 57011


-----------------------

It looks like you need to increase MON_HEAP_SZ.

Andy

Have a look at the User Response for message SQL0973
The monitor heap is running full in your case. Do you have some event monitors defined? You can either try to increase the heap size, or you delete monitors if possible.

Also, have you run the DB2 Configuration Advisor? It should configure your DB2 instance to give you a good base line.

Dear Andy,

thank you for your quick answer.
I know this desription, but problem which I'm afraid is that this MON_HEAP should not grow during the time - should be relased after commit - as I understand. Problem is that it is not.....

Rafal

The monitor heap is allocated at db2start and deallocated at db2stop. It is an instance wide parameter. So lots of monitoring or activity can cause it to be exhausted. It looks like you are at FP5. At FP7 and later DB2 can allocate more space (if available) when needed. You will need to increase the parameter and/or apply FP7 or later.

Andy

Dear Andy,

unfortunately we are on the FP5, and we cannot migrate into FP& for some reason
I double value of that heap size, bu anyway there is still same problem.
I find out somewhere information, that it could be the problem with commiting data...
Do you know actually what this monitor heap size is for?

Best regards
Rafal

The MON_HEAP_SZ reserves memory for DB2 to run montioring activities (snapshots, event monitors, etc). The size needed depends on a lot of factors. The number of people monitoring, the number of applications, the size of statements, locks, Units of work, etc. etc. It sounds like you need to have a particularly large MON_HEAP_SZ. You will just have to keep increasing it in increments to get it right. Either that or convince the powers that be to upgrade to a newer FP. The current FP level is 14, which means you are 9 behind. I checked when FP 5 came out, it was in February of 2004. This means you are 3 years behind.

Andy

Are you using any kind of monitoring tool like Foglight or BMC Patrol? If yes then find out what all agents are running. If you dont require any particular agent then disable that.
Find out how many jobs are running which take snapshots against the database. If its possible then reduce their number or try not to run them simultaneaously.
That memory is used just for monitoring the database using snaphot and event monitors. Even the monitoring tools like BMC patrol take snapshot against the database. So try to reduce the number of tools that are talking snapshots. Or else you will have to keep increasing the value until it is sufficient.

Hi,

yes we are using: Foglight. Thank you very much for your advices. I understand now, that it is not the application problems but rather administrator's setting in the db2.

Best regards
Rafal