11.1.3 Functions of pdreclaim

pdreclaim has the following two functions:

Executing pdreclaim without the -j or -a option specified releases used free pages. If used free segments are created as a result, executing pdreclaim with the -j option specified releases the used free segments. When pdreclaim is executed with the -a option specified, it releases used free pages and used free segments.

The following table shows whether pdreclaim can be executed depending on the specification of the -j or -a option:

Option specification in pdreclaimReleases used free pagesReleases used free segments
-j and -a options omittedYN
-j option specifiedNY
-a option specifiedYY
Legend:
Y: Executed
N: Not executed

Note that you cannot release used free segments without first releasing used free pages. However, if either of the operations noted below is executed, used free segments are created. In such a case, pdreclaim with the -j or -a option specified can release used free segments without releasing the used free pages created by pdreclaim without the -j or -a option specified.

Releasing used free pages from a table provides the following advantages:

Releasing used free pages from indexes provides the following advantages:

After releasing used free pages from indexes, the utility first allocates those unused pages that were released by means of used free space search processing. The utility allocates unused pages in used segments preferentially, thereby achieving efficient space utilization and reducing the overhead of used free space search processing.

(a) Releasing used free pages in a table

The figure below provides an overview of releasing used free pages in a table.

Figure 11-1 Overview of releasing used free pages in a table

[Figure]

By releasing used free pages in a table, you can select release of used free segments, release of used free pages, or execution of page compaction. Note that release of used free pages in a table is not applicable to LOB columns or columns of an abstract data type with the LOB attribute.

Deletion of a large amount of data results in used free pages that contain no data but which remain allocated. For all tables other than SEGMENT REUSE tables (tables for which the SEGMENT REUSE option is specified in CREATE TABLE or ALTER TABLE), HiRDB uses unused pages as well as unused segments for storing data. If no new space is available, HiRDB reuses used free pages, in which case performance is not as good as when new space can be allocated due to the overhead of searching for free space in the used pages. Even with a SEGMENT REUSE table, if there are used free pages, a global search involves unneeded searching of empty pages. Note that only the tables and indexes in the corresponding used segments are reusable.

Execution of pdreclaim has the following effects because this utility releases used free pages that are not immediately reused:

  1. A segment from which all data has been deleted becomes an unused segment; therefore, it becomes available to all tables and indexes.
  2. A page from which all data has been deleted becomes an unused page; therefore, it can be reused by the table in the corresponding used segment. Especially with a SEGMENT REUSE table, the effects are great because the overhead of searching the pages to be reused is eliminated.

As a part of effect 1, if the same RDAREA stores multiple tables, it is possible to prevent a shortage of RDAREA space that may result if table B cannot allocate a new unused segment because Table A has used free segments that contain no data. The figure below shows the effects of pdreclaim when the same RDAREA stores multiple tables.

Figure 11-2 Effects of pdreclaim when the same RDAREA stores multiple tables

[Figure]

The figure below provides an overview of page compaction in tables.

Figure 11-3 Overview of page compaction in tables

[Figure]

Explanation
Before execution of pdreclaim, any data larger than row 2 or 5 cannot be stored as is in the free space created by deleting rows 2 and 5. If these two free spaces are made contiguous, then data can be stored. Reorganizing these scattered free spaces into a single contiguous space is called page compaction. Page compaction is also executed as an extension of SQL statements. However, online performance is reduced when page compaction is executed as an extension of SQL statements. You can avoid such adverse effects on online performance by using pdreclaim to execute page compaction in advance. Note that page compaction increases the input/output processing load.
(b) Releasing used free pages from indexes

The figure below provides an overview of releasing used free pages in indexes.

Figure 11-4 Overview of releasing used free pages in indexes

[Figure]

By releasing used free pages in indexes, you can select release of used free segments or release of used free pages. Note that release of used free pages is not applicable to plug-in indexes.

As is the case with a table, if a large amount of data is deleted from an index, unused free pages are created that are not reusable. Especially, used free pages that are created by deleting existing data with small key values remain allocated (remain connected by pointers) to make them available should the same key values be stored again; these used free pages cannot be reused when new data with a large key value is added. Therefore, in the operation mode where key values of stored data simply increase as previous data is deleted, a steadily increasing number of nonreusable used free pages is created. If you use pdreclaim on such an index, data with the large key values can be stored because the used free pages are released and unused pages are created. This utility can also create unused pages from intermediate pages with no downward page pointers.

The figure below provides an overview of page compaction in indexes.

Figure 11-5 Overview of page compaction in indexes

[Figure]

Explanation
Before pdreclaim is executed, the deleted keys 2 and 5 are retained as remaining entries. Releasing these remaining entries and defragmenting their spaces into a contiguous space is called page compaction. Page compaction is also executed as an extension of SQL statements. However, when it is executed as an extension of SQL statements, online performance is adversely affected. You can avoid such adverse effects on online performance by executing page compaction in advance with pdreclaim. Note that page compaction increases the input/output processing load.
If remaining entries are left as is, a lock-release wait or deadlock may occur. By executing page compaction with pdreclaim, you can also avoid lock-release waits and deadlock.
For details about the remaining entries for indexes and unique indexes, see the HiRDB Version 9 UAP Development Guide.