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

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.

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.

Any help in this regards would be appreciated. Thanks in advance.

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