IBM Support

IZ39997: DROP TABLE FAILING WITH -901 WHICH HAD DEPENDENT STATISTICAL VIE WS

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • When attempting to drop a table, a -901 is returned referring
    to a dependent statistical view which has already been dropped.
    
    .
    This problem occurs when a DDL statement is issued against a
    base table for which statistical views based on this table need
    to be regenerated.
    .
    An example of this is when a column in the base table is
    dropped. Dropping the column requires all dependent views to
    be regenerated.  Statistical views can not beregenerated.
    Allowing this DDL on the base table will cause the statistical
    views to lose their reference to the base table. Since the
    reference is lost, dropping the view will not cleanup the
    corresponding reference to the view in the base table.
    Any attempt by the base table to find the dependent
    statistical views will fail with a SQL0901N.  This will
    happen, for instance, when attempting to drop the base
    table.
    .
    View regeneration of statistical views is not supported, and
    should have been blocked with sql code SQL0270N, reason
    code 70 which is what this APAR addresses.
    

Local fix

  • If altering a base table on which statistical views are
    dependent on:
           1. disable query optimization for all statistical views
          2. perform the alterations required on the base table
         3. enable query optimization for the views once again.
        If already at a state where you are unable to drop the
    tablebecause of a -901, contact IBM DB2 Service.
    

Problem summary

  • ****************************************************************
    USERS AFFECTED:
    All DB2 systems on all Linux, Unix and Windows platforms at
    service levels from Version 9.1 GA through to Version 9.1 Fix
    Pack 6.
    ****************************************************************
    PROBLEM DESCRIPTION:
    When trying to drop a table, a -901 is returned referring to a
    dependent statistical view which has already been dropped.
    .
    This problem occurs when a DDL statement is issued against a
    base table for which statistical views based on this table need
    to be regenerated.  An example of this is when a column in the
    base table is dropped. Since the statistical view regeneration
    was allowed to complete prior to this fix, if the view is
    dropped, the base tables packed descriptor information is not
    cleaned up.  This means that a subsequent drop of the table will
    fail with -901 as it is trying to find the view that has already
    been dropped.
    .
    View regeneration of statistical views is not supported, and
    should have been blocked with sql code SQL0270N, reason code 70
    which is what this APAR addresses.
    ****************************************************************
    RECOMMENDATION:
    Upgrade to DB2 Version 9.1 Fix Pack 7 or see "Local Fix" for
    workarounds.
    ****************************************************************
    

Problem conclusion

  • The complete fix for this problem first appears in DB2 Version
    9.1 Fix Pack 7 and all the subsequent Fix Packs.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IZ39997

  • Reported component name

    DB2 UDB ESE AIX

  • Reported component ID

    5765F4100

  • Reported release

    910

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2008-12-15

  • Closed date

    2009-03-11

  • Last modified date

    2009-03-11

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

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

    IZ40001

Fix information

  • Fixed component name

    DB2 UDB ESE AIX

  • Fixed component ID

    5765F4100

Applicable component levels

  • R910 PSN

       UP

[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"910"}]

Document Information

Modified date:
04 October 2021