11.2.8 Releasing a viewed table from invalidation

The way to release a viewed table from invalidation differs depending on the reason for the invalidation. The following explains the various ways.

Note

When you become unable to tell the reason for invalidation of the viewed table:: If you execute the ALTER VIEW statement on the viewed table, the ALTER VIEW statement returns an error. By referring to the error message that is output at this time, you can identify the reason for invalidation of the viewed table.

Organization of this subsection

(1) When viewed tables are invalidated by using an ALTER TABLE statement to change the table type
(2) When viewed tables are invalidated by using an ALTER TABLE statement to change a column name in a table
(3) When viewed tables are invalidated due to erroneous revocation of the SELECT privilege for an underlying table
(4) When viewed tables are invalidated due to erroneous deletion of a table
(5) When viewed tables are invalidated by using the ALTER VIEW statement to rebuild a viewed table that is the underlying table of those viewed tables
(6) When rebuilding of viewed tables at version upgrade fails

(1) When viewed tables are invalidated by using an ALTER TABLE statement to change the table type

In the following cases, all viewed tables that depend on the table whose type was changed are invalidated.

When a regular multi-chunk table is changed to an archivable multi-chunk table by the ALTER TABLE statement
When an archivable multi-chunk table is changed to a regular multi-chunk table by the ALTER TABLE statement

The following describes the procedure for releasing viewed tables from invalidation.

Example:: Because the CUSTOMER table was changed from a regular multi-chunk table to an archivable multi-chunk table, all viewed tables that depend on the CUSTOMER table have been invalidated. In this example, all viewed tables that depend on CUSTOMER table are released from invalidation.

Procedure:

Check a list of viewed tables that have been invalidated.

By retrieving data from a dictionary table, you can acquire a list of viewed tables that have been invalidated. For details about retrieving data from a dictionary table, see (33) Finding a list of dependent viewed tables in B.22 Searching a dictionary table.

In this example, assume that viewed tables CUSTOMER30s, CUSTOMER40s, and CUSTOMER50s have been invalidated.
Use the ALTER VIEW statement to rebuild the invalidated viewed tables.
```
ALTER VIEW "CUSTOMER30s" RECREATE
```
Also, use the ALTER VIEW statement to rebuild viewed tables CUSTOMER40s and CUSTOMER50s. You can release the viewed tables from invalidation.

Tip

If there are multiple viewed tables to be rebuilt, execute the ALTER VIEW statement in ascending order of view level.

To Page Top

(2) When viewed tables are invalidated by using an ALTER TABLE statement to change a column name in a table

If a column name in a table is changed by the ALTER TABLE statement, all viewed tables that depend on the table whose column name has been changed are invalidated. The following describes the procedure for releasing viewed tables from invalidation.

Note that the procedure for releasing viewed tables from invalidation differs, depending on whether the column name to be changed is specified in the CREATE VIEW statement.

Example:: Because a column name of the CUSTOMER table was changed, all viewed tables that depend on the CUSTOMER table have been invalidated. In this example, all viewed tables that depend on the CUSTOMER table are released from invalidation.

Procedure (when the column name to be changed has not been specified in the CREATE VIEW statement):

Check a list of viewed tables that have been invalidated.

By retrieving data from a dictionary table, you can acquire a list of viewed tables that have been invalidated. For details about retrieving data from a dictionary table, see (33) Finding a list of dependent viewed tables in B.22 Searching a dictionary table.

In this example, assume that viewed tables CUSTOMER30s, CUSTOMER40s, and CUSTOMER50s have been invalidated.
Use the ALTER VIEW statement to rebuild the invalidated viewed tables.
```
ALTER VIEW "CUSTOMER30s" RECREATE
```
Also, use the ALTER VIEW statement to rebuild viewed tables CUSTOMER40s and CUSTOMER50s. You can release the viewed tables from invalidation.

Tip

If there are multiple viewed tables to be rebuilt, execute the ALTER VIEW statement in ascending order of view level.

Procedure (when the column name to be changed has been specified in the CREATE VIEW statement):

Check the list of invalidated viewed tables and their definition information.

By retrieving data from a dictionary table, you can obtain a list of invalidated viewed tables and their definition information. For details about retrieving data from a dictionary table, see (32) Finding a list of dependent viewed tables, and the definition information of each of the viewed tables in B.22 Searching a dictionary table.

In this example, assume that viewed tables CUSTOMER30s, CUSTOMER40s, and CUSTOMER50s have been invalidated.
Use the DROP VIEW statement to delete the invalidated viewed tables.
```
DROP VIEW "CUSTOMER30s"
DROP VIEW "CUSTOMER40s"
DROP VIEW "CUSTOMER50s"
```
Delete all the invalidated viewed tables that you checked in step 1.
Modify the contents specified in the CREATE VIEW statement.

Of the specified contents of the CREATE VIEW statement you checked in step 1, modify the column name that was changed to the new name.

Example: When the column name of the T1 table is changed from C3 to C33:

<Before modification>
```
CREATE VIEW "VT1" ("VC1","VC2","VC3")
    AS SELECT "C1"."C2","C3" FROM "T1"
```
<After modification>
```
CREATE VIEW "VT1" ("VC1","VC2","VC3")
    AS SELECT "C1"."C2","C33" FROM "T1"
```

Use the CREATE VIEW statement to redefine the invalidated viewed tables.

CREATE VIEW "CUSTOMER30s" ("AGE","SEX","GNO","PNAME","PRICE")
  AS SELECT "CUSTOMER"."AGE",
            "CUSTOMER"."SEX",
            "CUSTOMER"."GNO",
            "STOCK"."PNAME",
            "STOCK"."PRICE"
       FROM "CUSTOMER","STOCK"
         WHERE "CUSTOMER"."AGE"=30 AND "CUSTOMER"."GNO"="STOCK"."GNO"

In the same way, for CUSTOMER40s and CUSTOMER50s, also use the CREATE VIEW statement to redefine the viewed tables.

Tip: If there are multiple viewed tables to be redefined, execute the CREATE VIEW statement in ascending order of view level.

To Page Top

(3) When viewed tables are invalidated due to erroneous revocation of the SELECT privilege for an underlying table

If the SELECT privilege for an underlying table is erroneously revoked, all viewed tables that depend on the underlying table are invalidated. The following describes the procedure for releasing viewed tables from invalidation.

Example:: Because the SELECT privilege for the underlying table was erroneously revoked, all viewed tables that depend on the underlying table have been invalidated. In this example, the relevant viewed tables and underlying table are identified, and all viewed tables that depend on the underlying table are released from invalidation.

Procedure:

Check a list of viewed tables that have been invalidated.

By retrieving data from a dictionary table, you can acquire a list of viewed tables that have been invalidated. For details about retrieving data from a dictionary table, see (33) Finding a list of dependent viewed tables in B.22 Searching a dictionary table.

In this example, assume that viewed table CUSTOMER30s has been invalidated.
Check the underlying table for the viewed tables that have been invalidated.

By retrieving data from a dictionary table based on the invalidated viewed tables identified in step 1, you can acquire the underlying table of the viewed tables that have been invalidated. For details about retrieving data from a dictionary table, see (35) Checking which base tables and viewed tables are underlying tables of a viewed table in B.22 Searching a dictionary table.

This example assumes that the underlying table of the invalidated viewed table CUSTOMER30s is the CUSTOMER table.
Execute the GRANT statement to grant the SELECT privilege for the underlying table.

For underlying table CUSTOMER identified in step 2, execute the GRANT statement to grant the SELECT privilege.
```
GRANT SELECT ON "CUSTOMER" TO "ADBUSER01"
```
The underlined part indicates the authorization identifier of the HADB user who owns viewed table CUSTOMER30s.
Use the ALTER VIEW statement to rebuild the invalidated viewed tables.
```
ALTER VIEW "CUSTOMER30s" RECREATE
```
Execute the ALTER VIEW statement to rebuild viewed table CUSTOMER30s. You can release the viewed tables from invalidation.

Tip

If there are multiple viewed tables to be rebuilt, execute the ALTER VIEW statement in ascending order of view level.

To Page Top

(4) When viewed tables are invalidated due to erroneous deletion of a table

If a table is erroneously deleted^#, all viewed tables that depend on the deleted table are invalidated. The following describes the procedure for releasing viewed tables from invalidation.

#: This case includes when the DROP TABLE or DROP VIEW statement for which specification of drop behavior is omitted is executed.

Example:: Because base table CUSTOMER is erroneously deleted, all viewed tables that depend on the deleted table are invalidated. In this example, the base table is redefined, and all viewed tables that depend on the base table are released from invalidation.

Procedure:

Check a list of viewed tables that have been invalidated.

By retrieving data from a dictionary table, you can acquire a list of viewed tables that have been invalidated. For details about retrieving data from a dictionary table, see (33) Finding a list of dependent viewed tables in B.22 Searching a dictionary table.

In this example, assume that viewed table CUSTOMER30s has been invalidated.
Redefine the table that was erroneously deleted.
- If the table that was erroneously deleted is a base table, redefine the base table by using the CREATE TABLE statement.
- If the table that was erroneously deleted is a viewed table, redefine the viewed table by using the CREATE VIEW statement.
In this example, base table CUSTOMER is redefined by using the CREATE TABLE statement.
Ask another HADB user to execute the GRANT statement to grant to you the SELECT privilege for the table that was redefined by that user.

If the table redefined in step 2 is owned by another HADB user, ask that user to execute the GRANT statement to grant to you the SELECT privilege for that table.

Note that this step is not necessary if you redefined the table by yourself.
```
GRANT SELECT ON "CUSTOMER" TO "ADBUSER01"
```
The underlined part indicates the authorization identifier of the HADB user who owns viewed table CUSTOMER30s.
Use the ALTER VIEW statement to rebuild the invalidated viewed tables.
```
ALTER VIEW "CUSTOMER30s" RECREATE
```
Execute the ALTER VIEW statement to rebuild viewed table CUSTOMER30s. You can release the viewed tables from invalidation.

Tip

If there are multiple viewed tables to be rebuilt, execute the ALTER VIEW statement in ascending order of view level.

To Page Top

(5) When viewed tables are invalidated by using the ALTER VIEW statement to rebuild a viewed table that is the underlying table of those viewed tables

If you execute the ALTER VIEW statement to rebuild a viewed table that is the underlying table of viewed tables, all viewed tables that depend on that rebuilt viewed table are invalidated. The following describes the procedure for releasing viewed tables from invalidation.

Note that the procedure for releasing viewed tables from invalidation differs, depending on whether a column name has been changed in a viewed table (underlying table), or whether the column name to be changed has been specified in the CREATE VIEW statement.

Procedure (when a column name has not been changed in a viewed table (underlying table), or when the column name to be changed has not been specified in the CREATE VIEW statement):

If a column name of the viewed table (underlying table) has not been changed, release the viewed table from invalidation by the following procedure. If a column name of the viewed table (underlying table) has been changed, but the column name to be changed has not been specified in the CREATE VIEW statement, also release the viewed table from invalidation by the following procedure.

Check a list of viewed tables that have been invalidated.

By retrieving data from a dictionary table, you can acquire a list of viewed tables that have been invalidated. For details about retrieving data from a dictionary table, see (33) Finding a list of dependent viewed tables in B.22 Searching a dictionary table.

In this example, assume that viewed tables CUSTOMER30s, CUSTOMER40s, and CUSTOMER50s have been invalidated.
Use the ALTER VIEW statement to rebuild the invalidated viewed tables.
```
ALTER VIEW "CUSTOMER30s" RECREATE
```
In the same way, use the ALTER VIEW statement to rebuild viewed tables CUSTOMER40s and CUSTOMER50s. You can release the viewed tables from invalidation.

Tip

If there are multiple viewed tables to be rebuilt, execute the ALTER VIEW statement in ascending order of view level.

Procedure (when the column name to be changed has been specified in the CREATE VIEW statement):

If a column name of the viewed table (underlying table) has been changed and the column name to be changed has been specified in the CREATE VIEW statement, release the viewed tables from invalidation by the following procedure.

Check a list of invalidated viewed tables and their definition information.

By retrieving data from a dictionary table, you can acquire a list of invalidated viewed tables and their definition information. For details about retrieving data from a dictionary table, see (32) Finding a list of dependent viewed tables, and the definition information of each of the viewed tables in B.22 Searching a dictionary table.

In this example, assume that viewed tables CUSTOMER30s, CUSTOMER40s, and CUSTOMER50s have been invalidated.
Use the DROP VIEW statement to delete the invalidated viewed tables.
```
DROP VIEW "CUSTOMER30s"
DROP VIEW "CUSTOMER40s"
DROP VIEW "CUSTOMER50s"
```
Delete all the invalidated viewed tables that you checked in step 1.
Modify the contents specified in the CREATE VIEW statement.

Of the specified contents of the CREATE VIEW statement you checked in step 1, modify the column name that was changed.

Example: When a column name of the T1 table is changed from C3 to C33:

<Before modification>
```
CREATE VIEW "VT1" ("VC1","VC2","VC3")
    AS SELECT "C1"."C2","C3" FROM "T1"
```
<After modification>
```
CREATE VIEW "VT1" ("VC1","VC2","VC3")
    AS SELECT "C1"."C2","C33" FROM "T1"
```

Use the CREATE VIEW statement to redefine the invalidated viewed tables.

CREATE VIEW "CUSTOMER30s" ("AGE","SEX","GNO","PNAME","PRICE")
  AS SELECT "CUSTOMER"."AGE",
            "CUSTOMER"."SEX",
            "CUSTOMER"."GNO",
            "STOCK"."PNAME",
            "STOCK"."PRICE"
       FROM "CUSTOMER","STOCK"
         WHERE "CUSTOMER"."AGE"=30 AND "CUSTOMER"."GNO"="STOCK"."GNO"

In the same way, use the CREATE VIEW statement to redefine viewed tables CUSTOMER40s and CUSTOMER50s.

Tip: If multiple viewed tables are to be redefined, execute the CREATE VIEW statement in ascending order of view level.

To Page Top

(6) When rebuilding of viewed tables at version upgrade fails

When version upgrade of the HADB server is performed, viewed tables might be rebuilt. In that case, if the rebuilding of viewed tables fails, the target viewed tables are invalidated. For details, see (5) Re-creation of viewed tables in the event of a version upgrade in 8.6.5 Notes on version upgrading.

The following describes the procedure for releasing the viewed tables (for which rebuilding failed) from invalidation.

Procedure:

Check a list of invalidated viewed tables and their definition information.

By retrieving data from a dictionary table, you can acquire a list of invalidated viewed tables and their definition information. For details about retrieving data from a dictionary table, see (32) Finding a list of dependent viewed tables, and the definition information of each of the viewed tables in B.22 Searching a dictionary table.
Use the DROP VIEW statement to delete the invalidated viewed tables.

Delete all the invalidated viewed tables that you checked in step 1.
Create the CREATE VIEW statement.

Create the CREATE VIEW statement for the invalidated viewed tables that you checked in step 1.
Based on the output message, modify the contents specified in the CREATE VIEW statement.

Modify the CREATE VIEW statement you created in step 3, and eliminate the reason for invalidation of the viewed tables.

If rebuilding of viewed tables at version upgrade fails, the KFAA51312-W message is output, indicating that rebuilding of viewed tables failed. In the message that is output immediately before the KFAA51312-W message, the reason for invalidation of the viewed tables and the steps to take are explained. According to the message, modify the CREATE VIEW statement.
Use the CREATE VIEW statement to redefine the invalidated viewed tables.

Use the CREATE VIEW statement that you modified in step 4 to redefine the invalidated viewed tables.

Tip

If multiple viewed tables are to be redefined, execute the CREATE VIEW statement in ascending order of view level.

To Page Top