8.13 Rules and notes

Organization of this section
(1) Rules
(2) Notes
(3) Using a file that contains a BOM

(1) Rules

(a) Executing the utility
(b) Whether the utility can be executed

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.

(c) Maximum number of concurrently executable utilities

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.

pd_utl_exec_mode=0
A maximum of 32 utilities can be executed concurrently.
pd_utl_exec_mode=1
The value of the pd_max_users operand determines the maximum number of concurrently executable utilities.

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.

(d) Tables and indexes during utility processing
(e) Changing partitioning key ranges

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.

(f) Log-acquisition mode during utility execution on an extracted database subject to data linkage

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.

(g) Facility for conversion to a DECIMAL signed normalized number

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 9 System Operation Guide.

(h) Output destination of files during index creation

If no destination is specified for files in the control information file during index creation, the utility outputs files to the directory shown in the table below.

Table 8-42 Directory to which pdrorg outputs files

Control statement#1pd_tmp_directory operand in the system definition
SpecifiedOmitted
TMPDIR#2 environment variable
SpecifiedOmitted
SpecifiedDirectory or file specified in the control statement
OmittedDirectory specified in pd_tmp_directoryDirectory specified in TMPDIR/tmp directory
#1
idxwork and sort statements
#2
Environment variable specified in the process server process (pdprcd) that is started from init (OS process)

(i) Output destination of index information files

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:

Table 8-43 Output destination of index information file

Type of pdrorg processingControl statementOutput destination of index information files
index statementidxname statementidxwork statement
-k rorg,
-k reld,
-k ixmk
Specified#N--Index information file specified in the index statement
Not specifiedNSpecifiedDirectory specified in the idxwork statement
Not specifiedNNot specifiedSee Table 8-42
-k ixrc,
-k ixor
NSpecifiedSpecifiedDirectory specified in the idxwork statement
NSpecifiedNot specifiedSee Table 8-42
SpecifiedN--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.

(j) Reorganizing a rebalancing table

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).

The table below shows whether pdrorg can be executed on an RDAREA that has been added to a rebalancing table.

Table 8-44 Whether or not pdrorg can be executed on an RDAREA that has been added to a rebalancing table

-k optionTable status
RDAREA addition up to pdrbal executionduring pdrbal processing#
(return code = 4)
After completion of pdrbal processing
(return code = 0)
ExistingAddedExistingAdded
unldY--YYY
rorg, reldBy tableY--Y#Y#Y
By RDAREAY------Y
ixmkY--YYY
ixrcY--YYY
ixorY--YYY

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 pdrbal 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:

  1. Execute pdrorg (-k unld).
  2. Add an RDAREA with ALTER TABLE ADD RDAREA.
  3. Delete all rows with PURGE TABLE.
  4. Execute pdrbal.
  5. Execute pdrorg (-k reld)

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.

(k) Processing an RDAREA in frozen update status

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.

(l) Reloading a table with SEGMENT REUSE specified

When reloading a table, the SEGMENT REUSE specification is ignored.

(m) About a table that is row-partitioned by the hash function with the rebalancing facility

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.

(n) Compatibility in unload data files between the 32-bit and the 64-bit version of HiRDB

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.

(o) Executing pdrorg on temporary tables

pdrorg cannot be executed on temporary tables. If pdrorg is executed with a temporary table specified in the -t option, pdrorg issues the KFPL15231-E message and terminates with an error. In the case of a reorganization in units of schemas (all specified in the -t option), pdrorg skips processing of temporary tables defined in schemas.

(2) Notes

(a) Return code

The following are the pdrorg utility's return codes:

0: Normal termination

4: Normal termination (some of the processing was skipped)

8: Abnormal termination

(b) Collecting a synchronization point dump

The system cannot collect synchronization points during reorganization processing (pdrorg -k rorg), because a transaction is not settled until the process is completed. The process means an unload operation, a reload operation, or index creation.

If other UAPs are executing at the same time as the pdrorg command and a system failure occurs during processing, the time required for restart increases. For this reason, do not execute UAPs while pdrorg is executing, if possible.

For a reorganization or reload operation with the synchronization point specification, you can collect synchronization points at intervals of any number of lines. This reduces the time required for a restart in the event of abnormal termination, compared with a reorganization or reload operation without collection of synchronization points.

When only an unload operation (pdrorg -k unld) is performed, synchronization points can be collected even while the pdrorg command is executing because the database is not updated.

(c) Canceling processing during utility execution

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 is expected to finish within a known amount of time, but it does not) and to 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.14 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.

(d) Modifying definition information during utility execution

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.

(e) Results of utility execution

You can check the result of the database reorganization utility using the database condition analysis utility.

(f) Changing partitioning conditions for a partitioning key during reloading

An error will occur during a reload operation if you specify either of the following options when changing the partitioning key partitioning condition:

(g) Using a multi-volume MT

Because MTguide is used for the volume switching operation when multi-volume magnetic tape is used, MTguide must be installed at the server machine.

(h) Status of an index during the reinitialization of the RDAREA

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.

(i) Using the system switchover facility

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.

(j) Abnormal termination of pdrorg during creation of an unload file

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.

(k) Unloading and reloading between systems

The following notes apply to unloading and reloading tables between systems:

(l) Modifying the partitioning key range or the hash function

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 9 System Operation Guide.

(m) Unloading and reloading using identical table definitions

If the table definitions are the same, a table unloaded from a HiRDB single server configuration can be reloaded to a table in a HiRDB parallel server configuration, 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 configuration 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 configuration.

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. 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.

(n) Using large files

Use of large files enables you to handle files whose size is 2 gigabytes or greater. Table 8-45 Whether large files can be used for pdrorg processing 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 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, 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-45 Whether large files can be used for pdrorg processing

File typeAvailability of large file system
Unload data fileY
LOB data unload fileY
Index information fileY
Work file for sortingY
MT attribute definition fileN
Process results fileY

Y: Can be used.

N: Cannot be used.

(o) Reloading a table with columns of the abstract data type
(p) Whether batch index creation and index re-creation are supported for a plug-in index

The following table shows whether batch index creation and index re-creation are supported for a plug-in index:

Provided functionBatch index creationIndex re-creation
Plug-in index delayed batch creation facilityExecutableExecutable
Batch plug-in index creation partial recovery facilityExecutableExecutable
None of the these facilities availableNot executableExecutable

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).

(q) Index updated by the utility using the differential index function of the HiRDB Text Search Plug-in

When using the differential index function of the HiRDB Text Search Plug-in, the database reorganization utility updates the following indexes:

Index creation methodSpecification of environmental variable PDPLUGINNSUB
YNNot specified
Re-creating an index (-k ixrc specified)MMM
Creating an entire index (-k ixmk specified)MMM
Creating an index for additional data (-k ixmk specified)SMS

M: Updates the MASTER index.

S: Updates the differential index.

(r) List created using a table being reorganized or reloaded

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.

(s) Use of DVD-RAM devices

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).

(t) File media available during utility 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).

FileRegular fileHiRDB fileBlocked fixed-length tapeBlocked variable-length tape
Unload data fileYYYY
Unload data file for LOB dataYYYY
Index information fileYIfNN
Work file for sortingYNNN
Process results fileYNNN
Legend:
Y: Can be used.
If: Can be used if the delayed batch index creation facility is used.
N: Cannot be used.
(u) Messages displayed in the command execution window

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 the syslogfile. To obtain the correct messages, view the message log file or the syslogfile.

(v) Reorganizing a shared table

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 B.2 Lock mode for utilities.

(w) Reorganizing a table for which referential constraints or check constraints have been defined

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 9 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).

(x) Relationship with the facility for predicting reorganization time

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.

#: For example, if rollback occurs due to abnormal termination during reloading, empty database information may be accumulated and used for prediction.
(y) When reorganizing a table containing BLOB columns

If you reorganize a table containing BLOB columns, make sure that you do not rename any applicable RDAREA between the time the data is unloaded and the time you reload the data. An error results if you attempt to reload an unload data file acquired before the RDAREA was renamed to a table in the renamed RDAREA.

(z) Exporting a table that uses a CHAR type column with the UTF-16 character set specified as its partitioning key

A table cannot be exported if it contains a CHAR-type column with the UTF-16 character set specified that is used as the partitioning key for key range partitioning or matrix partitioning (key range partitioning with a boundary value specified) and if the length of the partitioning key exceeds the column's definition length.

(3) Using a file that contains a BOM

If you selected utf-8 or utf-8_ivs as the character encoding in the pdsetup command, you might be able to use a file with a BOM as the input file for pdrorg. The table below shows the files with a BOM that can be used 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-46 Whether or not files with a BOM can be used in pdrorg (applicable to UTF-8)

Control statementInput fileUse of file with a BOM
--Control information fileY
emtdefMT attribute definition fileN
unloadUnload data fileN
EasyMT information fileN
indexIndex information fileN
lobunldLOB data unload fileN
EasyMT information fileN
Legend:
Y: Can be used
N: Cannot be used
--: Not applicable