Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2006
    Posts
    6

    Unanswered: Not enough "APP_CTL_HEAP_SZ" heap

    Hello all,

    I am getting the following error when try to run a simple statement like the following from unix command line.

    SELECT * FROM TEDCARG.CRAR_TCAT_DLY2 FETCH FIRST 100 ROWS ONLY

    SQL0973N Not enough storage is available in the "APP_CTL_HEAP_SZ" heap to process the statement. SQLSTATE=57011

    DBM cfg is as follows:
    Database monitor heap size (4KB) (MON_HEAP_SZ) = 51200
    Java Virtual Machine heap size (4KB) (JAVA_HEAP_SZ) = 512
    Audit buffer size (4KB) (AUDIT_BUF_SZ) = 0
    Size of instance shared memory (4KB) (INSTANCE_MEMORY) = AUTOMATIC
    Backup buffer default size (4KB) (BACKBUFSZ) = 1024
    Restore buffer default size (4KB) (RESTBUFSZ) = 1024
    Sort heap threshold (4KB) (SHEAPTHRES) = 524288
    Directory cache support (DIR_CACHE) = YES
    Application support layer heap size (4KB) (ASLHEAPSZ) = 15
    Max requester I/O block size (bytes) (RQRIOBLK) = 65535
    Query heap size (4KB) (QUERY_HEAP_SZ) = 4000

    DB CFG is as follows:
    Database heap (4KB) (DBHEAP) = 11184
    Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC
    Catalog cache size (4KB) (CATALOGCACHE_SZ) = (MAXAPPLS*4)
    Log buffer size (4KB) (LOGBUFSZ) = 256
    Utilities heap size (4KB) (UTIL_HEAP_SZ) = 40000
    Buffer pool size (pages) (BUFFPAGE) = 1000
    Extended storage segments size (4KB) (ESTORE_SEG_SZ) = 16000
    Number of extended storage segments (NUM_ESTORE_SEGS) = 0
    Max storage for lock list (4KB) (LOCKLIST) = 2500
    Max size of appl. group mem set (4KB) (APPGROUP_MEM_SZ) = 40000
    Percent of mem for appl. group heap (GROUPHEAP_RATIO) = 70
    Max appl. control heap size (4KB) (APP_CTL_HEAP_SZ) = 4096
    Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = (SHEAPTHRES)
    Sort list heap (4KB) (SORTHEAP) = 12800
    SQL statement heap (4KB) (STMTHEAP) = 32768
    Default application heap (4KB) (APPLHEAPSZ) = 8192
    Package cache size (4KB) (PCKCACHESZ) = (MAXAPPLS*8)
    Statistics heap size (4KB) (STAT_HEAP_SZ) = 8192



    It is ESE environment with 65 partitions, 8 P4 AIX, and each server has 8 partitions. Server 1 has 9 partitions.

    Any idea.

    Thanks
    Mis

  2. #2
    Join Date
    Sep 2003
    Posts
    237
    Whenever you get a SQL error please do the following db2 "? SQLERROR"; in your case db2 "? SQL0973N"; it clearly explains what you need to do.
    mota

  3. #3
    Join Date
    Sep 2006
    Posts
    6
    Thanks! For the tip, if IBM's help ??????? Anyways, I tried that and increased as suggested by the help. Some more background. The select * is going against the view, that comprises of 60 base tables of union all.

  4. #4
    Join Date
    Sep 2006
    Posts
    6
    Any help in this regards would be appreciated. Thanks in advance.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    If your database is partitioned or if you use intra-partition parallellism, try decreasing groupheap_ratio.

    Read here for more information: http://www-1.ibm.com/support/docview...id=swg21179841

Posting Permissions

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