Hitachi

Hitachi Advanced Database Setup and Operation Guide

11.4.15 Reorganizing a multi-chunk table: Reorganization of an entire table

This section describes how to reorganize an entire multi-chunk table.

Important

Note that if you reorganize an entire multi-chunk table (especially when the table has many data items), reorganization might take time. Therefore, we recommend that you periodically reorganize only the chunks that need to be reorganized rather than reorganizing the entire multi-chunk table.

You can use either of the following methods to perform reorganization of an entire multi-chunk table. Normally, use method 1 to perform reorganization of an entire multi-chunk table.

  1. Method for maintaining the chunk configuration after reorganization

    With this method, you can retrieve (but cannot update) the data in the table that is being reorganized. The configuration, statuses, and comments of the chunks before reorganization can be maintained.

    Note that this method requires both the reorganization-target data and reorganization-result data to be stored temporarily. Therefore, the data DB area that stores the reorganization-target table must have free space for storing both types of data. If the free space is insufficient, you cannot perform this method. Therefore, before you perform this method, use the adbdbstatus command to compare the size of the reorganization-target chunk with the size of free space in the data DB area that stores the table. For details about the adbdbstatus command, see adbdbstatus (Analyze the Database Status) in the manual HADB Command Reference.

    For details about reorganization using this method, see (1) In a case where you want to maintain the current chunk configuration after reorganization.

  2. Method for storing the table data to a single chunk after reorganization

    If you cannot use method 1 because the data DB area that stores the reorganization-target table does not have sufficient free space, use this method to perform reorganization of the table. With this method, you cannot retrieve or update the data in the table that is being reorganized. Because all data in the table is stored to a single chunk after reorganization, the configuration, statuses, and comments of the chunks cannot be maintained.

    For details about reorganization using this method, see (2) In a case where the table data can be stored to one chunk after reorganization.

    If only the current chunk is to be reorganized, the data DB area must have as large a free space as the data stored in the current chunk.

Organization of this subsection

(1) In a case where you want to maintain the current chunk configuration after reorganization

If you reorganize a table by using the method described in this section, the configuration, statuses, and comments of the chunks will be maintained in the resulting table. The chunk IDs before reorganization cannot be maintained.

The table reorganization procedure is as follows.

Procedure

  1. Make sure that no application programs or commands can update the reorganization-target table.

    Perform the following operations to prevent application programs and commands from updating the reorganization-target table:

    • If commands and jobs that update the reorganization-target table (such as the adbimport and adbmergechunk commands that are periodically executed) are executing on the HADB server machine, stop all of them.

    • Stop all application programs and commands that access the HADB server to update the reorganization-target table.

    We recommend that you perform both of the preceding operations.

  2. Obtain the information about all chunks in the reorganization-target table.

    Perform data retrieval in the system table STATUS_CHUNKS by using SQL statements to obtain the information about all chunks in the reorganization-target table. Obtain the following types of information:

    • CHUNK_ID (Chunk ID)

    • CHUNK_COMMENT (Comment set for a chunk)

    • CHUNK_STATUS (Chunk status)

    • CREATE_TIME (Creation date and time of a chunk)

    • SWAP_TIME (Date and time the current chunk was swapped)

    For examples of SQL statements that retrieve the preceding types of information, see (3) Checking the information about all chunks in a table based on a table name in C.9 Searching system tables.

    Important

    Check the current chunk. The current chunk is the chunk for which CREATE_TIME (creation date and time of a chunk) is set and SWAP_TIME (date and time the current chunk was switched) is a null value.

  3. Perform reorganization for each chunk.

    Repeat steps a to d for each chunk in the reorganization-target table, so that the table is reorganized on a chunk basis.

    Important

    Reorganize the current chunk last.

    1. Export data for each chunk.

      Execute the adbexport command with the -c option specified to export data for each chunk. For details about how to export data in units of chunks, see 11.4.5 Exporting data in units of chunks.

    2. Use the background-import facility to import the exported data as chunks in wait status.

      To perform background import of the data that was exported in step a, execute the adbimport command with the following options specified:

      • -b option

      • --status wait option

        If you execute the preceding command with the --status wait option specified, a chunk in wait status is created and data is stored in it.

      • -m option

        Specify the comment that is set for the chunk that you checked in step 2.

      For details about background import that creates a chunk in wait status, see 11.4.4 Temporarily excluding data to be imported to a multi-chunk table from retrieval (creating a chunk in wait status).

      Important

      When you execute the adbimport command, make sure that the value you specify for the import option (adb_import_rthd_num) satisfies the formula shown in (3) Estimating the value to be specified for the import option in 11.4.14 Reorganizing a multi-chunk table: Chunk-based reorganization. Specifying a value that does not satisfy the formula might adversely affect data storage efficiency.

    3. Change the chunk's status.

      If the reorganization-target chunk that you checked in step 2 is in wait status, you can skip this step. Proceed to step d.

      If the reorganization-target chunk that you checked in step 2 is in normal status, execute the adbchgchunkstatus command with the -w and -n options specified. Two chunk statuses will be changed simultaneously.

      • For the -w option, specify the chunk that stores the data exported in step a (reorganization-target chunk). The status of this chunk will change from normal status to wait status.

      • For the -n option, specify the chunk that was imported by background import in step b and is placed in wait status. The status of this chunk will change from wait status to normal status.

      For details about how to change the chunk status, see 11.4.12 Changing the chunk status.

    4. Delete the chunk subject to reorganization.

      Use the PURGE CHUNK statement to delete the data of the chunk that you exported in step a. Note that you cannot execute the PURGE CHUNK statement simultaneously with data retrieval from the multi-chunk table. Therefore, execute the PURGE CHUNK statement for the multi-chunk table when no operations are being performed for the table.

      For details about how to delete chunks by using the PURGE CHUNK statement, see 11.4.6 Deleting data in units of chunks.

  4. Make sure that the application programs and commands can update the reorganization-target table.

    After the operation in step 3 is completed for all chunks, use the following operation to allow the application programs and commands to update the reorganization-target table:

    • If you stopped commands and jobs running on the HADB server machine in step 1

      Restart the commands and jobs that were stopped.

    • If you stopped application programs and commands that access the HADB server in step 1

      Restart the application programs and commands that you stopped.

Important

Do not execute an update SQL statement or a command that updates a table (such as the adbimport or adbmergechunk command) for a table that is being reorganized. If you do so, the changes made by the SQL statement or command might be lost after reorganization.

To Page Top

(2) In a case where the table data can be stored to one chunk after reorganization

If you reorganize a table by using the method described in this subsection, the resulting table will have only one chunk. Therefore, the current configuration, statuses, comments and chunk IDs of the chunks cannot be maintained.

The table reorganization procedure is as follows.

Procedure

  1. Make sure that no application programs and commands can access the reorganization-target table.

    Perform the following operations to prevent application programs and commands from accessing the reorganization-target table:

    • Execute the adbchgsrvmode command with the --offline option specified to change the HADB server operation mode to offline mode. Furthermore, if commands and jobs that update the target table (such as the adbimport and adbmergechunk commands that are periodically executed) are executing on the HADB server machine, stop all of them.

    • Stop all application programs and commands that access the HADB server.

    We recommend that you perform both of the preceding operations.

  2. Export the data from the table.

    Export the data of the table by executing the adbexport command in which the reorganization-target table is specified for the -n option.

    For details about the adbexport command, see adbexport (Export Data) in the manual HADB Command Reference.

  3. Import the exported data in the creation mode.

    Execute the adbimport command with the -d option specified to import the data that was exported in step 2. If the -d option is specified, the data is imported in creation mode. All existing table data is deleted, and then the data exported in step 2 is imported.

    For details about the adbimport command, see adbimport (Import Data) in the manual HADB Command Reference.

    Important

    When you execute the adbimport command, make sure that the value you specify for the import option (adb_import_rthd_num) satisfies the formula shown in (3) Estimating the value to be specified for the import option in 11.4.14 Reorganizing a multi-chunk table: Chunk-based reorganization. Specifying a value that does not satisfy the formula might adversely affect data storage efficiency.

  4. Make sure that application programs and commands can access the reorganization-target table.

    Perform the following operation to enable application programs and commands to access the reorganization-target table:

    • If you changed the HADB server's operation mode to offline mode in step 1

      Execute the adbchgsrvmode command with the --normal option specified to change the HADB server operation mode to normal mode. If you stopped commands and jobs that were running on the HADB server machine, restart them.

    • If you stopped application programs and commands that access the HADB server in step 1

      Restart the application programs and commands that you stopped.

To Page Top

All Rights Reserved. Copyright (C) 2012, 2020, Hitachi, Ltd.