Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2006
    Posts
    4

    Unanswered: Sqlcode: -973, Sqlstate: 57011, Sqlerrmc: Mon_heap_sz

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Have a look at the User Response for message SQL0973
    Code:
    $ db2 "? sql973"
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Dec 2006
    Posts
    4
    Quote Originally Posted by ARWinner
    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
    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

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  6. #6
    Join Date
    Dec 2006
    Posts
    4
    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


    Quote Originally Posted by ARWinner
    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

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  8. #8
    Join Date
    Dec 2006
    Posts
    25

    mon_heap_sz

    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.

  9. #9
    Join Date
    Dec 2006
    Posts
    4
    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

    Quote Originally Posted by savethytrees
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •