IBM Support

IC72698: INCORRECT RESULTS OR "SQL204N TABLE NOT FOUND" ERROR RETURNED WHEN SELECTING FROM VIEW.

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • After recreating some objects that are part of a view
    definition, it is possible that the view may not be correctly
    invalidated due to an inconsistency in the SYSIBM.SYSVIEWDEP
    system catalog table. This may lead to an incorrect SQL0204N
    error being returned to the user.
    
    Moreover, selecting from the view can also yield incorrect
    results whilst packages using the old view definition remain in
    the package cache.
    
    The example below, shows the sequence of events that lead to the
    problem identified in this APAR.
    
    create view B as select * from (values (cast (null as int), cast
    (null as int))) as "B" (d1, d2);
    create table A (c1 int, c2 int);
    insert into A values (1,2);
    
    create view ALL as select * from A,B where c1 = 1;
    select * from ALL;
    drop table A;
    create view A as select * from (values (cast (null as int), cast
    (null as int))) as "A" (c1, c2);
    select * from ALL;
    drop view A;
    select * from ALL;
    create table A (c1 int, c2 int);
    insert into A values (1,2);
    
    
    Re-running the query at this point will show 0 rows being
    returned since view ALL is still referring to the old reference
    of view A.
    
    select * from ALL;
    
    After clearing the package cache (either by deactivating the
    database or executing "flush package cache dynamic"), the same
    query will return the SQL0204N error.
    

Local fix

  • To avoid getting wrong results or ensure there are no active
    views subject to this problem, deactivate the database or run
    "flush package cache dynamic" to clear the database package
    cache.
    If there is a view that has the problem, it will cause SQL0204N
    to be returned from any statement accessing that view. To
    resolve the SQL0204N error, manually drop and recreate the view.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * all                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * When recreating tables that define a view, it is possible    *
    * that the view may return incorrect results as the view did   *
    * not get invalidated.                                         *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade the db2 server to v9.7 Fix Pack 4                    *
    ****************************************************************
    

Problem conclusion

  • The problem has first been fixed in v9.7 Fix Pack 4
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC72698

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2010-11-19

  • Closed date

    2011-04-28

  • Last modified date

    2011-04-28

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

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

    IC73615

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN UP

       IC72698

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.7","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
28 April 2011