When a large amount of table data is deleted by a batch job or some other operation, some of the pages in which table data is being stored (data pages) might become used free pages. Similarly, when an index is defined, some of the pages in which index key values are being stored (index pages) also become used free pages. By executing the free page release utility (pdreclaim command), you can convert used free pages to unused pages and reuse them. This is called releasing used free pages. The following figure provides an overview of releasing used free pages.

Figure 15-3 Releasing used free pages

Hint

• You cannot release used free pages of data stored in LOB RDAREAs.
• You cannot release used free pages of plug-in indexes.

(a) Benefits of releasing used free pages of tables

The following table lists and describes the benefits of releasing used free pages of a table.

Table 15-9 Benefits of releasing used free pages of a table

Benefit	Description	Degree of benefit
Ability to increase the table reorganization cycle	The ability to reuse used free pages improves data storage efficiency. In turn, this can increase (improve) the table reorganization time cycle.	Good
Improvement in performance for searching large data sets	Because they are a type of used page, used free pages are searched. However, searching is not performed on unused pages (they are skipped by a search). Thus, converting to unused pages improves search performance in direct proportion to the ratio converted. The benefits are particularly evident when large data sets are searched.	Varies
Improvement in performance when INSERT and UPDATE are used.	If sufficient contiguous free space cannot be allocated when an attempt is made to save data in a used page, HiRDB performs a process called page compaction. In page compaction, data in the affected page is repacked to secure sufficient contiguous free space to enable storage of the new data. When used free pages are released, page compaction is performed at the same time. This eliminates the need to perform page compaction, which prolongs INSERT and DELETE processing, and improves performance by the corresponding amount. The pages on which page compaction is performed are used pages other than full pages and used free pages.	Varies
Potential reduction in errors when INSERT and UPDATE are performed on branch rows.	If you execute INSERT or UPDATE on a branch row when there are no unused pages, an error occurs (KFPA11756-E message). The increase in unused pages due to the release of used free pages tends to reduce the frequency of this error.	Varies

Legend:

Good: Always beneficial.

Varies: The degree of benefit varies depending on conditions.

It is particularly beneficial to apply this facility to tables that satisfy all of the conditions shown in the following table.

Table 15-10 Tables that benefit from release of used free pages

Condition	Reason
A table that has no updates of variable-length character strings.	Because branch rows do not occur in such tables, the probability of being able to release used free pages increases. Branch rows are stored on separate pages from their base rows, and base rows of other data are also stored on those pages. This means that, if there are branch rows, even if all rows of a particular value (such as registered duration) are deleted, the number of pages in which data is stored might not be reduced to zero. However, if there are no branch rows, when data inserted during a certain period is deleted, because the space in that contiguous area (page) becomes free, the probability of being able to release used free pages increases.
A table that has no changes in the number of repetition column elements.
A table that has no changes of NULL values to real data or real data to NULL values.
A table that has no cluster key index defined.	Because the storage position of a data item is determined from its key value when a cluster key index is defined, even if used free pages are released, the possibility that those pages will not be used is high, and little benefit will be achieved.
A table that has no LOB data.	This facility cannot be used on data stored in LOB RDAREAs.
A table for which data has already been deleted from the same page.	Because such a page is easy to convert to used free pages, the probability of being able to release used free pages increases.
A table that is a REUSE table (table on which the free space reusage facility has been used).	Free pages released in a REUSE table by the free page release utility can be quickly reused in the free page reuse mode. If the table is not a REUSE table, free pages released by the free page release utility cannot be used until new segments in the RDAREA can no longer be allocated.

Based on the conditions described in the table above, the optimal table on which to apply this facility is a REUSE table with the FIX attribute for which no cluster key index is defined.

(b) Benefits of releasing used free pages of indexes

The following table lists and describes the benefits of releasing used free pages of an index.

Table 15-11 Benefits of releasing used free pages of an index

Benefit	Description	Degree of benefit
Potential reduction of space shortages in RDAREAs storing indexes	If space runs out even though free pages (used free pages) exist, release the used free pages. Note that this tends to reduce the occurrence of space shortages even for RDAREAs that store indexes with key values that are updated or deleted frequently.	Great
Ability to increase the index reorganization cycle	The ability to reuse used free pages improves data storage efficiency. In turn, this can increase (improve) the index reorganization time cycle.	Good
Improvement in performance when searching large data sets that use indexes	Because they are a type of used page, used free pages are searched. However, searching is not performed on unused pages (they are skipped by a search). Thus, converting to unused pages improves the search performance in direct proportion to the ratio converted. The benefits are particularly evident when large data sets are searched.	Varies

Legend:

Great: Particularly beneficial.

Good: Always beneficial.

Varies: The degree of benefit varies depending on conditions.

Use of this utility is particularly beneficial when deleted key values are not re-registered. Because used free pages are reused when the same key value is added or deleted repeatedly, large numbers of used free pages do not appear. However, for indexes that are defined for rows that increase or decrease by a fixed number (such as date, sequence number, and so on), and if past data is deleted in tandem with increases in data, a large number of used free pages that are not reused appear in the first half of the index pages. The following figure shows the processing of used free pages that are created for index pages.

Figure 15-4 Processing of used free pages being created for index pages

Note that after the used free pages are released, the key values are stored in the released pages, which improves data storage efficiency.

Reference note

This processing applies only to used free pages; it performs no page compaction on pages whose storage efficiency has deteriorated. Other than for free used pages, no benefit results if applied when there is a large number of pages whose storage efficiency has deteriorated.

From the standpoint of performance and data storage efficiency, reorganizing tables or indexes is superior to releasing used free pages. However, while you are releasing used free pages, you can still access the tables or indexes on which the utility is operating. With reorganization, you cannot access the tables or indexes on which the utility is operating. This means that you do not have to interrupt normal operations in order to release free pages.

Use the execution results of the database condition analysis utility to evaluate whether to reorganize tables and indexes or to release used free pages. The following lists the evaluation criteria:

• If there is a large number of used free pages, release the used free pages.
• If the page utilization ratio of the used pages differs significantly from the segment free page ratio (value of the PCTFREE operand of CREATE TABLE), perform reorganization.

(a) Estimate the system log file capacity

Because updated logs of the database are obtained when used free pages are released, we recommend that you re-estimate the system log file capacity. For details about estimating system log file capacity, see the HiRDB Version 9 Installation and Design Guide.

(b) Check the page status

Use the database condition analysis utility to assess periodically the page storage efficiency, the number of used free pages, and other statuses. A page whose usage ratio is 0% is a used free page. The page usage ratio and the number of used free pages is displayed in Used Page Ratio of Condition analysis (logical analysis) by RDAREA and Condition analysis by table or index.

If the data storage efficiency has deteriorated due to increases in the number of used free pages, consider releasing used free pages.

(c) Execute the free page release utility

You use the free page release utility to release used free pages.

Before releasing used free pages from a table, consider whether to perform page compaction. In the following cases, page compaction provides no benefits, so execute the free page release utility without performing page compaction:

• If most of the used pages are full pages or used free pages
• If all pages are free pages

(d) Check the results

Check the execution results of the free page release utility to determine if used free pages have been released as expected. If there is a large number of used free segments, consider releasing used free segments as well. For details about releasing used free segments, see 15.11.3 Reusing used free segments.

(e) If the free page release utility terminates abnormally

If the free page release utility terminates abnormally, some used pages that cannot be used will remain. Therefore, re-execute the free page release utility immediately. Re-executing this utility will release the used pages that cannot be used.

• Execution of the free page release utility on an index does not cause UAPs to wait for a long time while the utility is executing. However, if a UAP accesses an index that is being processed by this utility, completion processing of the UAP transaction might be delayed.
• You can specify a maximum wait time in the -w option of the free page release utility. If the wait status is not released within the specified wait time, the KFPH25002-E message is issued and the free page release utility terminates abnormally. If the -w option is omitted, the system continues waiting until the UAP transaction is completed.
• If a rollback occurs while the free page release utility is being executed and the number of unused pages is small, a page shortage occurs during the rollback, placing the RDAREA in error shutdown status. Therefore, do not execute the free page release utility while a job involving many DELETE or UPDATE statements is being executed.
• When the free page release utility is executed on an index, all used free pages that can be released are first placed in reserved release status. Used free pages that are in reserved release status are released after transaction completion wait and become unused pages. Consequently, while the free page release utility is being executed, the number of available pages temporarily decreases, which tends to cause a page shortage.
• If an RDAREA stores only indexes and an error shutdown occurs, recover the RDAREA by re-creating all stored indexes after reinitialization. If an RDAREA stores both tables and indexes, recover it from a backup.
• If an error occurs while the free page release utility is being executed on an index, used free pages in reserved release status will remain. Re-execute the free page release utility as soon as possible.
  If HiRDB terminates abnormally, used free pages the reserved release status will remain, as is the case when an error occurs. After HiRDB restarts, re-execute the free page release utility.