11.3.2 Options

Organization of this subsection
(1) -k target-resource
(2) -t {[authorization-identifier.]table-identifier|[authorization-identifier.]all}
(3) -r RDAREA-name
(4) -c target
(5) -m [commit-interval-for-releasing-free-pages][,commit-interval-for-releasing-free-segments]
(6) -u authorization-identifier
(7) -w concurrently-executed-transaction-settlement-wait-time
(8) -X response-monitoring-time-for-server-to-server-communication
(9) -q generation-number
(10) {-Z|-j|-a}
(11) -x
(12) -p
(13) -n lock-retries-count
(14) -s server-name
(15) control-information-file-name

(1) -k target-resource

Specifies the type of resource (table or index) whose used free pages are to be released.

table:
Specifies that a table is to be processed.
index:
Specifies that indexes are to be processed.

(2) -t {[authorization-identifier.]table-identifier|[authorization-identifier.]all}

~<identifier>

Specifies the name of the table whose used free pages are to be released.

If you omit the authorization identifier, the utility assumes the user name used to connect to HiRDB.

If you specify all, the utility processes all tables or indexes that belong to the schema whose name is the specified authorization identifier. In this case, you cannot specify the idxname statement.

(3) -r RDAREA-name

~<identifier>

Specifies the name of the RDAREA that is to be processed (among the RDAREAs storing the table specified with the -t option).

You can specify this option only when -k table is specified. If you specify -k index, specify the idxname statement.

Only a user RDAREA can be specified. Batch specification is not permitted.

(4) -c target

~<<user>>

Specifies the type of RDAREA that is to be the target of this processing (user RDAREA or data dictionary RDAREA).

user:
Specifies that a user RDAREA is to be processed.
dic:
Specifies that a data dictionary RDAREA is to be processed.

(5) -m [commit-interval-for-releasing-free-pages][,commit-interval-for-releasing-free-segments]

Specifies a commit interval for releasing free pages or free segments.

commit-interval-for-releasing-free-pages ~<unsigned integer> ((0-100000)) <<1000>>
Specifies the transaction settlement interval for the used free page release processing, expressed as the number of pages released. When the number of released pages per RDAREA for the table or index reaches the specified value, the transaction is settled. The utility executes only page compaction on the released pages, including pages that could not become used free pages.
If you specify 0, the transaction settles when all used free pages in the corresponding RDAREA have been released.
By specifying a large value, you can reduce the amount of transaction log information. However, pdreclaim becomes a long-running transaction, losing the timing of synchronization point validation. When you also specify the -p option, you must specify an appropriate commit interval so that the transaction can be settled before a synchronization-point dump is acquired. A guideline for the commit interval value when the -p option is specified is that it should not exceed 50% of the sum of the number of synchronization-point dump pages of the global buffer accessed by pdreclaim. You can determine the number of synchronization-point dump pages by checking CYNCW in 14.3.5 Global buffer pool statistical information.
commit-interval-for-releasing-free-segments ~<unsigned integer>((0-10000000))<<0>>
Specification of a commit interval for releasing free segments is applicable only when the -a option is also specified.
Specifies the transaction settlement interval for the used free segment release processing, expressed as the number of segments released. When the number of released segments per RDAREA for the table or index reaches the specified value, the transaction is settled.
If you specify 0, the transaction settles when all used free segments in the corresponding RDAREA have been released.
By specifying a large value, you can reduce the amount of transaction log information. However, pdreclaim becomes a long-running transaction, losing the timing of synchronization-point validation. When you also specify the -p option, you must specify an appropriate commit interval so that the transaction can be settled before a synchronization-point dump is acquired. A guideline for the commit interval value when the -p option is specified is that is should not exceed sum of the number of synchronization-point dump pages of the global buffer accessed by pdreclaim[Figure] 2[Figure] 60. You can determine the number of synchronization-point dump pages by checking CYNCW in 14.3.5 Global buffer pool statistical information

(6) -u authorization-identifier

Specifies the authorization identifier of the user who executes pdreclaim.

For details about the authorization identifier, see 8.9.2(10) -u authorization-identifier.

(7) -w concurrently-executed-transaction-settlement-wait-time

~<unsigned integer> ((0-3600)) <<0>>

Specifies a wait time for pdreclaim (in seconds). When a UAP or another utility accesses a table or index while its free pages or free segments are being released, pdreclaim is placed in lock-release wait status or in transaction settlement wait status. This option specifies that wait time.

pdreclaim is placed in these wait statuses in the following situations:

Lock-release wait status:
pdreclaim is placed in lock-release wait status when either of the following occurs:
  • A UAP or utility that references or updates the same RDAREA is running
  • A UAP that performs data loading in units of RDAREAs or that uses the local buffer is running
Transaction settlement wait status:
pdreclaim is placed in transaction settlement wait status when either of the following occurs:
  • Any of the following operations is in transaction settlement wait status for the index whose free pages are to be released and for the table to which that index belongs:
    [Figure]SQL statement (SELECT, UPDATE, INSERT, or DELETE)
    [Figure]Unloading by pdrorg
    [Figure] pddbst (condition analysis in units of tables, condition analysis in units of indexes, storage condition analysis for cluster key and clustering data pages, facility for accumulating condition analysis results, or facility for predicting reorganization time)
  • Any of the following operations is in transaction settlement wait status for the table or index whose free segments are to be released:
    [Figure]SSQL statement (SELECT, UPDATE, INSERT, or DELETE)
    [Figure]Unloading by pdrorg
    [Figure] pdpgbfon
    [Figure] pddbst (condition analysis in units of tables, condition analysis in units of indexes, storage condition analysis for cluster key and clustering data pages, facility for accumulating condition analysis results, or facility for predicting reorganization time)
Wait status for a search using a holdable cursor:
If there is a UAP that uses a holdable cursor to search the table or index whose free segments are to be released, the search using the holdable cursor is placed in wait status (until the holdable cursor is closed and the transaction is settled). If the UAP closes the holdable cursor during execution of pdreclaim, then opens a holdable cursor again for the same table or index, the processing is placed in wait status until that cursor is closed and the transaction is settled.

The table below shows the relationship between the -w option's specification and pdreclaim's wait time.

Table 11-8 Relationship between -w option specification and pdreclaim's wait time

ProcessingSpecification of -w optionLock-release wait timeTransaction settlement wait timeWait time for a search using a holdable cursor
Releasing free pages in a tableYesWaits for the time specified in the -w option----
NoWaits until the lock status is released----
Releasing free pages in an indexYesWaits for the time specified in the -w optionWaits for the time specified in the -w option--
NoWaits until the lock status is releasedWaits until the other transaction is settled--
Releasing free segments in a table (-j)YesWaits for the time specified in the -w option----
NoWaits until the lock status is released----
Releasing free segments in an index (-j)YesWaits for the time specified in the -w option----
NoWaits until the lock status is released----
Releasing free pages or free segments in a table (-a)YesWaits for the time specified in the -w optionWaits for the time specified in the -w optionWaits for the time specified in the -w option
NoWaits until the lock status is releasedWaits until the other transaction is settled#1Waits until the holdable cursor used for search is closed and the transaction is settled#2
Releasing free pages or free segments in an index (-a)YesWaits for the time specified in the -w optionWaits for the time specified in the -w optionWaits for the time specified in the -w option
NoWaits until the lock status is releasedWaits until the other transaction is settled#1Waits until the holdable cursor used for search is closed and the transaction is settled#2
Legend:
--: Not applicable
#1
The transaction is settled at the following times:
  • COMMIT
  • ROLLBACK
  • Internal ROLLBACK
If free pages are to be released and UNTIL DISCONNECT is specified for the search, and COMMIT is also executed, one of the following operations is also required:
  • The cursor is closed before COMMIT is executed
  • The next data is fetched (FETCH)
#2
The wait status of a search using a holdable cursor is settled at the following times:
  • ROLLBACK
  • Internal ROLLBACK
  • The cursor is closed before COMMIT is executed
Rules
If the UAP's transaction is not settled or the search using a holdable cursor does not close the cursor and settle the transaction within the specified amount of time after pdreclaim is placed in wait status, pdreclaim cancels processing with return code 4. If the lock-release wait status is not released, pdreclaim cancels processing with return code 8.
Lock-release wait status for pdreclaim
The lock-release wait status for pdreclaim depends on whether the -a option is specified.
  • When the -a option is omitted
    The figure below shows the flow of locking in pdreclaim (when the -a option is omitted).

    Figure 11-7 Flow of locking in pdreclaim (when the -a option is omitted)

    [Figure]

#1
When free pages are released from a table, the target is table storage RDAREAs; when free pages are released from an index, the target is index storage RDAREAs.
#2
For details about the lock mode and the resources to be locked to release free pages, see B.2 Lock mode for utilities.
#3
If the lock mode is not released within the time specified in the -w option, pdreclaim outputs the KFPA11770-I message and cancels the pdreclaim processing.
#4
If the lock mode is not released within the time specified in the -w option, pdreclaim outputs the KFPL27500-E and KFPH25002-E messages, cancels the RDAREA processing, and then processes the next RDAREA. When pdreclaim cancels the processing, it outputs the KFPH25004-I message and displays information about all transactions that are to be placed in wait status.
#5
Applicable only when free pages are released in an index.
#6
This is the duration of the lock placed by pdreclaim.
#7
If the wait time reaches 10 minutes, pdreclaim outputs the KFPH25004-I message and displays information about all transactions that are to be placed in wait status. This KFPH25004-I message might be output more than once because pdreclaim outputs transaction information for each cause of a wait for transaction settlement.
  • When the -a option is specified
    The figure below shows the flow of locking in pdreclaim (when the -a option is specified).

    Figure 11-8 Flow of locking in pdreclaim (when the -a option is specified)

    [Figure]

#1
When free pages are released from a table, the target is table storage RDAREAs; when free pages are released from an index, the target is index storage RDAREAs.
#2
For details about the lock mode and the resources to be locked to release free pages, see B.2 Lock mode for utilities.
#3
If the lock mode is not released within the time specified in the -w option, pdreclaim outputs the KFPA11770-I message and cancels the pdreclaim processing.
#4
If the lock mode is not released within the time specified in the -w option, pdreclaim outputs the KFPL27500-E and KFPH25002-E messages, cancels the RDAREA processing, and then processes the next RDAREA. When pdreclaim cancels the processing, it outputs the KFPH25004-I message and displays information about all transactions that are to be placed in wait status.
#5
Applicable only when free pages are released in an index.
#6
This is the duration of the lock placed by pdreclaim.
#7
If the wait time reaches 10 minutes, pdreclaim outputs the KFPH25004-I message and displays information about all transactions that are to be placed in wait status. This KFPH25004-I message might be output more than once because pdreclaim outputs transaction information for each cause of a wait for transaction settlement.
How to handle the KFPH25004-I message
  • When 1 or a greater value is specified in the -w option
    When a timeout occurs, information is output about the transactions that are to be placed in wait status. You must re-execute pdreclaim after all the transactions displayed in the message have been completed. To determine whether the transactions have been completed, see the figure below.
  • When 0 is specified in the -w option or the option is omitted
    Information is output about the transactions to be placed in wait status. See the figure below to identify the transactions that are to be placed in wait status and terminate any unneeded transaction. Otherwise, you must wait until all transactions have terminated. Because there can be more than one reason for a transaction to be placed in wait status, information for another set of transactions might be displayed after all transactions displayed in the first message have been terminated. If you have a need to terminate pdreclaim before all transactions have been terminated, cancel pdreclaim.

    Figure 11-9 How to handle the KFPH25004-I message

    [Figure]

(8) -X response-monitoring-time-for-server-to-server-communication

~<unsigned integer> ((1-65535)) <<300>>

If an error, such as a communication error, occurs at the server where the command was executed, the command may stop responding and the application may stop. To detect errors, pdreclaim enables you to monitor a response time during communication for dictionary manipulation that is performed by the command.

You set in the -X option the response monitoring time during dictionary manipulation (in seconds). If the execution time during dictionary manipulation exceeds the value set in the -X option, pdreclaim treats it as a dictionary access error and cancels processing with return code 8.

Criteria
  • If you want to detect an error in less time than 300 seconds in the event of a no-response from the server due to a communication error or unit down, specify a smaller value than 300 in the -X option.
  • If the system switchover facility is used, the command may keep waiting for a response even though system switchover has been completed. In such a case, you can terminate the command immediately by reducing the monitoring time.
  • The specified monitoring time may result in a timeout if a response from the dictionary is delayed and if the utility's preprocessing is not completed within 300 seconds, which is the default value of the -X option. This can happen when many applications and utilities are executing concurrently. In such an environment, specify a value greater than 300 in the -X option.

(9) -q generation-number

~<unsigned integer> ((0-10))

When the inner replica facility is used, this option specifies the generation number of the target RDAREA.

Specify the generation number as follows:

0: Processes the original RDAREA.
1 to 10: Processes the replica RDAREA with the specified generation number.
Criteria
Specify this option to process an RDAREA other than the current RDAREA using the inner replica facility.
Rules
  1. When this option is omitted, the current RDAREA is assumed.
  2. If a replica RDAREA is to be processed, specify the name of the original RDAREA in the -r option and the target generation number in the -q option.
  3. pdreclaim checks the target RDAREA to determine whether its generation number matches the specification. The table below shows the RDAREAs whose generations are checked by pdreclaim.

    Table 11-9 RDAREAs subject to generation checking by pdreclaim

    Type of processingTarget RDAREASpecification of option or in control information file
    For storing a tableFor storing an index
    Releasing used free pages in tableIn units of tablesY---k table
    In units of RDAREAsY---k table -r RDAREA-name
    Releasing used free pages in indexIn units of indexesYY-k index
    In units of serversYY-k index
    idxname statement (with server specified)
    In units of RDAREAsYY-k index
    idxname statement (with rdarea specified)
Legend:
Y: Checked
--: Not checked

(10) {-Z|-j|-a}

Specifies how free pages and free segments are to be released. The default value depends on the mode, as shown below:

Default operation modeDefault value
Recommended mode-a
Compatibility mode-Z
Criteria
If there is no need to release free segments, specify -Z. To release free segments, specify -j, if you can secure enough time while online services are stopped to execute pdreclaim with the -j option specified. If you cannot secure enough time to execute pdreclaim, specify -a.
Notes
  • When free segments are released, used segments might not be released in some cases even though all their pages are unused.
    When the -j option is specified, there will be one such unused segment per table storage RDAREA for each table.
    When the -a option is specified, there will be two such segments that are not released per table storage RDAREA for each table (for a rebalancing table, there will be 1,024 such segments).
  • When free segments are released by pdreclaim with the -j option specified, the update buffer for the table or index in the global buffer subject to release is written into the database and the update and reference buffers are deleted from the global buffer. Therefore, the buffer hit rate for the corresponding table or index becomes poor immediately after pdreclaim has executed. If you want to improve the buffer hit rate, use pdpgbfon to read data again into the global buffer.
-Z
When you specify the -Z option, only free pages are released. Free segments are not released.
-j
When you specify the -j option, the table or index being processed becomes inaccessible. Therefore, use this option as explained below:
  1. During daytime online operation, execute pdreclaim with the -Z option specified (neither -a nor -j can be specified if you are using the 0904 compatibility mode).
  2. During nighttime batch operation, execute pdreclaim with -j specified.
When you specify the -j option, the released unused segments become available to any table or index, whether or not it has been using the segments. However, if you specify -k table or -k index, the table storage RDAREA or index storage RDAREA is placed in the EX lock mode, in which case pdreclaim can no longer be concurrently executable with a UAP.
-a
If you specify the -a option, the table or index can be accessed from another UAP or utility while it is being processed by pdreclaim. However, the time required to release free segments increases, and the amount of log information in the system log file also increases. For details about estimating the size of the system log file, see the manual HiRDB Version 9 Installation and Design Guide.
When the -a option is specified, a maximum of two segments are not released per table or index storage RDAREA. For a rebalancing table, a maximum of 1,024 segments are not released per storage RDAREA.

(11) -x

Specifies page compaction when free index pages are released. When an index key value with no lock is used or uncommitted data to be deleted is locked, page compaction is executed in order to release the remaining entries in the index. By executing page compaction, you can maintain online performance and avoid lock-release wait and deadlock. You can specify the -x option when you have specified -k index.

When page compaction is executed to release remaining entries, database update log information (FJ) is output and its volume is proportional to the amount of key space that is released.

Criteria
Specifying the -x option results in overhead for page compaction. If you want to reduce the volume of the database update log information (FJ) or if performance is more important, do not specify the -x option.

(12) -p

Specifies that the update buffer is to be applied to the database at the point of commit. If you specify the -p option when you execute the utility at the same time as you execute a UAP, you can reduce the workload of synchronization point processing, thereby reducing the effects on the performance of other transactions. Note that the performance of pdreclaim is lower than when the -p option is omitted.

Specify the -m option to set the commit interval in such a manner that a commit is acquired before a synchronization point dump is acquired.

A maximum of 10 concurrent executions of pdreclaim with the -p option specified is supported per server. Any excess pdreclaim utility executions will terminate with an error.

(13) -n lock-retries-count

~<unsigned integer> ((0-3600)) <<0>>

Specifies the maximum number of lock acquisition requests to be released in the case of releasing free segments. Because the -n option is applicable only when free segments are released, you must also specify the -j option when you specify the -n option.

The following describes release of lock acquisition requests in order to release free segments.

If you specify the -j option to release free segments, the target RDAREA is placed in the EX lock mode. If an attempt is made to release free segments in an RDAREA that is being processed by another transaction, the processing may be placed in lock-release wait status.

If another transaction acquires a lock on the RDAREA while the free segment release processing is in lock-release wait status, the first transaction that acquired a lock on the RDAREA and the free segment release processing are both placed in wait status until the lock is released.

When the -n option is specified, the lock acquisition request for the free segment release processing is released when the time specified in the -w option has elapsed, thereby giving preferences to the other transactions' lock-release wait status. The free segment release processing waits for lock release for the amount of time specified in the -w option. Therefore, if you specify the -n option, you must also specify the -w option.

If free segments are not released, the lock acquisition request for free segment release processing is cancelled and then issued again.

The figure below shows the relationship between the lock acquisition request and the lock-release wait queue.

Figure 11-10 Relationship between lock acquisition request and lock-release wait queue

[Figure]

When the -n option is specified, the utility waits for lock release for as long as the amount of time obtained from -w option value[Figure] -n option value. If this value exceeds the pdreclaim execution monitoring time specified in the exectime operand in the option statement, pdreclaim displays the KFPL11111-E message while waiting for lock release and terminates itself forcibly. It is important that you specify an appropriate value in the -n option, taking into account the following:

Relationship between the -n option and the pdreclaim execution monitoring time
A + B + C < D[Figure] 60
Legend:
A: Transaction settlement wait time (-w option value)
B: Lock-release wait time for the free segment release processing (-w option value [Figure] -n option value)
C: Execution time for free segment release processing
D: pdreclaim execution monitoring time specified in the exectime operand of the option statement

(14) -s server-name

~<identifier>

This option is applicable to a HiRDB parallel server configuration only (it is ignored if specified for a HiRDB single server configuration).

Specifies the name of the back-end server that is to manage execution of pdreclaim.

Criteria
When this option is omitted, one of the back-end servers is selected automatically; therefore, normally there is no need to specify this option.
Specify this option if you execute many pdreclaim commands concurrently and the number of utility server processes for pdrorg per server (0mrorg) is 32 or greater. You can determine the value of 0mrorg by checking the result of the pdls -d prc command for the number of process IDs (0mrorg).

(15) control-information-file-name

~<path name>

Specifies the name of the control information file that contains the control statements for pdreclaim.

You can specify the following control statements in the control information file; for details about these control statement, see 11.3.3 idxname statement (specification of index information) and 11.3.4 option statement (specification of optional information). Note that comments cannot be specified in control information files.