Whether or not the database reorganization utility can be executed depends on the open attribute of the table, index, and RDAREA containing LOB columns and the status of the RDAREA; for details, see Appendix C. RDAREA Status During Command Execution.
To reorganize a data dictionary table, place the RDAREA containing the data dictionary table in shutdown status by the pdhold command.
The maximum number of database reorganization utilities that can be executed at any one time depends on the value of the pd_utl_exec_mode operand in the system common definitions.
Note that during execution of pdreclaim and pdpgbfon, the maximum number of pdrorg commands that can be executed concurrently is reduced because pdreclaim and pdpgbfon call pdrorg internally. For example, if pd_utl_exec_mode=0 is specified, theoretically a maximum of 32 pdrorg commands can be executed; however, if 10 pdreclaim commands are executing, the maximum number of pdrorg commands that can be executed concurrently is reduced to 22.
If the partitioning key ranges are changed before a reload operation and the unloaded data does not fit in a redefined range, an error results and processing will terminate when that data is encountered during the reload operation. In this case, redefine the partitioning key ranges so that all the unloaded data fits within defined ranges, and reload the data.
To execute the database reorganization utility on an extracted database subject to data linkage, specify n or p in the -l option (to execute in no-log mode or pre-update log acquisition mode). If you execute the utility in the log acquisition mode, conformity may be lost between the extracted database and the target database because only a portion of the data updated in the extracted database is sent to the target database.
The database reorganization utility does not normalize the sign part of DECIMAL type, whether or not the facility for conversion to a DECIMAL signed normalized number is used. Therefore, if the unload data file was created by a HiRDB system that does not use the facility for conversion to a DECIMAL signed normalized number, you cannot reload this file to a table in a HiRDB system that uses this facility.
To normalize the sign part of a DECIMAL type in a database, create an unload data file with the -W option specified, then store the data again using the database load utility. For details about the facility for conversion to a DECIMAL signed normalized number, see the HiRDB Version 8 System Operation Guide.
The output destination of index information files depends on the type of pdrorg processing and the specified control statements, as shown in the following table:
Type of pdrorg processing | Control statement | Output destination of index information files | ||
---|---|---|---|---|
index statement | idxname statement | idxwork statement | ||
-k rorg, -k reld, -k ixmk | Specified* | N | ![]() | Index information file specified in the index statement |
Not specified | N | Specified | Directory specified in the idxwork statement | |
Not specified | N | Not specified | /tmp | |
-k ixrc, -k ixor | N | Specified | Specified | Directory specified in the idxwork statement |
N | Specified | Not specified | /tmp | |
Specified | N | ![]() | Index information file specified in the index statement |
N: Cannot be specified.
: Specification has no effect on the output destination of index information files.
* For a row-partitioned table, only the specified RDAREAs are being processed; therefore, the items with index statement not specified apply to the unspecified RDAREAs.
If you have added an RDAREA to a rebalancing table but have not executed pdrbal on the rebalancing table (return code = 0), you cannot reorganize the added RDAREA (in units of RDAREAs).
Table 8-35 shows whether or not pdrorg can be executed on an RDAREA that has been added to a rebalancing table.
Table 8-35 Whether or not pdrorg can be executed on an RDAREA that has been added to a rebalancing table
-k option | Table status | |||||
---|---|---|---|---|---|---|
RDAREA addition up to pdrbal execution | during pdrbal processing* (return code = 4) | After completion of pdrbal processing (return code = 0) | ||||
Existing | Added | Existing | Added | |||
unld | Y | ![]() | Y | Y | Y | |
rorg, reld | By table | Y | ![]() | Y* | Y* | Y |
By RDAREA | Y | ![]() | ![]() | ![]() | Y | |
ixmk | Y | ![]() | Y | Y | Y | |
ixrc | Y | ![]() | Y | Y | Y | |
ixor | Y | ![]() | Y | Y | Y |
Existing: Existing RDAREA specified in the table definition (CREATE TABLE)
Added: RDAREA added by ALTER TABLE ADD RDAREA
Y: pdrorg can be executed.
: pdrorg cannot be executed.
* To reload data while rebalancing the table, specify the -g option. If the table contains a LOB column or a column of an abstract data type with the LOB attribute, also specify the -j option. When a table is reorganized with the -g option specified, data is rearranged but its rebalancing is not completed. Therefore, make sure that pdrabl is executed after the reload operation and that its return code is 0.
To add an RDAREA to an unloaded table and then reload it, use the following procedure:
When executing the previous procedure, be sure to specify the -g option in pdrorg. Otherwise, an error results. For a rebalancing table with LOB columns or columns of abstract data type, also specify the -j option.
Executing -k rorg (reorganization) or reld (reload operation) on an RDAREA that is in frozen update status results in an error, because the utility cannot update the management area for the user LOB RDAREA. An attempt to reorganize or reload only a LOB column structure base table also results in the same error.
pdrorg executed on an external table terminates with an error.
When reloading a table, the SEGMENT REUSE specification is ignored.
If a table that has been FIX hash-partitioned by the rebalancing facility is to be reorganized, and if the partitioning conditions are changed between the unload and the reload processes, a shortage of RDAREA may occur due to lack of available segments even though there are free pages in the table storage RDAREA. This is because a change in the storage conditions requires data to be reloaded into a different segment from the one used during unloading, but there is no new segment that can be allocated. In such a case, expand the RDAREA before you execute reloading.
Data can be migrated between the 32-bit version and the 64-bit version of HiRDB by reloading the unload data files created by the 32-bit or 64-bit HiRDB. The migration conditions are the same as for migration between HiRDBs that use the same bit mode.
The following are the pdrorg utility's return codes:
0: Normal termination
4: Normal termination (some of the processing was skipped)
8: Abnormal termination
The system does not collect synchronization point dumps during the execution of pdrorg, because a transaction is not settled until one process is completed. One process means an unload operation, a reload operation, or index creation.
If other UAPs are executed at the same time as pdrorg and a system failure occurs during processing, the time required for restart increases. For this reason, you should not execute UAPs while pdrorg is executing, if possible.
For a reorganization or reload operation with the synchronization point specification, you can collect synchronization point dumps at intervals of specified number of lines. This reduces the time required for restart in the event of abnormal termination, compared with the reorganization or reload operation with no synchronization point dump collected.
To cancel processing during execution of pdrorg, use the pdcancel command. To treat the pdrorg command as resulting in a no-response error (such as when a routine reorganization job should finish within a known amount of time, but it does not) and forcibly terminate the command, redirect the display results of the pdls command (with -d rpc -a specified) to a file and then execute the pdcancel -d command.
In this case, processing will be rolled back. For details about the database status and recovery method, see 8.13 Database status in the event of an error and recovery method.
If you are using the facility for predicting reorganization time and you forcibly terminate pdrorg by a signal interrupt such as the kill command, the database management table cannot be updated. To forcibly terminate pdrorg while the facility for predicting reorganization time is used, make sure that you use the pdcancel command.
Once a table has been unloaded, do not specify the following definition SQL statements until the table has been reloaded:
However, you can execute these definition SQL statements when changing the partitioning key partitioning condition or the hash function.
You can check the result of the database reorganization utility using the database condition analysis utility.
An error will occur during a reload operation if you specify either of the following options when changing the partitioning key partitioning condition:
Because MTguide is used for the volume switching operation when multi-volume magnetic tape is used, MTguide must be installed at the server machine.
If you have reinitialized an RDAREA by the database structure modification utility, any index related to the RDAREA may be placed in incomplete status. If a non-partitioning key index is placed in incomplete status due to reinitialization, reorganize the table in the batch index creation mode in units of tables. If you create an index in batch mode using the index file that was output before reinitializing the RDAREA, the database may be damaged.
If you are using the system switchover facility, we recommend that you execute -k unld and -k reld instead of executing -k rorg for table reorganization. This method enables you to continue the reload operation at the target system using the unload data file on which the unload operation has been completed. In such a case, use for the unload data file a magnetic tape device (such as a DAT) or a HiRDB file created by a character string special file. If you use a regular file for block input/output operations to create an unload data file, the contents of the unload data file cannot be guaranteed even when the unload operation terminates with return code 0, because the OS buffer is deleted during system switchover.
If pdrorg terminates abnormally while an unload data file is being created, invalid files may remain. Re-executing pdrorg when this happens results in the remaining files being overwritten. If you do not re-execute the utility, you should delete these files. If the files to be deleted are regular files, use the OS's rm command to delete them; if they are HiRDB files, use the pdfrm command to delete them.
If pdrorg terminates abnormally while a table is being reorganized or reloaded, the processing is rolled back and data is deleted from the database. To restore the database, use the unload data file created by unload processing to execute reloading only. If multiple tables are output one after another to a single unload data file, the unload data file is overwritten when unloading of another table is performed after abnormal termination of pdrorg. As a result, data is lost from both the unload data file and the database. Therefore, if the database cannot be restored from its backup, make sure that you specify a separate unload data file for each table.
The following notes apply to unloading and reloading tables between systems:
When the partitioning key ranges are changed or the hash function is changed, the utility divides data and stores it into RDAREAs based on the changed partitioning conditions only at the following times (excluding the cases in which the partitioning key column is changed or the -j option is omitted for a table containing a LOB column):
For details about how to switch among key range partitioning, flexible hash partitioning, and FIX hash partitioning, see the HiRDB Version 8 System Operation Guide.
If the table definitions are the same, a table unloaded from a HiRDB/Single Server can be reloaded to a table in a HiRDB/Parallel Server, and vice versa. If the table contains a LOB column, you need to specify the -j option during the unload and reload operations.
If a table to be unloaded from a HiRDB/Parallel Server is partitioned and stored in multiple servers, you need to specify the -g option to create a single unload data file before you can reload it to a table in a HiRDB/Single Server.
If the table definitions are different or if you want to use the unload data files that are output for individual servers, execute the unload operation specifying the -W option, then use the database load utility to load the data. In this case, if -W bin is specified, but the -j option is omitted, the null value is set in any LOB columns. When you transfer a LOB column, specify both options, -W bin and -j. If a cluster key index has been defined for a row-partitioned table, data in the unload data file is sorted by the cluster key in each RDAREA, but it is not sorted by the cluster key in the entire table. In such a case, a clustering order error occurs. Therefore, you must specify the -x option in pdload or perform processing in units of RDAREAs.
Use of large files enables you to handle files whose size is 2 gigabytes or greater. Table 8-36 describes whether or not pdrorg supports large files. The maximum file size that can be created by the process is determined by the settings of the operating system in use. For the limit value of system resources for the HiRDB administrator and root user, either specify a value that is greater than the size of file that is created or do not limit the value. Special attention is needed under AIX-5L because its default file size is 1 gigabyte. You can check the limit value of system resources with the OS's limit or ulimit command. To change the limit value for file size in AIX 5L, you must also change the /etc/security/limits file. For details, see the applicable OS and shell documentation. Because HiRDB is an INIT startup process, you must restart the operating system before a value changed by the root user can take effect.
Table 8-36 Whether or not large files can be used for pdrorg processing
File type | Availability of large file system |
---|---|
Unload data file | Y |
LOB data unload file | Y |
Index information file | Y |
Work file for sorting | Y |
MT attribute definition file | N |
Process results file | Y |
Y: Can be used.
N: Cannot be used.
The following table shows whether batch index creation and index re-creation are supported for a plug-in index:
Provided function | Batch index creation | Index re-creation |
---|---|---|
Plug-in index delayed batch creation facility | Executable | Executable |
Batch plug-in index creation partial recovery facility | Executable | Executable |
None of the these facilities available | Not executable | Executable |
To execute batch index creation on a plug-in index (by specifying -k ixmk), the plug-in must provide the batch plug-in index creation partial recovery facility. If the plug-in does not provide this facility, you need to re-create the index (by specifying -k ixrc).
If batch index creation that uses the index information file created by the plug-in index delayed batch creation facility terminates normally, the system deletes the that index information file. Note that the index information file output directory (directory with pd_plugin_ixmk_dir specified in the server definition) used by this facility should not be used as a file I/O area for the database reorganization utility unless the utility processing is batch index creation (-k ixmk).
When using the differential index function of the HiRDB Text Search Plug-in, the database reorganization utility updates the following indexes:
Index creation method | Specification of environmental variable PDPLUGINNSUB | ||
---|---|---|---|
Y | N | Not specified | |
Re-creating an index (-k ixrc specified) | M | M | M |
Creating an entire index (-k ixmk specified) | M | M | M |
Creating an index for additional data (-k ixmk specified) | S | M | S |
M: Updates the MASTER index.
S: Updates the differential index.
If you have created a list using a table being reorganized or reloaded, searching this list after the reorganization or reload operation results in the following events:
In this case, you need to re-create the list before searching the list.
During the execution of the database reorganization utility, each server requires the following amount of locked resources:
X = 2 (a + b + c + 1)
(b
d)
Y = e + f + g
You can use a DVD-RAM device for unload data files for the pdrorg command. Note that the supported device depends on the operating system in use.
To use as regular files, you can create a file system on a medium in the same manner as conventional devices such as magnetic disks.
To use as HiRDB files, you must specify a sector length during creation of the HiRDB file system area (during the pdfmkfs command's execution).
The table below shows the file media that are available during execution of pdrorg. When regular files are used, file open processing accompanies the use of the maxfiles, nfile, and nflocks operating system parameters (kernel parameters).
File | Regular file | HiRDB file | Blocked fixed-length tape | Blocked variable-length tape |
---|---|---|---|---|
Unload data file | Y | Y | Y | Y |
Unload data file for LOB data | Y | Y | Y | Y |
Index information file | Y | If | N | N |
Work file for sorting | Y | N | N | N |
Process results file | Y | N | N | N |
pdrorg outputs progress messages to the standard output during processing. In the event of an error, pdrorg outputs an error message to the standard error output. If pdrorg is executed in an environment in which output to the standard output and standard error output is suppressed, pdrorg may be placed in the message output wait status and stop responding, or may output the KFPL20003-E message to the message log file and then terminate abnormally. Therefore, you should not execute pdrorg in an environment in which messages cannot be output to the standard output or standard error output. Note that the sequence and number of messages that are output to the standard output and standard error output may not match those in the message log file and syslogfile. To obtain the correct messages, view the message log file or syslogfile.
When a shared table is reorganized, the system places the RDAREAs containing the shared table and shared indexes defined for the target table in the EX lock mode. If the corresponding RDAREAs contain other tables and indexes, these tables and indexes cannot be referenced or updated. For details about the lock mode when a shared table is reorganized, see Appendix B.2 Lock mode for utilities.
When reorganization or reloading is to be executed, for details about whether or not the check pending status can be set for a table for which referential constraints or check constraints have been defined, see 8.9.15 constraint statement (specification of check pending status). For details about the check pending status, see the manual HiRDB Version 8 Installation and Design Guide.
When all of the following conditions are satisfied, a table in check pending status cannot be reorganized (-k rorg):
Whether or not the table is in check pending status is determined from the value of the CHECK_PEND or CHECK_PEND2 column in the data dictionary table (SQL_TABLES table) (if the column value is 'C', the table is in check pending status).
The history of table and index reorganization is applied to the result of reorganization time prediction.
If pdrorg has terminated abnormally, executing pddbst's condition analysis result accumulation facility results in an invalid prediction result* because the reorganization timing cannot be predicted in the reorganization completed status. Therefore, if pdrorg terminates abnormally, re-execute pdrorg to terminate it normally, then execute pddbst's condition analysis result accumulation facility.
If you selected utf-8 as the character encoding in the pdsetup command, you may be able to use a file with a BOM as the input file for pdrorg. Table 8-37 shows the files with a BOM in pdrorg. Note that even when a file with a BOM is used as the input file to pdrorg, the BOM is skipped. No BOM is included in the file that is output by pdrorg.
Table 8-37 Whether or not files with a BOM can be used in pdrorg (applicable to UTF-8)
Control statement | Input file | Use of file with a BOM |
---|---|---|
![]() | Control information file | Y |
emtdef | MT attribute definition file | N |
unload | Unload data file | N |
EasyMT information file | N | |
index | Index information file | N |
lobunld | LOB data unload file | N |
EasyMT information file | N |