DB2 FOR LUW / DB2FORLUW / 970 - DB2

A query prefixed with the DB2 support '.db2service' keyword may 
abend the instance if any of the tables used in the query has a 
dependent statistical view defined. 
 
An example of such a scenario follows: 
 
-- Table DDL 
CREATE TABLE T1 (C1 INT); 
CREATE TABLE T2 (C1 INT); 
 
-- View DDL 
CREATE VIEW STATVIEW AS (SELECT T1.C1 AS C1, T2.C1 AS C2 FROM 
T1, T2 WHERE T1.C1 = T2.C1); 
 
-- Convert view into a statistical view 
ALTER VIEW STATVIEW ENABLE QUERY OPTIMIZATION; 
 
-- Query that will abend the instance 
.db2service SELECT T1.C1 AS C1, T2.C1 AS C2 FROM T1, T2 WHERE 
T1.C1 = T2.C1; 
 
The stack trace from such an abend will look something like: 
sqlnq_mchFailList::findElem 
sqlnq_mchFailList::addElem 
sqlnq_qur::add_matchable_MQT_SV 
sqlnq_som::compute_matchinfo_opt 
sqlnr_compute_matchinfo 
sqlnr_optprep 
sqlnr_optprep_action 
sqlnr_comp 
sqlnr_seq 
sqlnr_rcc 
sqlnr_exe 
sqlnx_menuproc 
sqlnx_drv 
sqlnn_cmpl 
... 
 
This problem will be first fixed in DB2 Version 9.1 Fix Pack 8 
and DB2 Version 9.5 Fix Pack 5.

**************************************************************** 
* USERS AFFECTED:                                              * 
* EE or EEE                                                    * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description.                                       * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 9.7 Fix Pack 1.                       * 
****************************************************************

No feasible workaround exists aside from: 
1) Not using the '.db2service' keyword 
2) Disabling any dependent statistical views of tables used in 
the query for query optimization. For the aforementioned query, 
this may be done like so: 
 
-- Revert view from a statistical view to a normal view 
ALTER VIEW STATVIEW DISABLE QUERY OPTIMIZATION;

DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 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 10 for Linux, UNIX, and Windows
 

Problem was first fixed in DB2 Version 9.7 Fix Pack 1.

not known / see Local fix

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

21.08.2009
19.02.2010
19.02.2010

9.7.FP1