Page 1 of 4 123 ... LastLast
Results 1 to 15 of 51
  1. #1
    Join Date
    Jan 2012
    Posts
    53

    Unanswered: C++ cli application

    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

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    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.
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Jan 2012
    Posts
    53
    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)

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    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)
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    Jan 2012
    Posts
    53
    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

  6. #6
    Join Date
    Aug 2011
    Posts
    46
    seems data is skewed, and you are using scrollable cursor, limiting the rowsfetch probably your best bet.

  7. #7
    Join Date
    Jan 2012
    Posts
    53
    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?

  8. #8
    Join Date
    Aug 2011
    Posts
    46
    seems to me problem is with how you are using SQLFetchScroll API. what's the FetchOrientation ?

  9. #9
    Join Date
    Jan 2012
    Posts
    53
    SQLFetchScroll(stmtHandle, SQL_FETCH_NEXT, 500);

    i am using this orientation.

  10. #10
    Join Date
    Aug 2011
    Posts
    46
    You can try with even smaller rowfetchsize (e.g. 20,50 ). And open a PMR with IBM, seems problem with SQLFetchScroll API.

  11. #11
    Join Date
    Jan 2012
    Posts
    53
    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.

  12. #12
    Join Date
    Aug 2011
    Posts
    46
    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.

  13. #13
    Join Date
    Jan 2012
    Posts
    53
    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.

  14. #14
    Join Date
    Jan 2012
    Posts
    53
    Any solution for this problem ?

    I am still stuck in the problem.

  15. #15
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Tags for this Thread

Posting Permissions

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