DB2 FOR LUW / DB2FORLUW / 980 - 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 
 
PROBLEM SUMMARY: 
 
see ERROR DESCRIPTION

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

DB2 Version 9.8 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 9.8 Fix Pack 4 for AIX and Linux
 DB2 Version 9.8 Fix Pack 5 for AIX and Linux
 

The complete fix for this problem first appears in DB2 UDB 
Version 9.8 FixPak 3.

not known / see Local fix

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

23.02.2010
30.12.2010
30.12.2010

9.8.