Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2009

    Unanswered: db2syscs memory keeps increasing with every execution of XMLQUERY function

    The following problem occurs only when a query is executed for the view whose field definition depends on the function XMLQUERY multiple times.

    SQL10003C -There are not enough system resources to process the request. The request cannot be processed.

    Consider I have a view VIEW_TABLE_TEST. Also consider the view definition for the following two fields in VIEW_TABLE_TEST .

    FIELD1 - XMLCAST(XMLQUERY('$d/row/c6[position()=1]' passing b.XMLRECORD as "d") as VARCHAR(4000))
    FIELD2 - testget(a.RECID, '-', '1', '1')

    So when I execute the Query “SELECT RECID FROM VIEW_TABLE_TEST WHERE FIELD2 ='1031'” any number of times,everything works fine since the field CUSTOMER_NUMBER depends on the user defined function testget.

    But when I execute the Query “SELECT RECID FROM VIEW_TABLE_TEST WHERE FIELD1 ='US'” several times, I get this problem since the field FIELD1 depends on the function XMLQUERY.So each time the above query is executed, the memory occupied by the process db2sycs(The main DB2 system controller or engine) keeps on increasing. When it reaches the point where no more memory can be allocated , I get this problem.

    Is this a problem with XMLQUERY function?

  2. #2
    Join Date
    Jan 2003
    Provided Answers: 5
    What DB2 version, OS, and FixPack level are you using?


  3. #3
    Join Date
    Sep 2009

    db2syscs memory keeps increasing with every execution of XMLQUERY function

    I am using DB2 Personal Edition 9.7, no fix packs available for 9.7. And the OS is Windows xp.

    Also i have installed DB2 express edition 9.5 in linux where i have the same problem

  4. #4
    Join Date
    Jan 2003
    Provided Answers: 5
    Are these 32 or 64 bit machines? What are the DBM and DB parameter settings?


  5. #5
    Join Date
    Sep 2009

    db2syscs memory keeps increasing with every execution of XMLQUERY function

    Its a 32 bit machine.
    Please find the following database manager configuration settings.

    Database Manager Configuration

    Max number of concurrently active databases (NUMDB) = 3
    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) = E:\DB2Setup\java\jdk
    Diagnostic error capture level (DIAGLEVEL) = 3
    Notify Level (NOTIFYLEVEL) = 3
    Diagnostic data directory path (DIAGPATH) =
    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) = ON
    Timestamp (DFT_MON_TIMESTAMP) = ON
    Unit of work (DFT_MON_UOW) = ON
    Monitor health of instance and databases (HEALTH_MON) = ON

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

    Default database path (DFTDBPATH) = E:

    Database monitor heap size (4KB) (MON_HEAP_SZ) = AUTOMATIC(205)
    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) = 216222
    Backup buffer default size (4KB) (BACKBUFSZ) = 1024
    Restore buffer default size (4KB) (RESTBUFSZ) = 1024

    Agent stack size (AGENT_STACK_SZ) = 16
    Minimum committed private memory (4KB) (MIN_PRIV_MEM) = 32
    Private memory threshold (4KB) (PRIV_MEM_THRESH) = 20000

    Sort heap threshold (4KB) (SHEAPTHRES) = 5000
    Directory cache support (DIR_CACHE) = YES
    Application support layer heap size (4KB) (ASLHEAPSZ) = 3
    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) = 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

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

    NetBIOS Workstation name (NNAME) =

    TCP/IP Service name (SVCENAME) = db2c_DB2
    Discovery mode (DISCOVER) = SEARCH
    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) = AUTOMATIC(895)
    No. of int. communication channels (FCM_NUM_CHANNELS) = AUTOMATIC(256)
    db2start/db2stop timeout (min) (START_STOP_TIME) = 10

    Please find the following database settings

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

    Database territory = US
    Database code page = 1252
    Database code set = IBM-1252
    Database country/region code = 1
    Database collating sequence = UNIQUE
    Alternate collating sequence (ALT_COLLATE) =
    Number compatibility = OFF
    Varchar2 compatibility = OFF
    Date compatibility = OFF
    Database page size = 16384

    Dynamic SQL Query management (DYN_QUERY_MGMT) = DISABLE
    Statement concentrator (STMT_CONC) = OFF
    Discovery support for this database (DISCOVER_DB) = ENABLE

    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(87340)
    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(652)
    Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = 7000
    Sort list heap (4KB) (SORTHEAP) = AUTOMATIC(6000)

    Database heap (4KB) (DBHEAP) = AUTOMATIC(6000)
    Catalog cache size (4KB) (CATALOGCACHE_SZ) = 8
    Log buffer size (4KB) (LOGBUFSZ) = 256
    Utilities heap size (4KB) (UTIL_HEAP_SZ) = 5000
    Buffer pool size (pages) (BUFFPAGE) = 250
    SQL statement heap (4KB) (STMTHEAP) = AUTOMATIC(4096)
    Default application heap (4KB) (APPLHEAPSZ) = AUTOMATIC(4096)
    Application Memory Size (4KB) (APPL_MEMORY) = AUTOMATIC(10000)
    Statistics heap size (4KB) (STAT_HEAP_SZ) = 6000

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

    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) = 32768

    Log file size (4KB) (LOGFILSIZ) = 4096
    Number of primary log files (LOGPRIMARY) = 80
    Number of secondary log files (LOGSECOND) = 60
    Changed path to log files (NEWLOGPATH) =
    Path to log files = E:\DB2\NODE0000\SQL00001\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) = 520
    Log retain for recovery enabled (LOGRETAIN) = OFF
    User exit for logging enabled (USEREXIT) = OFF

    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) = 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

  6. #6
    Join Date
    Jan 2003
    Provided Answers: 5
    Quote Originally Posted by naveen_swiz View Post
    Is this a problem with XMLQUERY function?
    Probably. You should open a PMR with IBM to get this checked out.


  7. #7
    Join Date
    Sep 2009

    Problem with XMLQUERY Function

    Yes, its a problem with XMLQuery function.

Posting Permissions

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