11.10.6 Securing free space in a data DB area

This subsection describes how to secure free space in a data DB area.

Important: Before you begin to secure free space in a data DB area, check whether expansion of the data DB area is possible. For details about how to expand a data DB area, see 11.10.3 Expanding a data DB area (adding a data DB area file).

If expansion of the data DB area is impossible, use Action 1 or 2 (explained later) to secure free space in the data DB area. If you still cannot secure free space in the data DB area by performing Action 1 or 2, perform Action 3.

Action 1

Archive chunks. Compress the data in chunks to secure free space in the data DB area.

For details, see (1) Securing free space in a data DB area (compressing data).

Action 2

Reorganize the base table. Secure free space in the data DB area by deleting invalid areas or compressing data.

For details, see (2) Securing free space in a data DB area (reorganizing the base table).

Action 3

Delete unnecessary data. Delete unnecessary data to secure free space in the data DB area.

For details, see (3) Securing free space in a data DB area (deleting unnecessary data).

Organization of this subsection

(1) Securing free space in a data DB area (compressing data)
(2) Securing free space in a data DB area (reorganizing the base table)
(3) Securing free space in a data DB area (deleting unnecessary data)

(1) Securing free space in a data DB area (compressing data)

If you want to secure free space in a data DB area that contains an archivable multi-chunk table, you can try to archive chunks. If you use the adbarchivechunk command, which archives chunks, you might be able to increase the amount of free space in the data DB area by compressing the data in the chunks.

Note: For details about how to archive chunks, see 2.15.1 Overview of the chunk archiving function.

The following shows the procedure for compressing the data in chunks.

Procedure

Check the schema name and table identifier of the archivable multi-chunk table.

Execute the SELECT statement shown later to check the schema name and table identifier of the archivable multi-chunk table stored in the data DB area.

Specification example

SELECT "MASTER"."SQL_DIV_TABLE"."TABLE_SCHEMA",
        "MASTER"."SQL_DIV_TABLE"."TABLE_NAME"
  FROM "MASTER"."SQL_DIV_TABLE","MASTER"."SQL_TABLES"
    WHERE "DBAREA_ID" = (SELECT "DBAREA_ID"
                              FROM "MASTER"."SQL_DBAREAS"
                                WHERE "DBAREA_NAME" = ?)
      AND "MASTER"."SQL_DIV_TABLE"."TABLE_SCHEMA"
             = "MASTER"."SQL_TABLES"."TABLE_SCHEMA"
      AND "MASTER"."SQL_DIV_TABLE"."TABLE_NAME"
             = "MASTER"."SQL_TABLES"."TABLE_NAME"
      AND "IS_ARCHIVABLE" = 'Y'

If you performed the preceding SELECT statements by using the adbsql command, you will be prompted to enter the input data for the dynamic parameters. In response to the prompt, enter the DB area name of the data DB area in which you want to secure free space.

Determine the chunks to be archived.

Archive chunks to secure free space in the data DB area. Of the chunks that are not in the archived state in the archivable multi-chunk table, determine the chunks to be archived.

Note that retrieval from archived chunks takes a longer time than retrieval from non-archived chunks. Therefore, we recommend that you archive chunks for which data retrieval occurs less frequently. For example, if retrieval for later created chunks occurs more frequently than retrieval for earlier created chunks, archive earlier created chunks.

To check the information about the chunks to be archived, execute the adbdbstatus command with the -d used option specified, and then check the information about the usage of DB areas, tables, and indexes in the output results. Alternatively, use the SELECT statement to search system table STATUS_CHUNKS. For details, see (1) Executing the adbdbstatus command or (3) Searching the STATUS_CHUNKS system table in 11.4.8 Checking the chunk status and the number of chunks created.
Check the size of free space that can be secured by archiving chunks.

By archiving chunks, you can secure free space (of the size that those chunks occupy) in the data DB area. To check the size of free space that can be secured by archiving chunks, execute the adbdbstatus command with the -d used option specified. When the information about the usage of DB areas, tables, and indexes is output, check the content of Used_segments and Used_pages based on Chunk_ID.

For details, see (1) Executing the adbdbstatus command in 11.4.8 Checking the chunk status and the number of chunks created.
Archive chunks to compress data.

Execute the adbarchivechunk command to archive chunks (compress the data in chunks). For details about how to archive chunks, see 11.4.17 Archiving chunks (when using an archivable multi-chunk table).

To Page Top

(2) Securing free space in a data DB area (reorganizing the base table)

If you have repeatedly performed any of the following operations for a base table, try reorganizing the base table. You might be able to increase the amount of free space in the data DB area.

Using an SQL statement to perform update or deletion of rows for a row store table
Using an SQL statement to perform addition, update, or deletion of rows for a column store table
Using the adbimport command to perform background import for a multi-chunk table

Note

Reasons why the free space in a data DB area can be increased by reorganizing the base table are as follows:

Repeated update or deletion of rows by using an SQL statement or repeated execution of background import by using the adbimport command might increase invalid row data or non-reusable free space. When you reorganize the base table, such invalid row data or non-reusable free space will be released. This might increase the amount of free space in the data DB area.
If an SQL statement is used to add rows to a column store table, the rows will be added in row store format rather than in column store format. Data added in row store format is not compressed. When you reorganize the base table or indexes, data will be added in column store format. This might increase the amount of free space in the data DB area because the data added in column store format is compressed.

The following shows the procedure for reorganizing a base table.

Procedure

Check the size of free space that can be secured.

Use the following calculation expression to estimate the size of free space that can be secured by reorganizing the base table.
Expression for calculating the size of free space that can be secured by reorganizing the base table (megabytes)
```
Size of free space that can be secured by reorganizing the base table =
    space-usage-for-base-table-before-reorganization - space-usage-for-base-table-after-reorganization
```
space-usage-for-base-table-before-reorganization (megabytes)

You can check space-usage-for-base-table-before-reorganization by referring to (2) Checking the usage of a base table in 10.9.2 Checking the status and usage of a base table.

space-usage-for-base-table-after-reorganization (megabytes)

Estimate this value by referring to 5.8 Estimating the size of the data DB area. Note that the value obtained from the calculation formula in the section or subsection you reference is in kilobytes. Therefore, you must convert the value in megabytes.

Also note that when a base table is reorganized, the indexes defined for the base table are also reorganized. Use the following calculation expression to estimate the size of free space that can be secured by reorganizing indexes.
Expression for calculating the size of free space that can be secured by reorganizing indexes (megabytes)
```
Size of free space that can be secured by reorganizing indexes =
    space-usage-for-indexes-before-reorganization - space-usage-for-indexes-after-reorganization
```
space-usage-for-indexes-before-reorganization (megabytes)
You can check space-usage-for-indexes-before-reorganization by referring to the following subsections:
space-usage-for-indexes-after-reorganization (megabytes)

Estimate this value by referring to 5.8 Estimating the size of the data DB area. Note that the value obtained from the calculation formula in the section or subsection you reference is in kilobytes. Therefore, you must convert the value in megabytes.
Reorganize the base table.

Reorganize the base table by referring to the following subsections. Note that when the base table is reorganized, the indexes defined for the base table are also reorganized. You do not need to obtain the data storage efficiency.

Important

If the target table is a multi-chunk table that contains unnecessary chunks, try deleting the unnecessary chunks before performing reorganization. For details about how to delete unnecessary chunks, see (3) Securing free space in a data DB area (deleting unnecessary data).
While a base table is being reorganized, the data in the base table is temporarily deleted. Therefore, you cannot view data in a base table that is being reorganized.
If the target base table stores a large amount of data, the size of data to be exported during reorganization becomes large. Also, the time required for reorganization becomes longer. If there is not enough space for exporting data or time for reorganization, try securing free space in the data DB area by using another method.

To Page Top

(3) Securing free space in a data DB area (deleting unnecessary data)

This subsection describes how to secure free space in a data DB area by deleting unnecessary data.

If you cannot secure free space in a data DB area by using the methods described in the following subsections, try deleting unnecessary data.

(1) Securing free space in a data DB area (compressing data)
(2) Securing free space in a data DB area (reorganizing the base table)

You can secure free space in a data DB area by deleting the following types of unnecessary data:

Unnecessary indexes
Unnecessary chunks in a multi-chunk table

Important

Deleting unnecessary data carries the following risks:

Loss of data due to an incorrect operation
Degradation in performance of SQL statements and commands

Therefore, before performing such a deletion procedure, read the notes that apply to the procedure.

■ If there are unnecessary indexes in the data DB area in which free space is to be secured

You can secure free space by deleting unnecessary indexes. The following shows the procedure.

Procedure:

Determine the indexes to be deleted.

When you determine the indexes to be deleted, we recommend that you check the usage status of indexes in the access path statistical information.

If SQL tracing is enabled, the access path statistical information is output each time an SQL statement is executed, and you can check whether the SQL statement used indexes from that information. Delete indexes that are not used by SQL statements that need to be executed.

For details about SQL tracing, see 10.11 Running SQL tracing. For details about access path statistical information, see 10.11.3 Examples of output of and output items for access path statistical information.
Check the size of free space that can be secured.

You can increase the free space in the data DB area by the size of space that is used for the indexes to be deleted. You can check the space usage for indexes by referring to the following subsections:
Delete the indexes.

Delete unnecessary indexes by referring to 11.3.9 Deleting an index.

Notes

Deleting indexes might degrade the performance of SQL statements that use the indexes.
In case you need to later redefine indexes that are to be deleted, obtain the definition information that is required to do so before deleting them. For details about how to obtain definition information, see (29) Finding out index definition information in B.22 Searching a dictionary table.

■ If there is a multi-chunk table with unnecessary chunks in the data DB area in which free space is to be secured

You can secure free space by using the PURGE CHUNK statement to delete unnecessary chunks in a multi-chunk table. The following shows the procedure.

Procedure:

Determine the chunks to be deleted.

Check the status of each chunk in the multi-chunk table to determine the chunks to be deleted. For example, you can first delete the following types of chunks, which are more likely to be unnecessary:
- Deletion-pending chunks
- Unarchived chunks in wait status
- Chunks that are seldom searched (such as chunks created a long time ago)
To check the information about chunks, execute the adbdbstatus command with the -d used option specified, and then check the information about the usage of DB areas, tables, and indexes in the output results. Alternatively, use the SELECT statement to search system table STATUS_CHUNKS. For details, see (1) Executing the adbdbstatus command or (3) Searching the STATUS_CHUNKS system table in 11.4.8 Checking the chunk status and the number of chunks created.
Check the size of free space that can be secured.

You can increase the free space in the data DB area by the size of space that is used for the chunks to be deleted. To check the size of space used for chunks, execute the adbdbstatus command with the -d used option specified. When the information about the usage of DB areas, tables, and indexes is output, check the content of Used_segments and Used_pages based on Chunk_ID.

For details, see (1) Executing the adbdbstatus command in 11.4.8 Checking the chunk status and the number of chunks created.
Delete chunks.

Delete unnecessary chunks by referring to 11.4.6 Deleting data in units of chunks.

Notes

If the chunks to be deleted contain data that might need to be accessed later, export the chunks before deletion. For details about how to export the data in a chunk, see 11.4.5 Exporting data in units of chunks.
You cannot increase the free space in the data DB area by deleting archived chunks.

To Page Top