Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2010

    Unanswered: CLI Multithreading


    I am trying to develop a C++ application using DB2 CLI calls. In my application, I have 3 threads and each thread have a separate evironment and connection handles.

    My problem is that when I try to execute a query in the main thread, everything works fine but when I execute the same query on child thread and connection, I got an error message

    SQL0954C Not enough storage is available in the application heap to process the statement. SQLSTATE=57011

    The table I am trying to query only have 10 rows in it with no lob data.

    Any help will be highly appreciated.


  2. #2
    Join Date
    Apr 2012
    Provided Answers: 17
    Have you followed the advice for that sql code for your version of db2 and your operating system ?
    Like in here:

  3. #3
    Join Date
    Jan 2010
    I did follow but no luck.

  4. #4
    Join Date
    Apr 2012
    Provided Answers: 17
    what is your db2 version, fixpack, operating-system for your client (where your CLI program is running)?
    on your client (where the CLI is running) what is is the db2diag.log file.
    what is the value of the APPLHEAPSZ? is it AUTOMATIC ?
    Is your database on the same hostname where you CLI application is running ?

    Give more facts to get more help

  5. #5
    Join Date
    Jan 2010
    My client program is running on windows. DB2 database is running on HP-UX.

    DB2 Client Info
    Informational tokens are "DB2 v9.1.700.855", "s090308", "WR21434", and Fix Pack "7".

    DB2 Database Info
    DB21085I Instance "mrfinst1" uses "64" bits and DB2 code release "SQL09070" with level identifier "08010107".
    Informational tokens are "DB2 v9.7.0.0", "s090521", "HPIPF6497", and Fix Pack "0".
    Product is installed at "/db2home/v97".

    DB2 Database Manager Configuration
    Database Manager Configuration

    Node type = Database Server with local and remote clients

    Database manager configuration release level = 0x0d00

    CPU speed (millisec/instruction) (CPUSPEED) = 2.361721e-07

    Max number of concurrently active databases (NUMDB) = 8
    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) = /home/mrfinst1/sqllib/java/jdk64

    Diagnostic error capture level (DIAGLEVEL) = 3
    Notify Level (NOTIFYLEVEL) = 3
    Diagnostic data directory path (DIAGPATH) = /home/mrfinst1/sqllib/db2dump
    Size of rotating db2diag & notify logs (MB) (DIAGSIZE) = 0

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

    SYSCTRL group name (SYSCTRL_GROUP) =
    SYSMAINT group name (SYSMAINT_GROUP) =
    SYSMON group name (SYSMON_GROUP) =

    Client Userid-Password Plugin (CLNT_PW_PLUGIN) =
    Client Kerberos Plugin (CLNT_KRB_PLUGIN) =
    Group Plugin (GROUP_PLUGIN) =
    GSS Plugin for Local Authorization (LOCAL_GSSPLUGIN) =
    Server Plugin Mode (SRV_PLUGIN_MODE) = UNFENCED
    Server List of GSS Plugins (SRVCON_GSSPLUGIN_LIST) =
    Server Userid-Password Plugin (SRVCON_PW_PLUGIN) =
    Server Connection Authentication (SRVCON_AUTH) = NOT_SPECIFIED
    Cluster manager (CLUSTER_MGR) =

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

    Default database path (DFTDBPATH) = /home/mrfinst1

    Database monitor heap size (4KB) (MON_HEAP_SZ) = AUTOMATIC(90)
    Java Virtual Machine heap size (4KB) (JAVA_HEAP_SZ) = 4096
    Audit buffer size (4KB) (AUDIT_BUF_SZ) = 0
    Size of instance shared memory (4KB) (INSTANCE_MEMORY) = 4119280
    Backup buffer default size (4KB) (BACKBUFSZ) = 1024
    Restore buffer default size (4KB) (RESTBUFSZ) = 1024

    Agent stack size (AGENT_STACK_SZ) = 1024
    Sort heap threshold (4KB) (SHEAPTHRES) = 0

    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

    Workload impact by throttled utilities(UTIL_IMPACT_LIM) = 10

    Priority of agents (AGENTPRI) = SYSTEM
    Agent pool size (NUM_POOLAGENTS) = AUTOMATIC(100)
    Initial number of agents in pool (NUM_INITAGENTS) = 0
    Max number of coordinating agents (MAX_COORDAGENTS) = AUTOMATIC(200)
    Max number of client connections (MAX_CONNECTIONS) = AUTOMATIC(MAX_COORDAGENTS)

    Keep fenced process (KEEPFENCED) = YES
    Number of pooled fenced processes (FENCED_POOL) = AUTOMATIC(MAX_COORDAGENTS)
    Initial number of fenced processes (NUM_INITFENCED) = 0

    Index re-creation time and redo index build (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) = db2c_mrfinst1
    Discovery mode (DISCOVER) = SEARCH
    Discover server instance (DISCOVER_INST) = ENABLE

    SSL server keydb file (SSL_SVR_KEYDB) =
    SSL server stash file (SSL_SVR_STASH) =
    SSL server certificate label (SSL_SVR_LABEL) =
    SSL service name (SSL_SVCENAME) =
    SSL cipher specs (SSL_CIPHERSPECS) =
    SSL versions (SSL_VERSIONS) =
    SSL client keydb file (SSL_CLNT_KEYDB) =
    SSL client stash file (SSL_CLNT_STASH) =

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

    No. of int. communication buffers(4KB)(FCM_NUM_BUFFERS) = AUTOMATIC(1024)
    No. of int. communication channels (FCM_NUM_CHANNELS) = AUTOMATIC(512)
    db2start/db2stop timeout (min) (START_STOP_TIME) = 10

  6. #6
    Join Date
    Jan 2010
    DB2 Database Configuration

    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 = SYSTEM_1051
    Alternate collating sequence (ALT_COLLATE) =
    Number compatibility = OFF
    Varchar2 compatibility = OFF
    Date compatibility = OFF
    Database page size = 4096

    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 = RECOVERY
    User exit for logging status = YES

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

    Database heap (4KB) (DBHEAP) = AUTOMATIC(2967)
    Catalog cache size (4KB) (CATALOGCACHE_SZ) = 300
    Log buffer size (4KB) (LOGBUFSZ) = 2048
    Utilities heap size (4KB) (UTIL_HEAP_SZ) = 202361
    Buffer pool size (pages) (BUFFPAGE) = 1000
    SQL statement heap (4KB) (STMTHEAP) = AUTOMATIC(10000)
    Default application heap (4KB) (APPLHEAPSZ) = 10000
    Application Memory Size (4KB) (APPL_MEMORY) = AUTOMATIC(40000)
    Statistics heap size (4KB) (STAT_HEAP_SZ) = AUTOMATIC(4384)

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

    Changed pages threshold (CHNGPGS_THRESH) = 80
    Number of asynchronous page cleaners (NUM_IOCLEANERS) = AUTOMATIC(7)
    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(122)
    Average number of active applications (AVG_APPLS) = AUTOMATIC(1)
    Max DB files open per application (MAXFILOP) = 61440

    Log file size (4KB) (LOGFILSIZ) = 8192
    Number of primary log files (LOGPRIMARY) = 50
    Number of secondary log files (LOGSECOND) = 50
    Changed path to log files (NEWLOGPATH) =
    Path to log files = /db2data/mlmaindb/mlmainlg/
    Overflow log path (OVERFLOWLOGPATH) =
    Mirror log path (MIRRORLOGPATH) =
    First active log file = S0017916.LOG
    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) = 520
    Log retain for recovery enabled (LOGRETAIN) = RECOVERY
    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) = DISK:/db2data/mlmaindb/mlarchlg/
    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) = SYSTEM (RESTART)
    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

    SMTP Server (SMTP_SERVER) =

  7. #7
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1
    I would start with updating your client to the version of the server.
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Jan 2010
    I updated my client to version 10.1 fix 3. I can't update it to 10.5 as my OS is only win XP SP2.

    I am still having the error but this time it is different.

    [IBM][CLI Driver][DB2/HPUX-IA64] SQL10003C There are not enough system resources to process the request. The request cannot be processed. SQLSTATE=57011

    I am attaching my client program and the db2diag.log file

    I do not know what I am missing
    Attached Files Attached Files

  9. #9
    Join Date
    Apr 2012
    Provided Answers: 17
    The suggestion by n_i was to upgrade your db2-client to the same version as the db2-server.
    But you chose for some reason to make your db2-client a higher version than your db2-server.

    You wrote:
    db2-client (originally) was WinXP db2-client version 9.1.7
    db2-server was HPUX 64-bit db2-server 9.7.0

    I believe the suggestion was that you upgrade your db2-client on WinXP to db2-client 9.7 fixpack 0 (that would then make it the same as your reported db2-server version).

    You may still get the same (or different) symptom, but at least the version of client and server would then be the same , so version incompatibilities can be eliminated.

  10. #10
    Join Date
    Jan 2010
    I reinstalled my original same client driver again (9.7 Fix Pack 1) and it worked fine for unknown reason.

    Anyways, I looked into the processing of different threads (each thread has separate connection and context) but there is no improvment at.

    I looked into the TCPView utility and found out that all my calls are getting serialized to the db2 server even still i have multiple connections to the server. Really desparate to solve this problem.

    Another problem i saw is that db2Export routine took about 5 mins in order to export 150,000 rows in csv file. Kindly note that my db2 server is located across the continent (7000 miles away). I do not know whether it is normal or not.

    Thanks once again for the help.

  11. #11
    Join Date
    Jan 2010
    Any help will be highly appreciated.

  12. #12
    Join Date
    Jan 2009
    Zoetermeer, Holland
    did you already try the obvious? Increase the applheapsize on the server??? 10.000 4k pages is not that much
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  13. #13
    Join Date
    Jan 2010
    My problem is that I have two separate connections (on separate threads) to the db2 server. Instead of executing simultaneously, it is executing synchronously.

    I really do not understand that. Even I allocated and attached application context for each separate thread.

  14. #14
    Join Date
    Apr 2012
    Provided Answers: 17
    I don't see any references to contexts in the source code you provided.

    Get the IBM sample program dbthrds.sqc working first, see that it's threads are not serialised in db2.

Posting Permissions

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