I am trying to retrieve data from DB database table using CLI in c++ application.
and getting the error

[IBM][CLI Driver][DB2/NT64] SQL0954C Not enough storage is available in the application heap to process the statement. SQLSTATE=57011



But when i reduce the number of rowstofetch in SQLFetchScroll it returns more bytes of data and when i increase it returns less bytes of data and at the end throw the above error.

database have following configurations

Database Configuration for Database

Database configuration release level = 0x0d00
Database release level = 0x0d00

Database territory = US
Database code page = 1208
Database code set = UTF-8
Database country/region code = 1
Database collating sequence = IDENTITY
Alternate collating sequence (ALT_COLLATE) =
Number compatibility = OFF
Varchar2 compatibility = OFF
Date compatibility = OFF
Database page size = 32768

Dynamic SQL Query management (DYN_QUERY_MGMT) = DISABLE

Statement concentrator (STMT_CONC) = OFF

Discovery support for this database (DISCOVER_DB) = ENABLE

Restrict access = NO
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
Default maintained table types for opt (DFT_MTTB_TYPES) = SYSTEM
Number of frequent values retained (NUM_FREQVALUES) = 10
Number of quantiles retained (NUM_QUANTILES) = 20

Decimal floating point rounding mode (DECFLT_ROUNDING) = ROUND_HALF_EVEN

Backup pending = NO

All committed transactions have been written to disk = NO
Rollforward pending = NO
Restore pending = NO

Multi-page file allocation enabled = YES

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

Self tuning memory (SELF_TUNING_MEM) = ON
Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC(218980)
Database memory threshold (DB_MEM_THRESH) = 10
Max storage for lock list (4KB) (LOCKLIST) = AUTOMATIC(4096)
Percent. of lock lists per application (MAXLOCKS) = AUTOMATIC(60)
Package cache size (4KB) (PCKCACHESZ) = AUTOMATIC(1115)
Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = AUTOMATIC(3926)
Sort list heap (4KB) (SORTHEAP) = AUTOMATIC(786)

Database heap (4KB) (DBHEAP) = AUTOMATIC(60000)
Catalog cache size (4KB) (CATALOGCACHE_SZ) = 343
Log buffer size (4KB) (LOGBUFSZ) = 256
Utilities heap size (4KB) (UTIL_HEAP_SZ) = 12841
Buffer pool size (pages) (BUFFPAGE) = 10000
SQL statement heap (4KB) (STMTHEAP) = AUTOMATIC(60000)
Default application heap (4KB) (APPLHEAPSZ) = AUTOMATIC(50000)
Application Memory Size (4KB) (APPL_MEMORY) = AUTOMATIC(40000)
Statistics heap size (4KB) (STAT_HEAP_SZ) = 10000

Interval for checking deadlock (ms) (DLCHKTIME) = 10000
Lock timeout (sec) (LOCKTIMEOUT) = -1

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

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(40)
Average number of active applications (AVG_APPLS) = AUTOMATIC(1)
Max DB files open per application (MAXFILOP) = 65535

Log file size (4KB) (LOGFILSIZ) = 1024
Number of primary log files (LOGPRIMARY) = 29
Number of secondary log files (LOGSECOND) = 7
Changed path to log files (NEWLOGPATH) =
Path to log files = C:\DB2\NODE0000\SQL00005\SQLOGDIR\
Overflow log path (OVERFLOWLOGPATH) =
Mirror log path (MIRRORLOGPATH) =
First active log file =
Block log on disk full (BLK_LOG_DSK_FUL) = NO
Block non logged operations (BLOCKNONLOGGED) = NO
Percent max primary 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) = 1160
Log retain for recovery enabled (LOGRETAIN) = OFF
User exit for logging enabled (USEREXIT) = OFF

HADR database role = STANDARD
HADR local host name (HADR_LOCAL_HOST) =
HADR local service name (HADR_LOCAL_SVC) =
HADR remote host name (HADR_REMOTE_HOST) =
HADR remote service name (HADR_REMOTE_SVC) =
HADR instance name of remote server (HADR_REMOTE_INST) =
HADR timeout value (HADR_TIMEOUT) = 120
HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC
HADR peer window duration (seconds) (HADR_PEER_WINDOW) = 0

First log archive method (LOGARCHMETH1) = OFF
Options for logarchmeth1 (LOGARCHOPT1) =
Second log archive method (LOGARCHMETH2) = OFF
Options for logarchmeth2 (LOGARCHOPT2) =
Failover log archive path (FAILARCHPATH) =
Number of log archive retries on error (NUMARCHRETRY) = 5
Log archive retry Delay (secs) (ARCHRETRYDELAY) = 20
Vendor options (VENDOROPT) =

Auto restart enabled (AUTORESTART) = ON
Index re-creation time and redo index build (INDEXREC) =
Log pages during index build (LOGINDEXBUILD) = OFF
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
Auto deletion of recovery objects (AUTO_DEL_REC_OBJ) = OFF

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

Automatic maintenance (AUTO_MAINT) = ON
Automatic database backup (AUTO_DB_BACKUP) = OFF
Automatic table maintenance (AUTO_TBL_MAINT) = ON
Automatic runstats (AUTO_RUNSTATS) = ON
Automatic statement statistics (AUTO_STMT_STATS) = ON
Automatic statistics profiling (AUTO_STATS_PROF) = OFF
Automatic profile updates (AUTO_PROF_UPD) = OFF
Automatic reorganization (AUTO_REORG) = OFF

Auto-Revalidation (AUTO_REVAL) = DEFERRED
Currently Committed (CUR_COMMIT) = ON
CHAR output with DECIMAL input (DEC_TO_CHAR_FMT) = NEW
Enable XML Character operations (ENABLE_XMLCHAR) = YES
WLM Collection Interval (minutes) (WLM_COLLECT_INT) = 0
Monitor Collect Settings
Request metrics (MON_REQ_METRICS) = BASE
Activity metrics (MON_ACT_METRICS) = BASE
Object metrics (MON_OBJ_METRICS) = BASE
Unit of work events (MON_UOW_DATA) = NONE
Lock timeout events (MON_LOCKTIMEOUT) = NONE
Deadlock events (MON_DEADLOCK) = WITHOUT_HIST
Lock wait events (MON_LOCKWAIT) = NONE
Lock wait event threshold (MON_LW_THRESH) = 5000000

Please Help

as indicated in doc

Terminate the application on receipt of this message. If the applheapsz database configuration parameter is set to AUTOMATIC, you will need to increase either the APPL_MEMORY database configuration setting, or the INSTANCE_MEMORY database manager configuration setting. Otherwise, increase the applheapsz database configuration parameter to allow a larger application heap.

Thanks przytula_guy

I have also studied about the APPLHEAPSZ. but this is not effecting in my application.and current heap size is mentioned below . do you think this is not enough for a normal database. I am only able to get more data when i reduce rowstofetch in SQLFetchScroll. nothing else is helping

Default application heap (4KB) (APPLHEAPSZ) = AUTOMATIC(50000)

APPLHEAPSZ=automatic = will be adjusted as needed - (50000) is the default value
APPL_MEMORY =automatic = will be adjusted as needed
so - check INSTANCE_MEMORY (dbm cfg)

INSTANCE_MEMORY =524288(4k Pages) which is dynamic.
And this is the maximum allowed INSTANCE MEMORY.Table is not that much big it contains only about 2GB data but still the maximum data i can get is around 980MB.

What should i do now?

"APPLHEAPSZ=automatic = will be adjusted as needed - (50000) is the default value"

If i will not set the automatic APPLHEAPSZ then database will use 50000 every time?

Thanks

seems data is skewed, and you are using scrollable cursor, limiting the rowsfetch probably your best bet.

but with 200 rowstofetch I am getting only 1GB data in one hour.and at the getting same here
[IBM][CLI Driver][DB2/NT64] SQL0954C Not enough storage is available in the application heap to process the statement. SQLSTATE=57011

do you think this problem is because of database or CLI API functions?

seems to me problem is with how you are using SQLFetchScroll API. what's the FetchOrientation ?

SQLFetchScroll(stmtHandle, SQL_FETCH_NEXT, 500);

i am using this orientation.

You can try with even smaller rowfetchsize (e.g. 20,50 ). And open a PMR with IBM, seems problem with SQLFetchScroll API.

Thanks.

Now i am running with 50.but its taking too much time.how can i optimize this because at the end we are planning to fetch larger table in my application.i want
a solution for any size of table.

You can try various combinations of rowfetchsize and application heap(try setting higher value manually), see which combination gives better perfomance. In the meantime open a PMR with IBM, as it is specific to SQLFetchScroll API.

Note that you must have a SoftwareXcel basic edition or enterprise edition contract with IBM to ask a question.

I am using IBM DB2 DB2 Express-C without any contract with IBM. can i still ask any question to IBM?

and by using 50 i am getting the same error with some more bytes of data.

Any solution for this problem ?

I am still stuck in the problem.

Do you really need a scrollable cursor? Also check if you use and really need other cursor properties (e.g. WITH HOLD, isolation level etc.) And you may want to have a look at your query. If you can do without ORDER BY - as a very simply example - you may not need temp space in DB2 for the result table and reduce memory consumption that way, too.