DB2 FOR LUW / DB2FORLUW / A10 - DB2

When a transaction performs XML processing involving the 
XMLTable function, a temp table may need to be created. 
e.g. a select like : 
 
select  X.COL1 from XMLSOURCE S, XMLTABLE (  '$s/NOD1' PASSING 
S.MYXML as "s" COLUMNS COL1 VARCHAR(100) PATH 'TAB1/COL1' ) AS 
X; 
 
would create a system temporary table. 
When the transaction is completed , commit or rollback, this 
temp table is dropped. 
 
Dropping of these temps is usually executed asynchronously by 
the prefetcher edus to minimize the performance impact. 
 
However, when there is a high level of concurrency it is 
possible that the available prefetch requests become exhausted 
at which point the agent performing the commit/rollback will 
drop the temp. 
 
This in turn can increase the concurrency on the function 
performing the actual drop and this can create a situation of 
high latch contention driving significant performance impact. 
 
The stacks for the sessions in contention may show : 
 
sqloSpinLockConflict 
sqlbGetDLSize 
sqlbPurgeObject 
sqlbSMSDeleteObject 
sqldDropObj 
sqldDropTable 
sqldTableDrop 
 
and/or 
 
sqloSpinLockConflict 
sqlbSplatObject 
sqlbPurgeObject 
sqlbSMSDeleteObject 
sqldDropObj 
sqldDropTable 
sqldTableDrop

See Problem Description

The problem can be alleviated by 
1. increasing the available prefetch requests by setting values 
for NUMPREFETCHQUEUES and PREFETCHQUEUESIZE. ( in releases < 
V10.1 ). See Infocenter on how to set these. 
2. associating a smaller dedicated bufferpool to the temp 
tablespace. 
bufferpool to temp tablespaces.

See Problem Description

See Problem Description

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

05.06.2014
15.07.2015
15.07.2015