|
Problem IC67243
|
Status: Closed |
CREATION OF SUBVIEW SUCCEEDS BUT SHOULD FAIL WITH SQLCODE SQL20053 REASON CODE 2, SQLSTATE 428EA |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
For a hierarchy of views and subviews: Let BR1 and BR2 be any
branches that appear in the definitions of views in the
hierarchy. Let T1 be the underlying table or view of BR1, and
let T2 be the underlying table or view of BR2. Then:
* If T1 and T2 are not in the same hierarchy, then the root
view in the view hierarchy must specify the UNCHECKED option for
its OID column.
* If T1 and T2 are in the same hierarchy, then BR1 and BR2
must contain predicates or ONLY-clauses that are sufficient to
guarantee that their row-sets are disjoint.
The second requirement is not always correctly enforced, this
can happen if there is a predicate that compares two different
columns. For example:
CREATE VIEW v1 OF vt1 MODE DB2SQL (REF IS oid USER GENERATED)
AS SELECT vt1(VARCHAR(oid)), 1 FROM legacy
WHERE c1 = 40;
CREATE VIEW v2 OF vt2 MODE DB2SQL UNDER v1 INHERIT SELECT
PRIVILEGES
EXTEND AS SELECT 2 FROM legacy
WHERE c1 = 30 or c1 = c2;
The creation of v2 should be blocked with SQLSTATE 428EA but it
is not. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* For a hierarchy of views and subviews: Let BR1 and BR2 be *
* any branches that appear in the definitions of views in the *
* hierarchy. Let T1 be the underlying table or view of BR1, *
* and let T2 be the underlying table or view of BR2. Then: *
* *
* * If T1 and T2 are not in the same hierarchy, then the *
* root view in the view hierarchy must specify the UNCHECKED *
* option for its OID column. *
* * If T1 and T2 are in the same hierarchy, then BR1 and *
* BR2 must contain predicates or ONLY-clauses that are *
* sufficient to guarantee that their row-sets are disjoint. *
* *
* The second requirement is not always correctly enforced, *
* this can happen if there is a predicate that compares two *
* different columns. For example: *
* *
* CREATE VIEW v1 OF vt1 MODE DB2SQL (REF IS oid USER *
* GENERATED) *
* AS SELECT vt1(VARCHAR(oid)), 1 FROM legacy *
* WHERE c1 = 40; *
* *
* CREATE VIEW v2 OF vt2 MODE DB2SQL UNDER v1 INHERIT SELECT *
* PRIVILEGES *
* EXTEND AS SELECT 2 FROM legacy *
* WHERE c1 = 30 or c1 = c2; *
* *
* The creation of v2 should be blocked with SQLSTATE 428EA but *
* it is not. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 Version 9.7 Fix Pack 2 *
**************************************************************** |
| Local Fix: |
|
| available fix packs: |
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
|
| Solution |
Problem was first fixed in DB2 Version 9.7 Fix Pack 2 |
| Workaround |
not known / see Local fix |
| BUG-Tracking |
forerunner : APAR is sysrouted TO one or more of the following: IC67247
follow-up : |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 16.03.2010
08.04.2010
08.04.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP2
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.2
|
 |
