Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2004
    Posts
    38

    Unanswered: APP_CTL_HEAP_SZ Utilization

    Using db2mtrk I'm able to view the details on database level memory as below in my environment (AIX 5.3, DB2 UDB V8.1 FP 7, Partitioned database having 4 nodes)

    ==================================================
    /home/vij] > db2mtrk -i -p -w -d -v
    Tracking Memory on: 2006/02/25 at 20:29:46

    Memory for instance
    Database Monitor Heap has watermark of 180224 bytes
    Other Memory has watermark of 8323072 bytes
    FCMBP Heap has watermark of 17432576 bytes
    Total: 25935872 bytes

    Memory for database: CARD
    Backup/Restore/Util Heap has watermark of 9699328 bytes
    Package Cache has watermark of 507904 bytes
    Catalog Cache Heap has watermark of 196608 bytes
    Buffer Pool Heap has watermark of 211517440 bytes
    Buffer Pool Heap has watermark of 247808000 bytes
    Buffer Pool Heap has watermark of 4341760 bytes
    Buffer Pool Heap has watermark of 655360 bytes
    Buffer Pool Heap has watermark of 393216 bytes
    Buffer Pool Heap has watermark of 262144 bytes
    Buffer Pool Heap has watermark of 196608 bytes
    Lock Manager Heap has watermark of 491520 bytes
    Database Heap has watermark of 3768320 bytes
    Other Memory has watermark of 32768 bytes
    Application Control Heap has watermark of 16384 bytes
    Application Control Heap has watermark of 16384 bytes
    Application Control Heap has watermark of 638976 bytes
    Application Group Shared Heap has watermark of 57344000 bytes
    Total: 537886720 bytes

    Memory for agent 1937486
    Application Heap has watermark of 147456 bytes
    Total: 147456 bytes

    Memory for agent 757968
    Application Heap has watermark of 65536 bytes
    Total: 65536 bytes

    Memory for agent 1519790
    Application Heap has watermark of 65536 bytes
    Total: 65536 bytes

    Memory for agent 1368068
    Application Heap has watermark of 278528 bytes
    Total: 278528 bytes

    Memory for agent 868528
    Application Heap has watermark of 65536 bytes
    Total: 65536 bytes

    Memory for agent 962608
    Application Heap has watermark of 65536 bytes
    Total: 65536 bytes

    Memory for agent 1343590
    Application Heap has watermark of 65536 bytes
    Total: 65536 bytes

    Memory for agent 1667184
    Application Heap has watermark of 65536 bytes
    Total: 65536 bytes
    ==================================================

    Recently I updated the APP_CTL_HEAP_SZ to 1024 in all the 4 partitions as below
    db2_all "db2 update db cfg for vijdb using APP_CTL_HEAP_SZ 1024" as I got the error SQL0973N Not enough storage is available in the APP_CTL_HEAP_SZ heap to process the statement. SQLSTATE=57011 when I called a stored procedure. So I just doubled its value.

    Now my questions
    1. How can I find out the the value of of APP_CTL_HEAP_SZ utilized by my stored procedure ?

    2. Is APP_CTL_HEAP_SZ is depended on the SQL statement length or the volume of data that stored procedure is handling?

    3. Is there is a procedure to find out the value of APP_CTL_HEAP_SZ (instead of just doubling the value when the default value fails) ?

    4. In the output which I pasted above has 3 entries for APP_CTL_HEAP_SZ
    Application Control Heap has watermark of 16384 bytes
    Application Control Heap has watermark of 16384 bytes
    Application Control Heap has watermark of 638976 bytes
    But how can I find out that which is associated with each partition?

    5. I have 4 partitions and APP_CTL_HEAP_SZ is same in all partitions but only 3 entries are listed above and it is not same. Whats the reason behind this ?

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    It is possible that the partition that needs more heap is the partition 0, which is the one the coordinates the answer sets from all the other partitions into a single answer back to the requesting application.

    There does not seem to be any easy way to calculate the amont of heap memory in advance for this parm, so I would take the advice of the manual and double it each time you have a problem. 1024 (pages) is only 4MB of memory.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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