IBM Support

IZ40001: 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.5 GA through to Version 9.5    *
    * Fix Pack 3.                                                  *
    ****************************************************************
    * PROBLEM 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.                   *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 Version 9.5 Fix Pack 4 or see "Local Fix" for *
    * simple workaround.                                           *
    ****************************************************************
    

Problem conclusion

  • The complete fix for this problem first appears in DB2 Version
    9.5 Fix Pack 4 and all subsequent Fix Packs
    

Temporary fix

Comments

APAR Information

  • APAR number

    IZ40001

  • Reported component name

    DB2 UDB ESE AIX

  • Reported component ID

    5765F4100

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2008-12-15

  • Closed date

    2009-05-29

  • Last modified date

    2009-05-29

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

    IZ39997

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

Fix information

  • Fixed component name

    DB2 UDB ESE AIX

  • Fixed component ID

    5765F4100

Applicable component levels

  • R950 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":"950"}]

Document Information

Modified date:
04 October 2021