IBM Support

IC78261: ADMIN_DROP_SCHEMA FAILED WHEN AUTO_REVAL IS DISABLED AND ONLY INOPERATIVE OBJECTS EXIST IN A SCHEMA

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as fixed if next.

Error description

  • When only inoperative objects exist in a schema, the stored
    procedure ADMIN_DROP_SCHEMA does not remove the schema and
    dependent objects correctly.
    
    Below is the script that creates a database and tests
    ADMIN_DROP_SCHEMA. It first creates a table and a view, then
    drops the table. Dropping the schema using admin_drop_schema
    results in an error. However, the view and schema are removed.
    
    Then the script does the same test but with AUTO_REVAL disabled.
    This gives another error, and does not remove the view.
    
    --drop database tmp;
    create database tmp;
    connect to tmp;
    call sysproc.sysinstallobjects('DB2AC', 'C', null, null);
    create table sven.test(x integer not null, y integer);
    create view sven.v as select * from sven.test;
    drop table sven.test;
    call sysproc.admin_drop_schema('SVEN', null, 'E', 'E');
    select * from e.e;
    list tables for schema sven;
    
    connect reset;
    update db cfg for tmp using auto_reval disabled;
    connect to tmp;
    create table sven2.test(x integer not null, y integer);
    create view sven2.v as select * from sven2.test;
    drop table sven2.test;
    call sysproc.admin_drop_schema('SVEN2', null, 'E2', 'E2');
    select * from e2.e2;
    list tables for schema sven2;
    

Local fix

  • Drop underlying objects and the schema manually using
    corresponding DROP statements.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * DB2 for LUW                                                  *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Drop underlying objects and the schema manually using        *
    * corresponding DROP statements.                               *
    ****************************************************************
    

Problem conclusion

Temporary fix

Comments

APAR Information

  • APAR number

    IC78261

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED FIN

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2011-08-23

  • Closed date

    2012-06-06

  • Last modified date

    2012-06-06

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

    IC78245

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

Fix information

Applicable component levels

  • R950 PSN

       UP

  • R950 PSY

       UP

  • R970 PSN

       UP

  • R970 PSY

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC78261

Modified date: 06 June 2012