11.4.20 Changing a regular multi-chunk table to an archivable multi-chunk table
To change a regular multi-chunk table to an archivable multi-chunk table, execute the ALTER TABLE statement. The following shows the procedure.
- Important
-
You cannot change a regular multi-chunk table to an archivable multi-chunk table if the regular multi-chunk table is a column store table.
Procedure:
-
Check whether there are viewed tables that are to be invalidated.
If you change a regular multi-chunk table to an archivable multi-chunk table, the viewed tables that depend on the target table are invalidated. Therefore, before you change a regular multi-chunk table to an archivable multi-chunk table, check whether there are viewed tables that are to be invalidated. To check viewed tables that are to be invalidated (dependent viewed tables), see 11.2.11 Checking dependent viewed tables.
If there is a viewed table that is to be invalidated, after changing a regular multi-chunk table to an archivable multi-chunk table, you need to release the viewed table from invalidation.
-
Check the number of chunks that can be created in a data DB area.
Use the adbdbstatus command to output the DB area summary information of the data DB area in which the regular multi-chunk table is stored. Then, confirm that the value output to the following item is no less than 10:
-
Creatable_Chunks (Number of chunks that can be created in a DB area)
If the value is less than 10, you cannot change the regular multi-chunk table to an archivable multi-chunk table. When the table is changed to an archivable multi-chunk table, the number of chunks is consumed by the location table and the indexes for the location table that are automatically defined by the HADB server.
- Note
-
For details about the adbdbstatus command, see adbdbstatus (Analyze the Database Status) in the manual HADB Command Reference.
-
-
Consider and determine the column that you specify as the archive range column.
From the columns that compose the regular multi-chunk table, consider and determine a column that you specify as the archive range column. Consider which column to specify as the archive range column based on the explanation in (5) Specification of the archive range column under 5.2.5 Points to consider in defining an archivable multi-chunk table [Row store table].
-
Check whether a range index has been defined for the column that you specify as the archive range column.
After you determined the column that you specify as the archive range column in step 3, check whether a range index has been defined for the column that you specify as the archive range column. To determine whether a range index is defined, see (27) Finding out the range indexes defined for the archive range column in an archivable multi-chunk table in B.22 Searching a dictionary table.
-
Change the regular multi-chunk table to an archivable multi-chunk table.
Execute the ALTER TABLE statement to change the regular multi-chunk table to an archivable multi-chunk table. Note that how to specify the ALTER TABLE statement differs depending on the confirmation result in step 4.
-
When a range index has been defined for the column that you specify as the archive range column
Do not specify IN DB-area-name for the chunk-archive specification of the ALTER TABLE statement.
After execution of the ALTER TABLE statement is completed, go to step 7.
-
When no range index has been defined for the column that you specify as the archive range column
Specify IN DB-area-name for the chunk-archive specification of the ALTER TABLE statement. The range index automatically defined by the HADB server is stored in the DB area specified for IN DB-area-name. The range index is defined for the column that you specify as the archive range column.
After execution of the ALTER TABLE statement is completed, go to step 6.
- Note
-
For details about the ALTER TABLE statement, see ALTER TABLE (alter table definition) in the manual HADB SQL Reference.
-
-
Re-create the range index automatically defined by the HADB server.
If you specified IN DB-area-name for the chunk-archive specification of the ALTER TABLE statement in step 5, execute the adbidxrebuild command for the applicable archivable multi-chunk table. Execute the adbidxrebuild command to re-create the range index automatically defined by the HADB server.
After execution of the adbidxrebuild command is completed, go to step 7.
-
Re-validate viewed tables.
If there are viewed tables that depend on the target table, release the viewed tables from invalidation. For details about how to release a viewed table from invalid status, see (1) When viewed tables are invalidated by using an ALTER TABLE statement to change the table type in 11.2.8 Releasing a viewed table from invalidation.
Now, change of the regular multi-chunk table to an archivable multi-chunk table is completed.