IBM Support

IT06783: AFTER A VIEW HAS BEEN ENABLED FOR OPTIMIZATION YOU MAY ENCOUNTER SQL0204N ERROR WHEN SELECTING FROM THE VIEW.

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

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

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

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 LUW V9.7 FP11.                                *
    ****************************************************************
    

Problem conclusion

  • Upgrade to DB2 LUW V9.7 FP11.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT06783

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2015-01-27

  • Closed date

    2015-10-07

  • Last modified date

    2015-11-06

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

    IT07228 IT07229 IT07230 IT07232

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IT06783

Modified date: 06 November 2015