|
Problem IT07230
|
Status: Closed |
AFTER A VIEW HAS BEEN ENABLED FOR OPTIMIZATION YOU MAY ENCOUNTER SQL0204N ERROR WHEN SELECTING FROM THE VIEW. |
| product: |
DB2 FOR LUW / DB2FORLUW / A10 - DB2 |
| Problem description: |
The specific steps to encounter this error are :
connect to sample
create table user1.foo like staff
set schema user1
create view v1 as select id,name from foo
terminate
set schema user2
alter view user1.v1 enable query optimization
select count(*) from user.v1
The last SELECT statement will return the error
SQL0204N "USER2.FOO" is an undefined name. SQLSTATE=42704
and invalidate the view USER1.V1. To resolve the problem the
view should be recreated. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* Users with views enabled for optimization. *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 UDB V10.1 FP5 *
**************************************************************** |
| Local Fix: |
Option 1: Fully qualify the table names in the view
- recreate the statistical view in which the tables are
referenced using fully qualified names.
- subsequent ALTER VIEW ... ENABLE QUERY OPTIMIZATION statements
can be performed under any schema name.
Option 2: Version 10.1.0.5 or later
- recreate the statistical view as-is; i.e., some tables in the
view are referenced without fully qualified names.
- subsequent ALTER VIEW ... ENABLE QUERY OPTIMIZATION statements
can be performed under any schema name.
Option 3:
- recreate the statistical view as-is; i.e., some tables in the
view are referenced without fully qualified names
- subsequent ALTER VIEW ... ENABLE QUERY OPTIMIZATION statements
must be performed under the original schema to avoid
encountering the issue again.
Example:
set schema user1
alter view user1.v1 enable query optimization
Options 1 and 2 are recommended. |
| Solution |
Upgrade to DB2 UDB V10.1 FP5 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 18.02.2015
13.07.2015
06.11.2015 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 10.1.0.5
|
 |
