IBM Support

Check database for obsolete views

Question & Answer


Question

How can I identify obsoleted views? 

Answer

When a base table changes, any views associated with it may need to be rebuilt to account for those changes. Dropping a table or view and re-creating it without any changes will not require rebuilding the associated views. Modifying data types, however, does require rebuilding the views. Also, if you have dropped an object on which the view is dependent (as shown below), you must rebuild the views.

    MYDATABASE(ADMIN)=> create table table1 (int1 integer);
    CREATE TABLE
    MYDATABASE(ADMIN)=> create view view1 as select * from table1;
    CREATE VIEW
    MYDATABASE(ADMIN)=> drop table table1;
    DROP TABLE
    MYDATABASE(ADMIN)=> create table table1 (int1 smallint);
    CREATE TABLE
    MYDATABASE(ADMIN)=> select * from view1;
    ERROR:  Base table/view 'TABLE1' attr 'INT1' has changed (datatype); rebuild view 'VIEW1'

If TABLE1 was referenced in a production ETL job, this action would have interrupted processing and the job would have failed. To prevent this problem, you can use the nz_check_views script to check every database for obsoleted views. The script is available in the /nz/support/contrib/<release>/bin directory.
    [nz@cs-spubox2 bin]$ nz_check_views
    Database: $mydb                 # Of Views:   0
    Database: WAREHOUSE_SHADOW   # Of Views: 212
    Database: MYDATABASE2              # Of Views:   2

    Database:  MYDATABASE2
        View:  MYVIEW2
       ERROR:  Relation 'MYTAB2' does not exist

    Database: MYDATABASE          # Of Views:   0


To check for obsoleted views in a single database, specify the target database as shown below:
    [nz@cs-spubox2 bin]$ nz_check_views mydatabase
    Database: MYDATABASE            # Of Views:   2

    Database:  MYDATABASE
        View:  VIEW1
       ERROR:  Base table/view 'TABLE1' attr 'INT1' has changed (datatype); rebuild view 'VIEW1'

    Database:  MYDATABASE
        View:  VIEW2
       ERROR:  Relation 'TABLE2' does not exist


You can use the -replace option to rebuild the view using 'CREATE OR REPLACE' as shown in the following example:
    [nz@cs-spubox2 bin]$ nz_check_views mydatabase -replace yes
    Database: MYDATABASE            # Of Views:   2

    Database:  MYDATABASE
        View:  VIEW1
       ERROR:  Base table/view 'TABLE1' attr 'INT1' has changed (datatype); rebuild view 'VIEW1'

    ***** Attempting to do a 'create or replace view ...'
    ***** Creating view: "VIEW1"
    CREATE VIEW

    Database:  MYDATABASE
        View:  VIEW2
       ERROR:  Relation 'TABLE2' does not exist

    ***** Attempting to do a 'create or replace view ...'
    ***** Creating view: "VIEW2"
    ERROR:  Relation 'TABLE2' does not exist
In this example, the first view was successfully rebuilt. In the second view, because the underlying table did not exist, the view could not be rebuilt and thus is still invalid.

[{"Product":{"code":"SSULQD","label":"IBM PureData System"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":null,"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"1.0.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Historical Number

NZ494579

Document Information

Modified date:
17 October 2019

UID

swg21570135

Page Feedback