DB2 FOR LUW / DB2FORLUW / 970 - DB2

Query using text search will crash instance when text function 
(SCORE, NUMBEROFMATCHES, CONTAINS) are present in a sub-select 
containing an OUTER JOIN or when the input column to the text 
function is produced from the result of an OUTER JOIN operator 
in a sub-select or view. 
 
 
Furthermore this occurs when the text function is used as an 
input to another function or basic predicate where the text 
function result is checked to be greater then a value, for 
example 
 
CONTAINS(...) > 0 
CONTAINS(...) >= ? 
? < CONTAINS(...) 
1 <= CONTAINS(...) 
 
In stack will see functions similar to the following 
 
sqlnq_pid::myqncp 
<sqlnr_tsoj_rewrite> <sqlnr_qrwprep.C:3313> 
<sqlnr_qrwprep_phase2> <sqlnr_qrwprep.C:10113> 
<sqlnr_prep2_action> <sqlnr_exe.C:10133> 
<sqlnr_seq> <sqlnr_sys.C:602> 
<sqlnr_rcc> <sqlnr_sys.C:768> 
<sqlnr_exe> <sqlnr_exe.C:11057> 
<sqlnn_cmpl> <sqlnn_cmpl.C:3345> 
<sqlnn_cmpl> <sqlnn_cmpl.C:530> 
<sqlra_compile_var> <sqlra_cmpl.C:1053> 
<sqlra_find_var> <sqlra_dyn.C:4312> 
<sqlra_get_var> <sqlra_var.C:1778> 
<sqlrr_prepare> <sqlrr_req_sql.C:2987> 
<sqljs_ddm_prpsqlstt> <sqljspds.C:633>

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 9.7 Fix Pack 8                        * 
****************************************************************

For the CONTAINS function the workaround to this problem is to 
change the basic predicate so that the result of the text 
function is compared less then a different value. For example 
 
     WHERE 0 < CONTAINS(T1.T1_DATA,'Example') 
 
Instead use the following which will avoid the error. 
 
     WHERE 1 >= CONTAINS(T1.T1_DATA,'Example') 
 
Similar workaround may be possible for the SCORE and 
NUMBEROFMATCHES functions.

DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 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 Version 9.7 Fix Pack 8

not known / see Local fix

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

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

10.08.2012
01.04.2013
01.04.2013

9.7.FP8