DB2 FOR LUW / DB2FORLUW / 970 - DB2

SQLCODE SQL0901N may be issued compiling a query that satisfies 
the following conditions: 
- There are 2 or more base tables in the query 
- At least one of the base tables (BT) has dependent MQT(s) 
- There is a non-equality predicate (PRD) that references 2 or 
more tables 
- None of the dependent MQTs has the BT column referenced in PRD 
- Other non-BT column(s) referenced in PRD can be mapped to BT 
column(s) via equivalence 
 
An example scenario: 
 
-- DDL 
CREATE TABLE T1 (ID INTEGER); 
CREATE TABLE T2 (ID INTEGER, TXTID CHAR(8)); 
 
SET CURRENT REFRESH AGE ANY; 
CREATE TABLE T2_MQT AS (SELECT ID FROM T2) DATA INITIALLY 
DEFERRED REFRESH DEFERRED; 
REFRESH TABLE T2_MQT; 
 
-- Query 
SELECT  T1.ID 
FROM    T1, 
        T2 
WHERE   (   T2.TXTID = '12345678' -- <-- PRD: TXTID not 
referenced in T2_MQT 
         OR T1.ID = 12345678)     -- <-- PRD: T1.ID maps to 
T2.ID via equivalence 
AND     T1.ID = T2.ID             -- <-- provides equivalence 
for T2.ID 
; 
 
-- -901 issued 
SQL0901N  The SQL statement failed because of a non-severe 
system error. 
Subsequent SQL statements can be processed. 
(Reason "qnc was not found when expected".)  SQLSTATE=58004

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description.                                       * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 9.7 Fix Pack 2 or later.              * 
****************************************************************

Alter the CURRENT QUERY OPTIMIZATON special register to a     va 
lue of 0, 1 or 3 
 
             - 
If the problematic MQT(s) have been identified, add the 
mi 
ssing column(s) to their output, i.e., define T2_MQT as: 
CR 
EATE TABLE T2_MQT AS (SELECT ID, TXTID FROM T2) DATA 
INITIALLYDE 
FERRED REFRESH DEFERRED; 
             - 
Rewrite PRD to a semantically equivalent form that precludes  th 
e conditions specified above, i.e.,: 
 
(T2.TXTID = '12345678' OR T2.ID = 12345678)

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 6 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 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
 

First fixed in DB2 Version 9.7 Fix Pack 2.

not known / see Local fix

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

20.08.2009
22.06.2010
22.06.2010

9.7.FP2