DB2 FOR LUW / DB2FORLUW / 970 - DB2

CREATE VIEW may fail with SQL0340N if the view references one 
or more inoperative views. 
 
All of the following conditions must be satisfied: 
 
- Database configuration parameter AUTO_REVAL is set to DEFERRED 
  or DEFERRED_FORCE 
- A view (V) is being created 
- V's creation text refers to one or more inoperative views 
  (IN_V) 
- V's creation text also refers to tables/views (BT_V) other 
  than IN_V 
- IN_V's creation text also refers to BT_V 
- V's creation text refers to BT_V one or more times before it 
  refers to IN_V 
- V's creation text refers to BT_V one or more times after it 
  refers to IN_V 
 
Test case: 
========== 
-- table 
create table bt_v (c int); 
 
-- nested view to be made inoperative 
create view in_v as (select c from bt_v); 
 
-- make in_v inoperative 
drop table bt_v; 
 
-- recreate bt_v 
create table bt_v (c int); 
 
-- create view which will derive re-validation of in_v 
create view v as (select bt_v.c from bt_v, in_v union select 
bt_v.c from bt_v); 
 
 
The following eye catchers are logged in db2diag.log: 
 
2010-12-14-15.00.16.640000-480 I198263H896        LEVEL: Info 
PID     : 5952                 TID  : 4312        PROC : 
db2syscs.exe 
INSTANCE: DB2                  NODE : 000         DB   : xxxxx 
APPHDL  : 0-38                 APPID: *LOCAL.DB2.101214225928 
AUTHID  : DB2ADMIN 
EDUID   : 4312                 EDUNAME: db2agent (xxxxx) 0 
FUNCTION: DB2 UDB, SW- common services, sqlnn_cmpl, probe:670 
MESSAGE : ZRC=0x8031000F=-2144272369=SQLNN_E_AMBIG 
          "Conflicting or ambiguous elements in the command or 
an internal object" 
DATA #1 : String, 327 bytes 
Compiler error stack for rc = -2144272369: 
sqlnn_cmpl[300] 
sqlnp_main[250] 
sqlnp_parser[330] 
sqlnp_smactn[100] 
sqlnq_sem[460] 
sqlnq_handle_from_table_re[10] 
sqlnq_handle_table_ref[5] 
sqlnq_handle_named_ref[110] 
sqlnq_handle_named_ref[110] 
sqlnq_handle_named_ref[110] 
sqlnq_handle_named_ref[120] 
sqlnq_handle_qtb_extref_re[190]

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

Move the reference to the nested inoperative view(s) (that will 
get revalidated) to the end. For the view in the example 
provided, this may be done like so: 
 
create view v as (select bt_v.c from bt_v 
                  union 
                  select bt_v.c from bt_v, in_v);

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 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 4.

not known / see Local fix

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

27.01.2011
28.04.2011
28.04.2011

9.7.FP4