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?

What DB2 version, OS, and FixPack level are you using?

Andy

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

Are these 32 or 64 bit machines? What are the DBM and DB parameter settings?

Andy

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

Probably. You should open a PMR with IBM to get this checked out.

Andy

Yes, its a problem with XMLQuery function.