DB2 FOR LUW / DB2FORLUW / 970 - DB2

INSERT into a view V1 may return SQL901N with reason "parent in 
1-1 rel. already has child" 
when all of the following conditions are satisfied: 
 
(1) View V1 is based on another view V0 
(2) View V0 is based on a table T1 that has a column (COLDEFVAL) 
with DEFAULT value 
(3) In the SELECT list of the definition of view V0, a column 
without default value is listed *after* column COLDEFVAL. 
(4) olumn COLDEFVAL is not referenced in the definition of view 
V1 
(5) An insead-of INSERT trigger is defined on view V0. 
 
 
Example: 
 
CREATE TABLE T1 (C1 int, 
                 COLDEFVAL int with default 1); 
 
CREATE VIEW V0 AS 
      SELECT 
       COLDEFVAL, 
       C1 
      FROM T1; 
 
CREATE TRIGGER TRG1 
 INSTEAD OF INSERT ON V0 
  REFERENCING NEW AS N 
   FOR EACH ROW 
    MODE DB2SQL 
 BEGIN ATOMIC 
   insert into t1 values (N.C1, DEFAULT);-- 
 END; 
 
CREATE VIEW V1 AS 
   SELECT C1 
   FROM V0; 
 
INSERT INTO V1 values (10);

INSERT into a view V1 may return SQL901N with reason "parent in 
 
1-1 rel. already has child" 
when all of the following conditions are satisfied: 
 
(1) View V1 is based on another view V0 
(2) View V0 is based on a table T1 that has a column (COLDEFVAL) 
with DEFAULT value 
(3) In the SELECT list of the definition of view V0, a column 
without default value is listed *after* column COLDEFVAL. 
(4) olumn COLDEFVAL is not referenced in the definition of view 
V1 
(5) An insead-of INSERT trigger is defined on view V0. 
 
 
Example: 
 
CREATE TABLE T1 (C1 int, 
                 COLDEFVAL int with default 1); 
 
CREATE VIEW V0 AS 
      SELECT 
       COLDEFVAL, 
       C1 
      FROM T1; 
 
CREATE TRIGGER TRG1 
 INSTEAD OF INSERT ON V0 
  REFERENCING NEW AS N 
   FOR EACH ROW 
    MODE DB2SQL 
 BEGIN ATOMIC 
   insert into t1 values (N.C1, DEFAULT);-- 
 END; 
 
CREATE VIEW V1 AS 
   SELECT C1 
   FROM V0; 
 
INSERT INTO V1 values (10);

In the definition of the intermediate view (V0 in the example 
above) with instead-of-trigger, put those columns (e.g. 
COLDEFVAL) that are not referenced in the referencng view (e.g. 
V1) at the end. 
 
For example: 
 
CREATE VIEW V0 AS 
      SELECT 
       C1, 
       COLDEFVAL                                  <<<<< Put 
COLDEFVAL last. 
      FROM T1; 
 
 
Note: Changing the column order in a view definition changes the 
order of columns returned by queries like "SELECT * from V0". 
Existing applications with queries that relies on the previous 
column order may be affected.   Queries that list the columns 
explicitly (instead of select *) will not be affected.

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

Defect   => wsdbu00627865 
Fixed in => v9.7 + FP3 
Module   => engn_sqnq

In the definition of the intermediate view (V0 in the example 
above) with instead-of-trigger, put those columns (e.g. 
COLDEFVAL) that are not referenced in the referencng view (e.g. 
V1) at the end. 
 
For example: 
 
CREATE VIEW V0 AS 
      SELECT 
       C1, 
       COLDEFVAL                                  <<<<< Put 
COLDEFVAL last. 
      FROM T1; 
 
 
Note: Changing the column order in a view definition changes the 
order of columns returned by queries like "SELECT * from V0". 
Existing applications with queries that relies on the previous 
column order may be affected.   Queries that list the columns 
explicitly (instead of select *) will not be affected.

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

17.03.2010
24.09.2010
24.09.2010

9.7.