DB2 FOR LUW / DB2FORLUW / 970 - DB2

The problem happens when multiple applications are running 
simultaneously and they all have complex expressions used in 
LIKE predicates. 
The cause of the performance issue is related to frequent memory 
operations during the handling of the LIKE operation. 
 
Here is an example of a LIKE predicates that can trigger the 
problem: 
  Column1 LIKE REPLACE (Column2, '*', '&') 
 
In this example, since the pattern being compared is the result 
of an expression (in this case, the result of the REPLACE 
function), it requires that some memory is used to compute the 
pattern for every row scanned. The handling of this memory 
operation is inefficient, leading to a slower query performance. 
 
When the problem happens, please issue "db2pd -stack all" 
command to collect DB2 stack files. 
 
If the following stack traces can be found in the stack files, 
then it may suggest that this issue is occurring: 
 
=========================================== 
sqloSpinLockConflict 
SMemSet::captureLatch 
SQLO_MEM_POOL:allocateMemoryBlock 
sqlogmblkEx 
sqlmoAllocBlock 
sqlogmblkEx 
sqlri_alloc_pattern_mem 
sqlri_like_comp_pat 
 
<LatchInformation> 
Waiting on latch type: (SQLO_LT_SMemSet__MemLatchType__latch) 
</LatchInformation> 
 
Or 
 
sqloSpinLockConflict 
SMemSet::captureLatch 
sqlofmblkEx 
sqlri_free_pattern_mem 
sqlri_like_comp_pat 
 
<LatchInformation> 
Waiting on latch type: (SQLO_LT_SMemSet__MemLatchType__latch) 
</LatchInformation> 
=========================================== 
 
<StackTrace> 
MemChunksGet 
SMemSet::getChunksFromTree 
SMemSet:getContiguousChunks 
SQLO_MEM_POOL::allocateMemoryBlock 
sqlogmblkEx 
sqlmoAllocBlock 
sqlogmblkEx 
sqlri_alloc_pattern_mem 
sqlri_like_comp_pat 
</StackTrace> 
 
<LatchInformation> 
Holding Latch type: (SQLO_LT_SMemSet__MemLatchType__latch) 
</LatchInformation> 
 
Or 
 
<StackTrace> 
SMemSet::releaseLatch 
sqlofmblkEx 
sqlri_free_pattern_mem 
sqlri_like_comp_pat 
address: 0x00007FFFF558900B ; dladdress: 0x00007FFFF28F3000 ; 
offset in lib: 0x0000000002C9600B ; 
sqlri_like_comp_pat 
</StackTrace> 
 
<LatchInformation> 
Holding Latch type: (SQLO_LT_SMemSet__MemLatchType__latch) 
</LatchInformation> 
===========================================

**************************************************************** 
* USERS AFFECTED:                                              * 
* All users of version 9.7 on Linux, Unix and Windows          * 
* platforms.                                                   * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Update to DB2 LUW 9.7 Fix Pack 9 or higher levels.           * 
****************************************************************

Use constant strings in LIKE predicates, instead of complex 
compressions.

DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

First fixed in DB2 LUW Version 9.7 Fix Pack 9.

Use constant strings in LIKE predicates, instead of complex 
compressions.

forerunner  : APAR is sysrouted TO one or more of the following: IC97012 IC97836 
follow-up : 

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

20.08.2013
06.01.2014
06.01.2014

9.7.FP9