Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Posts
    4

    Question Unanswered: SQL0954C Not enough storage is available in the application heap to process

    Hi,

    I was trying to run a SQL statment with a size of 30kb in DB2. Then I got an error message:"SQL0101N The statement is too long or too complex. SQLSTATE=54001". Later, I increased both the size of STMTHEAP and APPLHEAPSZ to 59392. Then, I got another error message: "SQL0954C Not enough storage is available in the application heap to process the statement SQLSTATE=57011". Since I already increase the application heap to the maximum size, why DB2 still reports not enough storage for application heap?

    Can anyone give me a hint how to deal with such a problem? (My SQL statment is just a SELECT statment, no DELETE, UPDATE. )

    Thanks a lot!

  2. #2
    Join Date
    Jul 2003
    Location
    Austin, TX, USA
    Posts
    278

    Please post

    Hi,

    Can you please post your db and dbm config parameters as well as the sql statement.

    Please kindly note that it is a must to mention you os,db version and fixpacks with every post, all members would appreciate if you follow the same.

    Thanks

    Nitin.
    HTH

    Nitin

    Ask the experienced rather than the learned

  3. #3
    Join Date
    Sep 2003
    Posts
    218
    Interesting problem, even after allocating 60000 (4kb pages) ~ 235 MB of space, you are receiving this error!! Generally a value of 1024 (4kb pages) = 4mb should be sufficient.

    Anyway, could you please provide a snapshot of current db parameters. This will help in isolating the issue.

    Thanks.

  4. #4
    Join Date
    Jan 2003
    Posts
    1,605

    Re: SQL0954C Not enough storage is available in the application heap to process

    Hi,

    I have worked for one customer where he had problem of running SQL more then 15 hours and then got message from DB2: "The statement is too long or too complex".

    The problem was poorly written SQL. After I have rewritten the SQL, it executed in 2 minutes. Please check if DB2 is using some temporaly tables. Try to rewrite SQL.

    Can you post the SQL witch makes problems?

    Hope this helps,
    Grofaty

  5. #5
    Join Date
    Jan 2004
    Posts
    4

    Smile The systems conf and the SQL statment

    Hi, Thanks for the repsones!

    The SQL statment that I attached here is automatically generated by my program. Since in DB2, the max-length for SQL stmtment is 64kb, to reduce the size of the generated SQL plan, VIEWs are used in the SQL statment.

    I already increase the size of STMTHEAP, APPLHEAPSZ and PCKCACHESZ, but still not worked.

    The following is the systems' information:

    ============================
    OS: SunOS softbase.math 5.8 Generic_108528-19 sun4u sparc SUNW,Ultra-4

    ==============================
    DB2 VERSION:
    DB21085I Instance "db2_in01" uses "32" bits and DB2 code release "SQL08010"
    with level identifier "01010106".
    Informational tokens are "DB2 v8.1.0.0", "s021023", "", and FixPak "0".

    =======================================
    Database Configuration for Database david

    Database configuration release level = 0x0a00
    Database release level = 0x0a00

    Database territory = US
    Database code page = 819
    Database code set = ISO8859-1
    Database country/region code = 1

    Dynamic SQL Query management (DYN_QUERY_MGMT) = DISABLE

    Discovery support for this database (DISCOVER_DB) = ENABLE

    Default query optimization class (DFT_QUERYOPT) = 5
    Degree of parallelism (DFT_DEGREE) = 1
    Continue upon arithmetic exceptions (DFT_SQLMATHWARN) = NO
    Default refresh age (DFT_REFRESH_AGE) = 0
    Number of frequent values retained (NUM_FREQVALUES) = 10
    Number of quantiles retained (NUM_QUANTILES) = 20

    Backup pending = NO

    Database is consistent = NO
    Rollforward pending = NO
    Restore pending = NO

    Multi-page file allocation enabled = NO

    Log retain for recovery status = NO
    User exit for logging status = NO

    Data Links Token Expiry Interval (sec) (DL_EXPINT) = 60
    Data Links Write Token Init Expiry Intvl(DL_WT_IEXPINT) = 60
    Data Links Number of Copies (DL_NUM_COPIES) = 1
    Data Links Time after Drop (days) (DL_TIME_DROP) = 1
    Data Links Token in Uppercase (DL_UPPER) = NO
    Data Links Token Algorithm (DL_TOKEN) = MAC0

    Database heap (4KB) (DBHEAP) = 1200
    Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC
    Catalog cache size (4KB) (CATALOGCACHE_SZ) = (MAXAPPLS*4)
    Log buffer size (4KB) (LOGBUFSZ) = 8
    Utilities heap size (4KB) (UTIL_HEAP_SZ) = 5000
    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) = 100

    Max size of appl. group mem set (4KB) (APPGROUP_MEM_SZ) = 30000
    Percent of mem for appl. group heap (GROUPHEAP_RATIO) = 70
    Max appl. control heap size (4KB) (APP_CTL_HEAP_SZ) = 59392

    Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = (SHEAPTHRES)
    Sort list heap (4KB) (SORTHEAP) = 256
    SQL statement heap (4KB) (STMTHEAP) = 59392
    Default application heap (4KB) (APPLHEAPSZ) = 59392
    Package cache size (4KB) (PCKCACHESZ) = 64000
    Statistics heap size (4KB) (STAT_HEAP_SZ) = 4384

    Interval for checking deadlock (ms) (DLCHKTIME) = 10000
    Percent. of lock lists per application (MAXLOCKS) = 10
    Lock timeout (sec) (LOCKTIMEOUT) = -1

    Changed pages threshold (CHNGPGS_THRESH) = 60
    Number of asynchronous page cleaners (NUM_IOCLEANERS) = 1
    Number of I/O servers (NUM_IOSERVERS) = 3
    Index sort flag (INDEXSORT) = YES
    Sequential detect flag (SEQDETECT) = YES
    Default prefetch size (pages) (DFT_PREFETCH_SZ) = 32

    Track modified pages (TRACKMOD) = OFF

    Default number of containers = 1
    Default tablespace extentsize (pages) (DFT_EXTENT_SZ) = 32

    Max number of active applications (MAXAPPLS) = AUTOMATIC
    Average number of active applications (AVG_APPLS) = 1
    Max DB files open per application (MAXFILOP) = 64

    Log file size (4KB) (LOGFILSIZ) = 1000
    Number of primary log files (LOGPRIMARY) = 3
    Number of secondary log files (LOGSECOND) = 2
    Changed path to log files (NEWLOGPATH) =
    Path to log files = /u2/db2_in01/db2_in01/NODE0000/SQL00003/SQLOGDIR/
    Overflow log path (OVERFLOWLOGPATH) =
    Mirror log path (MIRRORLOGPATH) =
    First active log file =
    Block log on disk full (BLK_LOG_DSK_FUL) = NO
    Percent of max active log space by transaction(MAX_LOG) = 0
    Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0

    Group commit count (MINCOMMIT) = 1
    Percent log file reclaimed before soft chckpt (SOFTMAX) = 100
    Log retain for recovery enabled (LOGRETAIN) = OFF
    User exit for logging enabled (USEREXIT) = OFF

    Auto restart enabled (AUTORESTART) = ON
    Index re-creation time (INDEXREC) = SYSTEM (RESTART)
    Default number of loadrec sessions (DFT_LOADREC_SES) = 1
    Number of database backups to retain (NUM_DB_BACKUPS) = 12
    Recovery history retention (days) (REC_HIS_RETENTN) = 366


    TSM management class (TSM_MGMTCLASS) =
    TSM node name (TSM_NODENAME) =
    TSM owner (TSM_OWNER) =
    TSM password (TSM_PASSWORD) =


    =============================================
    (Because the space limit of the post, I will post the Database Manager Configuration in a seperate post.)

    Thank you very much for the help!!!
    Attached Files Attached Files

  6. #6
    Join Date
    Jan 2004
    Posts
    4

    Database Manager Configuration

    ================================

    Database Manager Configuration

    Node type = Enterprise Server Edition with local and remote clients

    Database manager configuration release level = 0x0a00

    CPU speed (millisec/instruction) (CPUSPEED) = 1.879021e-06
    Communications bandwidth (MB/sec) (COMM_BANDWIDTH) = 1.000000e+00

    Max number of concurrently active databases (NUMDB) = 8
    Data Links support (DATALINKS) = NO
    Federated Database System Support (FEDERATED) = NO
    Transaction processor monitor name (TP_MON_NAME) =

    Default charge-back account (DFT_ACCOUNT_STR) =

    Java Development Kit installation path (JDK_PATH) = /usr/j2se

    Diagnostic error capture level (DIAGLEVEL) = 3
    Notify Level (NOTIFYLEVEL) = 3
    Diagnostic data directory path (DIAGPATH) = /u2/db2_in01/sqllib/db2dump

    Default database monitor switches
    Buffer pool (DFT_MON_BUFPOOL) = OFF
    Lock (DFT_MON_LOCK) = OFF
    Sort (DFT_MON_SORT) = OFF
    Statement (DFT_MON_STMT) = OFF
    Table (DFT_MON_TABLE) = OFF
    Timestamp (DFT_MON_TIMESTAMP) = ON
    Unit of work (DFT_MON_UOW) = OFF
    Monitor health of instance and databases (HEALTH_MON) = OFF

    SYSADM group name (SYSADM_GROUP) = EVERYONE
    SYSCTRL group name (SYSCTRL_GROUP) =
    SYSMAINT group name (SYSMAINT_GROUP) =

    Database manager authentication (AUTHENTICATION) = SERVER
    Cataloging allowed without authority (CATALOG_NOAUTH) = NO
    Trust all clients (TRUST_ALLCLNTS) = YES
    Trusted client authentication (TRUST_CLNTAUTH) = CLIENT
    Use SNA authentication (USE_SNA_AUTH) = NO
    Bypass federated authentication (FED_NOAUTH) = NO

    Default database path (DFTDBPATH) = /u2/db2_in01

    Database monitor heap size (4KB) (MON_HEAP_SZ) = 90
    Java Virtual Machine heap size (4KB) (JAVA_HEAP_SZ) = 2048
    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) = 20000

    Directory cache support (DIR_CACHE) = YES

    Application support layer heap size (4KB) (ASLHEAPSZ) = 15
    Max requester I/O block size (bytes) (RQRIOBLK) = 32767
    Query heap size (4KB) (QUERY_HEAP_SZ) = 1000
    DRDA services heap size (4KB) (DRDA_HEAP_SZ) = 128

    Priority of agents (AGENTPRI) = SYSTEM
    Max number of existing agents (MAXAGENTS) = 400
    Agent pool size (NUM_POOLAGENTS) = 200
    Initial number of agents in pool (NUM_INITAGENTS) = 0
    Max number of coordinating agents (MAX_COORDAGENTS) = (MAXAGENTS - NUM_INITAGENTS)
    Max no. of concurrent coordinating agents (MAXCAGENTS) = MAX_COORDAGENTS
    Max number of client connections (MAX_CONNECTIONS) = MAX_COORDAGENTS

    Keep fenced process (KEEPFENCED) = YES
    Number of pooled fenced processes (FENCED_POOL) = MAX_COORDAGENTS
    Initialize fenced process with JVM (INITFENCED_JVM) = NO
    Initial number of fenced processes (NUM_INITFENCED) = 0

    Index re-creation time (INDEXREC) = RESTART

    Transaction manager database name (TM_DATABASE) = 1ST_CONN
    Transaction resync interval (sec) (RESYNC_INTERVAL) = 180

    SPM name (SPM_NAME) =
    SPM log size (SPM_LOG_FILE_SZ) = 256
    SPM resync agent limit (SPM_MAX_RESYNC) = 20
    SPM log path (SPM_LOG_PATH) =

    TCP/IP Service name (SVCENAME) =
    Discovery mode (DISCOVER) = SEARCH
    Discovery communication protocols (DISCOVER_COMM) = TCPIP
    Discover server instance (DISCOVER_INST) = ENABLE

    Maximum query degree of parallelism (MAX_QUERYDEGREE) = ANY
    Enable intra-partition parallelism (INTRA_PARALLEL) = NO

    No. of int. communication buffers(4KB)(FCM_NUM_BUFFERS) = 4096
    Node connection elapse time (sec) (CONN_ELAPSE) = 10
    Max number of node connection retries (MAX_CONNRETRIES) = 5
    Max time difference between nodes (min) (MAX_TIME_DIFF) = 60

    db2start/db2stop timeout (min) (START_STOP_TIME) = 10

    =============================================

  7. #7
    Join Date
    Apr 2002
    Posts
    2
    There is an APAR that was apparently fixed in FixPak-4 of DB2 8.1. Here is the link to the APAR http://www-1.ibm.com/support/docview...id=swg1JR19060 . Check if it applies to your situation.

  8. #8
    Join Date
    Jan 2004
    Posts
    4

    Smile

    Thank you very much, ashokrahi!

    I fixed my problem by increasing the QUERY_HEAP_SZ in Database Manager Configuration. For convenient, I increased the value to the maximum. I will also check the link you gave me to see if it also help. Thanks for the reply.

    Wing

Posting Permissions

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