Repeated data addition and deletion to a table affects the arrangement of rows in a table, resulting in reduced data access and storage efficiency. In this case, you can improve data access and storage efficiency by saving the table data in a file and then storing it back into the table. This is called reorganizing a table.

You can reorganize a table in units of tables or RDAREAs (for a row-partitioned table).

The figure below provides an overview of table reorganization.

Figure 8-2 Overview of table reorganization

• Unload data file
  The unload data file is used to temporarily save table data.
  
• Control information file
  The control information file contains pdrorg's control statements.
  The control statements are used to specify an unload data file, index information, and LOB column information.
  

If a table contains a LOB column, you can reorganize its LOB column structure base table and LOB column at the same time or separately.

A LOB column structure base table is the part of a table without the LOB column. A LOB column is a column of the BLOB data type.

If you reorganize a LOB column structure base table and the LOB column at the same time, specifying the -j option improves performance. Apply the reorganization with the -j option omitted when you reorganize either a LOB column structure base table or a LOB column.

If an abstract data type provided by a plug-in is stored in a user LOB RDAREA, you can choose to reorganize the abstract data type together with the LOB column structure base table or you can choose to not reorganize the abstract data type.

The figure below shows the procedure for reorganizing a table with a LOB column.

Figure 8-3 Reorganizing a table with a LOB column

Explanation:

Data for the LOB column structure base table (Columns A and B) is saved to an unload data file.

Data for the LOB column (Column C) is saved to a LOB data unload data file.

By specifying the -g and -j options, you can save both the LOB column structure base table data and the LOB column data to an unload data file.

An unload data file output by pdrorg is a temporary file. pdrorg unloads data without converting the character codes in a character set-defined column. Also during reloading, pdrorg stores data in the database without converting character codes. However, when used in conjunction with a UOC, pdrorg can perform character code conversion on the data in a column for which a character set has been defined. pdrorg then unloads the data to a file.

To reorganize compressed tables, pdrorg first unloads the compressed data from compressed columns, and then reloads them as is. If a UOC is used, pdrorg expands data in compressed columns during unloading, compresses the data during reloading, and then stores it because the UOC references the column data.

You can also reorganize tables with the FIX attribute for which reserved columns are defined.

When reorganizing a table in a HiRDB single server configuration, you can use a utility special unit. You can place an unload data file in the utility special unit. The figure below shows the procedure for using a utility special unit to reorganize a table.

Figure 8-4 Reorganizing a table using a utility special unit

When a table is reorganized, a transaction is normally settled after all data is reloaded. If the utility terminates abnormally during execution, the transaction rolls back to the start point, in which case you have to re-execute the utility from the beginning.

Reorganization with the synchronization point specification enables you to settle the transaction at every specified number of data items. This reduces the time required for rollback and re-execution in the event of abnormal termination.

Note that reorganization with the synchronization point specification is not applicable to unloading, batch index creation, or reorganization of LOB columns, in which case a transaction is settled when all processing is completed.

To execute reorganization with the synchronization point specification, specify the option statement (with the job operand). The figure below provides an overview of reorganization with the synchronization point specification.

Figure 8-5 Overview of reorganization with the synchronization point specification

Explanation:

At the first execution, rollback occurs after the occurrence of an error. Rollback is to the point of two million entries because the transaction has settled at that point.

The re-execution skips deletion of existing table data and data storage processing up to the point of two million entries, then stores the remaining data.

You can use a user-created program (UOC) for reorganization.

When you use a UOC, you can pass data retrieved from the database to the UOC for editing and then output the result, instead of directly saving the retrieved data to the unload data file.

For example, when a large amount of data is to be deleted, suppose that a UOC is used to check the data retrieved from the database to determine whether or not it is to be output to the unload data file. By reloading data from the resulting unload data, you can delete data in the format obtained immediately after reorganization; that is, when there are no scattered free pages (there are no used free pages). Additionally, by using a UOC for unloading, you can output data in a desired format.