8.9.2 Options

Organization of this subsection
(1) -k processing-type
(2) -c object-of-processing
(3) -t {[authorization-identifier.]table-identifier|[authorization-identifier.]all|table-identifier[,table-identifier]...}
(4) -r RDAREA-name
(5) -l log-acquisition-method
(6) -i index-creation-method
(7) -W {{dat|extdat}[,[separator-character][,{cr|crlf}][,sup]]|bin|fixtext[,[padding-character][,{cr|crlf}]]}
(8) -g
(9) -j
(10) -u authorization-identifier
(11) -n [batch-input/output-local-buffer-sectors-count],[div],[random-access-local-buffer-sectors-count]
(12) -f unload-data-file-type-or-LOB-data-unload-file-type
(13) -b unload-sequence
(14) -s
(15) -o
(16) -m progress-message-output-interval
(17) -X response-monitoring-time-for-server-to-server-communication
(18) -q generation-number
(19) control-information-filename

(1) -k processing-type

Specifies the type of processing to be executed by pdrorg:

rorg
Reorganize a table.
unld
Unload a table.
reld
Reload a table.
ixmk
Execute batch index creation.
ixrc
Re-create an index.
ixor
Reorganize an index.
(a) Notes

When -k rorg is specified, the utility executes unload, reload, and index creation processing (if -i c specified) sequentially in this order. If the utility terminates abnormally during the processing, the utility stores information about the processing that resulted in abnormal termination as a status in the database table. Therefore, when the same processing is restarted, the utility starts from the processing that resulted in abnormal termination. This means that the utility does not always start with unload processing. For details, see 8.4.4 Handling of a reloading error during table reorganization.

To always start with unload processing, regardless of whether the previous pdrorg terminated normally or abnormally, consider using the type of operation that executes -k unld and -k reld separately.

(2) -c object-of-processing

[Figure]<<user>>

Specifies the type of table to be reorganized:

dic
Reorganize a data dictionary table.
user
Reorganize a table.

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

Specifies the name of the table being processed.

(a) User-defined table

To specify a user-defined table, use the following format:

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

Specify either only one table or all to reorganize all tables owned by the schema. Reorganizing all tables owned by a schema is called reorganizing in units of schemas.

If you omit the authorization identifier, the system assumes the user name used to establish connection with HiRDB.

Rules
  1. You cannot specify a view table.
  2. If an authentication identifier or table identifier is enclosed in double quotation marks ("), the command treats it as being case sensitive. If it is not enclosed in double quotation marks ("), the command treats it as in all uppercase letters. If you are using sh (Bourne shell), csh (C shell), or ksh (Korn shell), you must enclose the entire identifier in single quotation marks (').
Rules for reorganization in units of schemas
  1. Reorganization in units of schemas is applicable to -k rorg, -k unld, and -k reld, that does not involve reorganization or reloading with the synchronization point specification.
  2. If you unload a table in units of schemas, you can use the obtained unload data file to reload the table in units of tables. On the other hand, if you unload a table in units of tables, you cannot use the obtained unload data file to reload the table in units of schemas.
  3. Tables are unloaded or reloaded in the order that their table identifiers are specified.
  4. If an error occurs while processing a table, the system terminates the processing at that point. If an error occurs while reloading a table, reload the affected table as well as any unprocessed table in units of tables. Reorganization has been completed for the tables for which processing has terminated normally.
  5. If you are reorganizing a table in units of schemas, use the pdhold command to shut down the RDAREAs storing the table in the schema so that the UAP that accesses the table under reorganization is not placed in wait status.
  6. If tables contained in the schema are different between unload and reload operations, the system handles them as follows:
    Tables during unload operation: (T1, T3, T5, T7)
    Tables during reload operation: (T1, T2, T3, T5)
    T1, T3, T5: Are reloaded.
    T2: Is skipped because it was not in the schema during unload operation.
    T7: Is not reloaded because it was not in the schema during unload operation.
  7. If there is an external table for the schema, the external table will not be processed.
(b) Data dictionary tables

To specify data dictionary tables, use the following format:

-t table-identifier[,table-identifier]...

You can specify as many table identifiers as there are data dictionary tables. You cannot specify an authorization identifier. To reorganize all data dictionary tables, omit the -t option.

Before starting reorganization of a data dictionary table, make a backup copy of the data dictionary RDAREA (if a space shortage occurs in the RDAREA during reload processing, the backup copy can be used to restore the RDAREA to its status before reorganization and then you can expand the RDAREA).

You can specify the following data dictionary table identifiers:

No.Table identifierRemarks
1SQL_PHYSICAL_FILES[Figure]
2SQL_RDAREAS
3SQL_TABLES
4SQL_COLUMNS
5SQL_INDEXES
6SQL_USERS
7SQL_RDAREA_PRIVILEGES
8SQL_TABLE_PRIVILEGES
9SQL_DIV_TABLE
10SQL_DIV_TABLE_REGULARIZETable used by the system
11SQL_INDEX_COLINF[Figure]
12SQL_TABLE_STATISTICS
13SQL_COLUMN_STATISTICS
14SQL_INDEX_STATISTICS
15SQL_VIEW_TABLE_USAGE
16SQL_VIEWS
17SQL_VIEW_DEFTable used by the system
18SQL_DIV_INDEX[Figure]
19SQL_DIV_COLUMN
20SQL_REFERENTIAL_CONSTRAINTSTable used by the system
21SQL_ALIASES[Figure]
22SQL_ROUTINES
23SQL_ROUTINE_RESOURCES
24SQL_ROUTINE_PARAMS
25SQL_DATATYPES
26SQL_DATATYPE_DESCRIPTORS
27SQL_TABLE_RESOURCES
28SQL_PLUGINS
29SQL_PLUGIN_ROUTINES
30SQL_PLUGIN_ROUTINE_PARAMS
31SQL_REGISTRY_CONTEXTTable used by the system
32SQL_REGISTRY_KEY
33SQL_INDEX_TYPESNone
34SQL_INDEX_RESOURCES
35SQL_INDEX_DATATYPE
36SQL_INDEX_FUNCTION
37SQL_TYPE_RESOURCES
38SQL_INDEX_TYPE_FUNCTION
39SQL_EXCEPT
40SQL_FOREIGN_SERVERS
41SQL_USER_MAPPINGS
42SQL_USAGE_PRIVILEGESTable used by the system
43SQL_IOS_GENERATIONSNone
44SQL_PARTKEY
45SQL_PARTKEY_DIVISION
46SQL_AUDITS
47SQL_AUDIT_REGULARIZETable used by the system
48SQL_TRIGGERSNone
49SQL_TRIGGER_ACTCONDTable used by the system
50SQL_TRIGGER_COLUMNSNone
51SQL_TRIGGER_DEF_SOURCE
52SQL_TRIGGER_USAGE
53SQL_KEYCOLUMN_USAGE
54SQL_TABLE_CONSTRAINTS
55SQL_CHECKS
56SQL_CHECK_COLUMNS
57SQL_REFERENTIAL_CONSTRAINTS
58SQL_DIV_TYPE
59SQL_SYSPARAMS
60SQL_DB_MANAGEMENTTable used by the system
61SQL_DB_STATE_ANALYZED
62SQL_PUBLICVIEW_SAME_USERS

(4) -r RDAREA-name

[Figure]<identifier> ((1-30))

For a row-partitioned table stored in multiple RDAREAs, this option specifies the name of the RDAREA being reorganized.

When this option is omitted, the utility assumes that all data for the row-partitioned table is being reorganized.

(a) Rules
  1. If an RDAREA name is enclosed in double quotation marks ("), the command treats it as being case sensitive. If it is not enclosed in double quotation marks ("), the command treats it as in all uppercase letters. If an RDAREA name contains a space, enclose the RDAREA name in double quotation marks ("). If you are using sh (Bourne shell), csh (C shell), or ksh (Korn shell), you must enclose the entire set of RDAREA names in single quotation marks (').
  2. If you have unloaded a table partitioned by the flexible hash partitioning method in units of RDAREAs and changed the hash function or added an RDAREA, the system ignores the hash function during the reload operation and stores the data as is in the original RDAREAs.
    If you have unloaded a table partitioned by the key range or FIX hash partitioning method in units of RDAREAs, you cannot reload the table using different partitioning conditions.
    To reorganize a table by changing the hash function, adding an RDAREA, or modifying the partitioning conditions, use the method of reorganization in units of tables, specifying the -g option. To change the hash function or add an RDAREA, use the ALTER TABLE definition SQL statement. To determine whether or not reloading is permitted when the table definition for an unload table does not match the table definition of the reload table, see Section 8.4.3 Whether or not reloading is permitted when the table definitions of unload table and reload table do not match.
  3. When reorganizing tables in units of schemas, you cannot specify the -r option.
  4. If you are specifying a replica RDAREA, specify its original RDAREA name in this option and also specify the target generation number in the -q option.

(5) -l log-acquisition-method

[Figure]<<p>>

Specifies the method for acquiring the database update log during the execution of pdrorg.

If pdrorg terminates abnormally during execution, the database is not restored to its status immediately before execution of pdrorg even when a database update log has been acquired. For details about how to handle abnormal termination of pdrorg, see 8.13 Database status in the event of an error and recovery method.

a
Indicates the log acquisition mode in which the system collects database updating log information required for rollback and rollforward.
Criteria
This mode is suitable for reorganizing a small amount of table data. When pdrorg is executed in the log acquisition mode, there is no need to make backups either before or after execution of the utility, but performance is lower than in the other modes.
p
Indicates the pre-update log acquisition mode in which the system collects database updating log information required for rollback, but not the database updating log information required for rollforward.
Criteria
This mode is suitable for reorganizing a large amount of table data. When pdrorg is executed in pre-update log acquisition mode, execution time is faster than in the log acquisition mode. However, you need to make backup copies after the execution of pdrorg to protect against possible media errors.
If an error occurs during execution of pdrorg, pdrorg restores the database to the synchronization point immediately preceding the error. This prevents RDAREAs from being placed in no-log shutdown status; however, the target table is not restored to its status immediately before execution of pdrorg.
n
Indicates the no-log mode in which the system does not collect database updating log information.
Criteria
When the pdrorg command is executed in the no-log mode, execution time is faster than in other modes. However, you must make backups before and after execution of the pdrorg command so that the database can be restored from its backup in the event of an error during utility execution or from its backup and log information in the event of a media error. This mode is suitable for reorganizing a large amount of table data.
Notes
  1. If an error occurs while pdrorg is executing in the no-log mode, you need to either restore the RDAREA that was shut down due to an error from its backup copy or re-initialize it.
  2. You cannot specify the no-log mode for a reorganization or reload operation with the synchronization point specification.
  3. For index re-creation (-k ixrc), the system assumes the log acquisition mode to protect the database under key search processing from being placed in error shutdown status. For index reorganization (-k ixor), the system assumes the pre-update log acquisition mode for the same reason. Therefore, the segment release log is output during key search processing, even if you specify the no-log mode.
(a) Notes
  1. If p or n is specified and the database can be restored from its previous backup copy and log information or from its unload data (applicable if the corresponding RDAREA contains only the table being reorganized), there is no need to make backup copies before the execution of pdrorg.
  2. For details about the operating method when p or n is specified (when the database updating log information is not collected), see the HiRDB Version 8 System Operation Guide.
  3. The transaction log is always collected by the transaction (T) that is generated by pdrorg, whether or not the -l option is specified. The system creates the following amount of transaction log information per server; the formula for determining the amount of log information during execution of pdrorg is shown below:
    Amount of log = (1328 + 176 [Figure] 3) [Figure] T + A (bytes)
    -k unld specified:
    T = (x[Figure] 2) + (y[Figure] 2)
    -k reld specified:
    T = (x[Figure] 3) + (y[Figure] 3) + (z[Figure] 2)
    -k ixrc specified:
    T = (x[Figure] 2) + (z[Figure] 2)
    -k ixmk specified:
    T = z[Figure] 2
    -k ixor specified:
    T = z[Figure] 3
    -k rorg specified:
    T = Value when -k unld is specified + value when -k reld is specified + (x + y)
    x: Number of tables (for reloading with synchronization point specification, the number of synchronization points)*
    y: Number of LOB column (LOB attribute) storage RDAREAs (when -j is specified, 1)*
    z: Number of indexes [Figure] number of index storage RDAREAs (this is not needed if -i s is specified)*
    * For processing in units of schemas, you must determine the value for all tables owned by the corresponding schema.
    A: Amount of system log information that is output according to database manipulation (amount of database update log information). This value depends on the value of the -l option. For details about determining the amount of system log information, see the HiRDB Version 8 Installation and Design Guide.
    If p or n is specified, the system collects the following amount of ENQ log information per server by lock control:
    ENQ log = (p + q + r) [Figure] T
    p: Number of table storage RDAREAs
    q: Number of LOB column (LOB attribute) storage RDAREAs
    r: Number of index storage RDAREAs
    Therefore, the system outputs as much system log information as equals record length of system log file [Figure] ENQ log information.
  4. When -k unld is specified, the log acquisition mode (-l a) is assumed regardless of the -l option value.
  5. If you use Real Time SAN Replication based on the log-only synchronous method and have executed pdrorg with -l p or -l n specified at the transaction execution site, you must execute the preparations for log application. For details about the preparations for log application, see the manual HiRDB Version 8 Disaster Recovery System Configuration and Operation Guide.

(6) -i index-creation-method

Specifies the index creation method.

When this option is omitted, the utility assumes c for a user-defined table and s for a data dictionary table.

c
Indicates the batch index creation mode in which the system executes batch index creation immediately after reloading the table.
Criterion
When you are reorganizing a large amount of table data, this mode enables you to create an index at high speed.
n
Indicates the index information output mode in which the system outputs only index information to the index information file.
Criterion
You can create an index at high speed by executing multiple batch index creation (-k ixmk) processes concurrently by pdrorg using the index information files that have been output. This method is especially effective for a table that is partitioned in multiple servers constituting a HiRDB/Parallel Server.
Notes
  1. If you specify the index information output mode, you need to execute batch index creation (-k ixmk) with pdrorg after reloading data to the table. If you use ALTER TABLE to add an RDAREA without having executed batch index creation, the index information file obtained is not usable. To add an RDAREA using ALTER TABLE, be sure to complete batch index creation beforehand. If you have added an RDAREA with ALTER TABLE, you need to execute index re-creation (-k ixrc) with pdrorg.
  2. If a plug-in index is defined for a table being reorganized, you cannot specify -i n unless the plug-in supports the batch plug-in index creation partial recovery facility.
s
Indicates the index update mode in which the system updates the index each time a row is stored.
Criterion
Specify this option when reorganizing a table with a small amount of data.
(a) Notes about index creation
  1. Figure 8-16 shows the method for creating a row-partitioned index and non-partitioned index. If a table is partitioned and stored in multiple RDAREAs at the same server, a row-partitioned index is stored in as many index storage RDAREAs as there are table storage RDAREAs, while a non-partitioned index is always stored in a single index storage RDAREA, regardless of the number of table storage RDAREAs.
    A row-partitioned index is stored in the same manner as for a table that is partitioned within the same server (RDAREAs INDEX1_1 and INDEX1_2 in the figure).
    A non-partitioned index is always stored in a single RDAREA in the server regardless of the number of table storage RDAREAs (RDAREAs INDEX1_3, INDEX2_1, and INDEX2_2 in the figure).

    Figure 8-16 Index creation method for a partitioning key index and non-partitioning key index

    [Figure]

    Explanation:
    Index creation depends on the -i option during table reorganization. For example, if you reorganize TABLE1_1 in units of RDAREAs, row-partitioned index INDEX1_1 is created (there is no effect on INDEX1_2). In this case, non-partitioned index INDEX2_1 is not created because it requires information about both TABLE1_1 and TABLE1_2 (the index information file is created only for TABLE1_1).
    To create a non-partitioned index, you also need to reorganize TABLE1_2 to create the index information file for this table, then execute pdrorg's batch index creation using the index information files for TABLE1_1 and TABLE1_2.
  2. After the reload operation is completed, the corresponding index is placed in unfinished status, thereby unusable until batch index creation is completed.
  3. If you specify -i c or -i n, the command creates as many index information files as the number of indexes [Figure]number of table storage RDAREAs. Because these files are opened simultaneously, the maximum number of files permitted per process may be exceeded. If this is the case, increase the pd_max_open_fds operand value in the system definition. If the value of the pd_max_open_fds operand is exceeded, check and, if necessary, revise the number of table partitions per server and the number of defined indexes, or specify -i s.
(b) Notes about specifying -i c or -i n

If -i c or -i n is specified and the index and idxwork statements are omitted, the system outputs the index information file to the /tmp directory using the following naming convention:

/tmp/INDEX-index-name-index-storage-RDAREA-name-unique-character-string

If pdrorg terminates abnormally, this file is not deleted. If you re-execute pdrorg, another index information file is created under a different name. Because this may result in a space shortage in the /tmp directory, you should delete unneeded index information files using the OS's rm command.

(7) -W {{dat|extdat}[,[separator-character][,{cr|crlf}][,sup]]|bin|fixtext[,[padding-character][,{cr|crlf}]]}

Specifies that the unload data file is used as the input data file to the database load utility.

When this option is specified, the system outputs table data to an unload data file in DAT, extended DAT, binary, or fixed-size data format.

For details about the format of the unload data file that is output by pdrorg specifying the -W option, see Section 8.3.3 Format of database load utility input files.

dat
Specifies that data is to be output in the DAT format.
extdat
Specifies that data is to be output in the extended DAT format.
When you output data in extended DAT format, you cannot specify more than one unload data file.
separator-character[Figure] <character string>
Specifies that the separator between data items is to be changed to a character other than the comma (,) when data is output in DAT or extended DAT format.
When this option is omitted, the utility assumes a comma as the separator character.
Rules
  1. You cannot specify any of the following characters because they are not permitted as separator characters for the input data file to the database load utility:
    * (asterisk), " (double quotation mark), _ (underline)
  2. The following characters, which can occur in data being unloaded, are not suitable for use as separator characters:
    [Figure]Uppercase letters (A-Z) and lowercase letters (a-z)
    [Figure]Numeric characters (0-9)
    [Figure]Characters that can conflict with the results of conversion of row data into a DAT format
    [Figure]Characters that can occur as character codes used in Japanese-language input:
    | \ [ ] ( ) { }[Figure](overbar)
    [Figure]Signs for numeric data (+ or -)
    [Figure]Hyphen (-) for date data input
    [Figure]Colon (:) for time data input
    [Figure]Period (.) for time and date interval data input
  3. To use the comma as the separator character, do not specify a separator character.
  4. If the separator character contains a blank, enclose the entire separator character in double quotation marks.
{cr|crlf}
Specifies the linefeed code to be used for data output in DAT or extended DAT format. When this option is omitted, the utility assumes cr.
cr: Outputs 0x0a as the linefeed code.
crlf: Outputs 0x0d, 0x0a as the linefeed code.
When specifying either cr or crlf by omitting a separator character, use a comma to separate them (e.g., -W dat,, cr).
sup
Specifies that trailing consecutive spaces are not to be output when column data of CHAR, NCHAR, or MCHAR type is output in DAT or extended DAT format.
For the CHAR or MCHAR type, the system compresses trailing single-byte spaces in a column with data that is less than the defined length. If the column data is all spaces, the system outputs one single-byte space.
For the NCHAR type, the system compresses trailing double-byte spaces in a column with data that is less than the defined length. If the column data is all spaces, the system outputs one double-byte space. The double-byte space depends on the character encoding in use. For details about the character encodings, see the HiRDB Version 8 SQL Reference.
For the output format when the sup option is specified, see Section 8.3.3 Format of database load utility input files.
Criteria
Specifying the sup option has the following effects:
  • The size of unload data file is reduced.
  • If you specify the suppress option for a CHAR, NCHAR, or MCHAR column during table definition, the system creates input data for the database load utility in such a format that all trailing spaces are already compressed.
Notes
  1. If the sup option is specified during an unload operation, the column data does not have an identical length in the unload data file. This point should be noted if the output data is to be sorted or edited with a UAP.
  2. You can store multi-byte characters and a value other than character codes in a column of the CHAR or MCHAR type; however, if you specify the sup option, the command compresses spaces unconditionally even when a multi-byte character string contains a space character, or a non-character code has the same value as the space character. If this happens, you can pad any column shorter than the defined length with spaces by re-executing data loading on the same table. In this case, do not specify the sup option because if the data is referenced or displayed by another program, a coding error occurs on the last double-byte character.
bin
Specifies that the output data is in binary format.
fixtext
Specifies that data in the fixed-size data format is to be output.
Examples
  1. When the padding character and {cr|crlf} are omitted
    -W fixtext
  2. When the padding character is [Figure] and {cr|crlf} is omitted
    -W fixtext,[Figure]
  3. When the padding character is [Figure] and cr is specified
    -W fixtext,[Figure],cr
  4. When the padding character is omitted and crlf is specified
    -W fixtext,,crlf
padding-character
Specifies the character to be used to fill out to the defined length when the column data or element data is the null value or when a variable-length character string is shorter than the defined length.
Rules
  1. The padding character must be a 1-byte character.
  2. When the padding character is omitted, the system assumes the space character (0x20). To use the space character as the padding character, omit padding-character.
  3. The asterisk (*), double quotation mark ("), and underscore (_) must not be specified as the padding character.
{cr|crlf}
Specifies the linefeed code to be used.
cr:
Use the 1-byte linefeed code (0x0a).
crlf:
Use the 2-byte linefeed code (0x0d, 0x0a).
(a) Criteria

Specify this option in the following cases:

(b) Rules
  1. An unload data file obtained with the -W option specified cannot be used for reload processing by pdrorg.
  2. If this option is specified, hirdb cannot be specified in the -f option.
  3. When reorganizing a table in units of schemas, you cannot specify the -W option.
  4. If the defined length of a table row exceeds 512 megabytes, data cannot be output in the DAT or fixed-size data format. In such a case, output the data in binary format.
  5. If you are transferring data from the UNIX version of HiRDB to the Windows version, or vice versa, output the data in the DAT format. If you need to convert the character encoding, do so first and then load data to the migration target.
  6. If you specify the -W option for a table with LOB columns or columns of abstract data type with LOB attribute, either real data or the null value is output, depending on the -j option specification. Table 8-13 shows the relationship between options -W and -j.

    Table 8-13 Relationship between options -W and -j

    Column data type-W option
    bindat or extdatfixtext
    Predefined data typeBINARY type-j option omittedReal dataReal dataNot output
    -j option specifiedReal dataReal dataNot output
    BLOB type-j option omittedNull valueNull valueNot output
    -j option specifiedReal dataNull valueNot output
    Other-j option omittedReal dataReal dataReal data
    -j option specifiedReal dataReal dataReal data
    Abstract data typeBLOB attribute*-j option omittedReal dataNull valueNot output
    -j option specifiedReal dataNull valueNot output
    Other-j option omittedReal dataReal dataNot output
    -j option specifiedReal dataReal dataNot output

* This is the argument type of constructor parameter reverse creation function that is specified in the unld_func statement.

(8) -g

For a HiRDB/Parallel Server, this option specifies that table data stored in multiple servers is to be unloaded to a single unload data file at one server.

For a HiRDB/Single Server, this option specifies that an unload data file is to be created on the utility special unit.

Even when a table contains a LOB column, you can unload to a single LOB data unload file.

(a) Criteria

Specify this option in the following cases:

(b) Notes
  1. With a HiRDB/Parallel Server, the system assumes that the -g option is specified during table reorganization in units of schemas, even if this operand is omitted.
  2. If the data unloaded in units of RDAREAs is reloaded in units of tables with the -g option specified, the data is deleted because the system treats other servers' RDAREAs as containing no data. In this case, do not specify the -g option.
  3. If the data unloaded in units of RDAREAs is reloaded in units of RDAREAs with the -g option specified, you cannot modify the table partitioning conditions.

(9) -j

Specifies that a table containing LOB columns is to be unloaded.

You an also specify this option to unload a table containing an abstract data type with LOB attribute or reorganize LOB storage RDAREAs.

When this option is specified, the utility outputs both the LOB column structure base table and the LOB data to the unload data file during the unload operation. During the reload operation, the utility reloads both the LOB column structure base table and the LOB data. This feature enables a table containing LOB columns to be migrated to another system, or its partitioning condition to be changed. This depends on the unloading process being able to be executed as described in Section 8.4.3 Whether or not reloading is permitted when the table definitions of unload table and reload table do not match.

(a) Rules
  1. This option is ignored if specified for a table containing no LOB column.
  2. The -j option is always assumed during reorganization in units of schemas.
  3. If you specify the -j option during an unload operation, be sure to specify this option during a reload operation.
  4. If the -g option is specified for unloading a table divided and stored in multiple servers in HiRDB/Parallel Servers, unloading is performed sequentially for each server at the expense of a reduction in parallel-processing performance.
  5. If a table with LOB columns is to be unloaded using the -W bin option, the -j option sets the LOB columns as the columns to be unloaded.

(10) -u authorization-identifier

Specifies the authorization identifier of the user executing pdrorg.

For details about the default value, see (b) Default value, as follows.

When this option is specified, the system displays a message requesting entry of a password. If no password is required, enter null in response to the message.

The system uses the specified authorization identifier to connect to HiRDB and to check execution privileges.

(a) Criterion

Specify this option to use a different authorization identifier than the one specified in the PDUSER environment variable.

(b) Default value

When this option is omitted, the system assumes the following authorization identifier and password:

  1. The system assumes the value of the PDUSER environment variable specified during execution of pdrorg. If you are executing the utility in the background with & attached by the shell, or in a remote shell environment where a response cannot be entered, be sure to specify the PDUSER environment variable. The following shows an example of specifying the PDUSER environment variable with the C shell:
    With password: setenv PDUSER'"authorization-identifier"/"password"'
    Without password: setenv PDUSER'"authorization-identifier"'
  2. If the PDUSER environment variable is not specified, the system assumes the login window's user name. Enter the password when a message is displayed requesting password entry. If no password is required, enter null in response to the message.
(c) Rules
  1. Do not specify this option if you are executing the utility in the background with & attached by the shell or in a remote shell environment where a response cannot be entered.
  2. The system treats an authorization identifier enclosed in double quotation marks (") as case sensitive; otherwise, the system treats it as in all uppercase letters. If you use the Bourne shell (sh), C shell (csh), or Korn shell (ksh), you must enclose the authorization identifier in single quotation marks (').

(11) -n [batch-input/output-local-buffer-sectors-count],[div],[random-access-local-buffer-sectors-count]

Specifies that a local buffer is to be used for reading data from the database during an unload operation or for writing data to the database during a reload operation. When this option is specified, the number of input/output operations can be reduced by the number of batch input/output operations because the system uses the local buffer to access databases.

When this option is omitted, the system uses the global buffer to access one page at a time during an input/output operation.

batch-input/output-local-buffer-sectors-count[Figure]<unsigned integer> ((2-4096))
Specifies the number of batch input/output local buffer sectors. The batch input/output local buffer is used for data pages.
We recommend that you specify a value in the range 16-32 for the number of batch input/output local buffer sectors. The guideline is 64 kilobytes/page size.
div
Specify div when all the conditions listed below are satisfied. If all these conditions are satisfied but div is not specified, the number of input/output operations may increase, thereby affecting performance adversely.
  • The partitioning conditions during the unload operation differ from those during the reload operation.
  • There are multiple RDAREAs for a table in the same server.
When div is specified, the required memory size increases because the system allocates as many buffer sectors as there are table partitions in the server.
random-access-local-buffer-sectors-count[Figure]<unsigned integer> ((4-125000))
Specifies the number of random access local buffer sectors. The random access local buffer is used for index pages.

Hitachi recommends that you change the combination of the number of batch input/output local buffer sectors, div, and the number of random access local buffer sectors according to the table definition. Table 8-14 shows the recommended -n option value.

Table 8-14 Recommended -n option specification (pdrorg)

ConditionTable typeColumn definitionTable partitioning in server
YesNo
During unloadingFIX table[Figure]-n x, div-n x
Non-FIX table[Figure]-n, , y
During reloadingFIX table[Figure]-n x, div-n x
Non-FIX tableA variable-length character string with a column length of 256 bytes or more or a BINARY column is defined.-n, , y
An abstract data type column is defined.-n x, div, y-n x, , y
A repetition column is defined.
Other-n x, div-n x
Legend:
x: Number of batch output local buffer sectors
y: Number of random access local buffer sectors
[Figure]: Not applicable
(a) Buffer used by pdrorg
  1. When the -n option is omitted, the pdrorg command uses the global buffer. In such a case, transaction performance is degraded for a process that uses the global buffer because a large amount of global buffer space is needed for reorganization. When the -n option is specified, there is no such buffer contention. Figure 8-17 shows the relationship between pdrorg and the buffer:

    Figure 8-17 Relationship between pdrorg and the buffer

    [Figure]

    Explanation:
    When only the global buffer is used (-n option is omitted), contention occurs on the buffer between pdrorg and UAPs.
    When both a local buffer and a global buffer are used (-n option is specified), no contention occurs on the buffer between pdrorg and UAPs. Note that during reorganization of a LOB column, the system uses the global buffer even if the -n option is specified.
  2. If you specify only the number of batch input/output local buffer sectors in the -n option, the system uses only one batch input/output local buffer sector for a single RDAREA. This may result in buffer contention because the system uses only one batch input/output local buffer sector even if there are multiple RDAREAs. If buffer contention occurs, the number of input/output operations increases, affecting performance adversely. In such a case, specify div. When div is specified, the system allocates as many batch input/output local buffer sectors as there are RDAREAs (one batch input/output local buffer sector for each RDAREA), thereby avoiding buffer contention.
  3. Even if a batch input/output local buffer is specified, the system may use the global buffer depending on conditions. Tables 8-15 and 8-16 describe the relationships between the conditions and the buffer.

    Table 8-15 Relationships between conditions and the buffer to be used (during an unload operation by pdrorg)

    ConditionRandom access local buffer
    Not specifiedSpecified
    Global bufferBatch input/output local bufferGlobal bufferBatch input/output local bufferRandom access local buffer
    Data pageRDAREA storing LOB column structure base tableFIX table[Figure]Y[Figure]Y[Figure]
    Non-FIX tableY[Figure]Y[Figure][Figure]
    RDAREA storing LOB columnY[Figure]Y[Figure][Figure]
    RDAREA storing LOB attributesY[Figure]Y[Figure][Figure]
    Index pageWhen searching key values from index (when -b is specified)Y
    Y
    [Figure][Figure][Figure]Y
    Directory page[Figure]Y[Figure][Figure]
Legend:
Y: Used
[Figure]: Not used

Table 8-16 Relationships between conditions and the buffer to be used (during a reload operation by pdrorg)

ConditionRandom access local buffer
Not specifiedSpecified
Global bufferBatch input/output local bufferGlobal bufferBatch input/output local bufferRandom access local buffer
Data pageRDAREA storing LOB column structure base tableFIX table[Figure]Y[Figure]Y[Figure]
Non-FIX tableWhen a variable-length character string or BINARY string with column length greater than 256 bytes is definedY[Figure]Y[Figure][Figure]
When a column of an abstract data type or a repetition column is defined and a row of data is too large to fit on one pageY[Figure]Y[Figure][Figure]
Other[Figure]Y[Figure]Y[Figure]
RDAREA storing a LOB columnY[Figure]Y[Figure][Figure]
RDAREA storing LOB attributesY[Figure]Y[Figure][Figure]
Index pageWhen searching key values from table (when -i c or -i n is specified)[Figure]Y[Figure]Y[Figure]
When creating index concurrently (when -i s is specified)Y[Figure][Figure][Figure]Y
When executing batch index creation (when -i c is specified)Y[Figure][Figure][Figure]Y
Directory pageY[Figure]Y[Figure][Figure]
Legend:
Y: Used
[Figure]: Not used
(b) Rules
  1. For rebalancing a table, performance may reduce considerably if the size of the global buffer is insufficient. If none of the columns exceeds 256 bytes in defined length, specify the number of batch output pages.
    If the local buffer specification does not take effect for some reason, such as the existence of a column with a defined length of 256 bytes or greater, allocate at least the following number of global buffer sectors:
    Number of buffer sectors required per RDAREA
    = 1024 [Figure] (number of table storage RDAREAs) [Figure] 2 + 3
  2. For rebalancing a table using the FIX hash partitioning method, the system allocates the buffer for the number of pages specified for each hash group. Therefore, more memory may be required than for a non-partitioned table or a table partitioned by other methods.

(12) -f unload-data-file-type-or-LOB-data-unload-file-type

Specifies the type of unload data file or LOB data unload file.

When this option is omitted, the utility assumes a regular file or streaming tape device as the unload data file.

easymt
EasyMT is used as the unload data file or LOB data unload file.
For a HiRDB/Parallel Server, if you have specified hirdb
A HiRDB file is used as the unload data file or LOB data unload file.

(13) -b unload-sequence

Specifies the order in which data is to be unloaded.

When this option is omitted, if the cluster key is defined for the table, the utility unloads the table data in the order of the cluster key values; otherwise, the utility unloads the table in the order that its data is stored.

You cannot specify this option for an index, plug-in index, or index for a repetition column with an exception value specification.

(a) Criteria

To unload a table with a cluster key defined, omit this option. If a cluster key has not been defined, but the table is to be unloaded and then a cluster key is to be defined for it before reloading, you must unload the table in the order of index values that is the same as the order of cluster key values (ASC or DESC).

Specify one of the following options as the order of data to be unloaded:

cluster
Unload data in the order of the cluster key values
index,index-identifier[,desc]
Unload data in the order of the index values.
desc unloads in the descending order of index values. Note that desc is applicable only to a single-column index.
primary[,,desc]
Unload data in the order of primary key values.
desc unloads data in the descending order of index values. Note that desc is applicable only to a single-column index.
physical
Unload data in the order it is stored.
Rules
  1. The system treats an index identifier enclosed in double quotation marks (") as case sensitive; otherwise, the system treats it as in all uppercase letters. If an index identifier contains a space, enclose the index identifier in double quotation marks ("). If using the Bourne shell (sh), C shell (csh), or Korn shell (ksh), you must enclose the index identifier in single quotation marks (').
  2. The desc option is applicable only to single-column indexes.
  3. When this option is specified, data is output to the unload data file in units of table storage RDAREAs in the order in the specified index, but it is not in the order in the index in the table as a whole. If you need an unload data file in which data is sorted in the order in the index (such as when you output data in DAT format by specifying the -W option), you must create an unload data file in units of RDAREAs for a row-partitioned table.
  4. You cannot specify the -b option for reorganization in units of schemas.
  5. When you are unloading LOB columns in the order of index values (by specifying index in the -b option), the index must be available. As in the following cases, if no index has been created, you need to create the index first and then unload the LOB columns:
  • n specified in the -i option
  • Reloading to a non-partitioned table in units of RDAREAs for which a non-partitioning key index is defined

(14) -s

When reorganizing a table with the suppress option specified for a CHAR, NCHAR, or MCHAR column, specify this option to remove all trailing consecutive spaces from columns during the unload operation.

(a) Effects of this option

By specifying this option, you can reduce processing time and the size of unload data file.

The following table shows the cases for which specifying the -S option can reduce processing time and the size of unload data file:

Status of unloaded tableStatus of reloaded table
Column without suppress optionColumn with suppress option
Condition ACondition BCondition ACondition B
Column with suppress optionCondition AMMYM
Condition B[Figure][Figure][Figure][Figure]

Condition A: Applicable column has no index defined and is neither key range-partitioned nor hash-partitioned.

Condition B: Applicable column has an index defined or is key range-partitioned or hash-partitioned.

Y: The unload and reload processing time is reduced and the size of the unload data file is also reduced.

M: The unload processing time is reduced and the size of the unload data file is also reduced, but the reload processing time is slower than the case indicated as .

[Figure]: There is no effect on the reduction of processing time or size of the unload data file.

(b) Notes
  1. For the following columns, specifying the -S option cannot reduce the processing time or the size of the unload data file, even if the suppression option is specified for the corresponding table:
    • Columns defining index
    • Columns partitioned by key range partitioning method
    • Columns partitioned by hash partitioning method
  2. If the definitions of these columns have changed during the reload operation, it takes more processing time than when the definitions have not changed.
  3. If the -W option is specified, the -s option, if specified, is ignored.

(15) -o

Specify this option to automatically delete the index information files after index creation processing is completed successfully.

(a) Criteria

A plug-in index in particular requires a large index information file. Such index information files require a large amount of disk space, if retained. You can specify this option to make sure that the index information files are always deleted after index creation processing.

(b) Notes

The following table shows the relationship between index information files and the -o option:

-k optionContents of index information file-o option
SpecifiedOmitted
rorg, reldIndex information file specified in the index statementY*[Figure]
Index information file allocated by HiRDBY*Y*
ixmkIndex information file created by pdload or pdrorgY[Figure]
Index information file created by the plug-in index delayed batch creation facilityYY
ixrc, ixorIndex information file allocated by HiRDB with the idxname specifiedYY
Index information file specified in the index statementY[Figure]

Y: Deleted.

[Figure]: Not deleted.

* For a table partitioned within the server, the index information file is not deleted if its non-partitioning key index is not partitioned within the server during reorganization in units of RDAREAs.

(16) -m progress-message-output-interval

[Figure]<unsigned integer> ((1-1000)) <<10>>

Specifies in units of 10,000 rows the interval at which execution status messages are to be output.

(a) Criterion

Specify this option to change the default value of 10,000 rows.

(b) Notes
  1. When the batch index creation mode is specified or batch index creation is underway during a table reload operation, the utility ignores this option, in which case messages are output at the time index creation is started and terminated.
  2. This option is ignored if ixrc is specified in the -k option. However, this option take effect for the processing of plug-in indexes.

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

[Figure]<unsigned integer> ((1[Figure]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 help you detect errors, pdrorg enables you to monitor a response time during communication for dictionary manipulation that is performed by the command.

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

Criteria
  • If you want to detect an error in fewer than 300 seconds in the event of a no-response from the server due to a communication error or unit down, specify a value less 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 for 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.

(18) -q generation-number

[Figure]<unsigned integer> ((0-10))

Specifies the generation number of the target RDAREA when the inner replica facility is used.

Specify the generation number as follows:

0: When the original RDAREA is to be processed
1 to 10: When the replica RDAREA with the specified generation number is to be processed
(a) Criteria

Specify this option when you are using the inner replica facility and processing an RDAREA that is not the current RDAREA.

(b) Rules
  1. When you are not using the inner replica facility, this option is not available.
  2. When this option is omitted, the current RDAREA becomes subject to processing.
  3. If you are processing a replica RDAREA, use the -r option to specify the name of the original RDAREA and the -q option to specify the generation number that is to be subject to processing.
  4. pdrorg checks the generation of the target RDAREA for errors. If the following checking results in an error, pdrorg displays a message and terminates with return code 8:
    Checking for an inconsistent generation number for a row-partitioned table:
    When a row-partitioned table is to be processed, pdrorg checks the generations of target RDAREAs.
    When the -q option is specified, pdrorg checks each target for the specified generation of RDAREA. When the -q option is omitted, pdrorg checks the current RDAREA at each target for any inconsistency in the generation number.
    Checking for any inconsistent generation number for the RDAREAs storing the table and index:
    pdrorg checks the generations of the RDAREAs storing the table and index.
    When the -q option is specified, pdrorg checks the target RDAREA for the specified generation. When the -q option is omitted, pdrorg checks all the target current RDAREAs to determine whether or not their generations are the same.
    Checking for any inconsistent replica status for the target RDAREA:
    When the -q option is specified, pdrorg checks the target RDAREAs to determine whether any non-current RDAREA is mixed in the current RDAREAs.
  5. The following table describes whether or not each file is available depending on the use of inner replica facility:
    File output conditionAvailability of file
    Inner replica facility not usedInner replica facility used
    Original RDAREAReplica RDAREA
    Same generation*1Different generation
    Index information fileInner replica facility not usedYYNN
    Inner replica facility usedOriginal RDAREAYYNN
    Replica RDAREANNYN
    Unload data fileInner replica facility not usedYYY*2Y*2
    Inner replica facility usedOriginal RDAREAYYY*2Y*2
    Replica RDAREAY*2Y*2YY*2
Legend:
Y: File can be used.
N: File cannot be used.
*1 This is the case where the generation information during file output is the same as the generation information specified in the -q option. When the -q option is omitted, this is the case where the generation information during file output is the same as the generation information for the current RDAREA.
*2 The file can be used when the following conditions are satisfied:

(19) control-information-filename

[Figure]<pathname>

Specifies the name of the control information file that contains pdrorg's control statements.

You can specify the following control statements in the control information file. For details about each control statement, see Sections 8.9.3 to 8.9.18.

Control statementpdrorg's function
Table reorgTable unldgTable reldgData dict table reorgBatch IX crIX recrIX reorg
mtguide statement
emtdef statement
OOOO[Figure][Figure][Figure]
unload statementRRRR[Figure][Figure][Figure]
index statementO[Figure]O[Figure]RR6R6
idxname statement[Figure][Figure][Figure][Figure][Figure]R6R6
idxwork statementO1[Figure]O1[Figure][Figure]OO
sort statementO2[Figure]O2[Figure]OO[Figure]
lobunld statementOO3O[Figure][Figure][Figure][Figure]
unlduoc statementOO[Figure]EEEE
tblname statement[Figure][Figure]O[Figure][Figure][Figure][Figure]
array statement[Figure]O4[Figure][Figure][Figure][Figure][Figure]
unld_func statementO5O8[Figure][Figure][Figure][Figure][Figure]
reld_func statementO5[Figure]O[Figure][Figure][Figure][Figure]
constraint statementO[Figure]O[Figure][Figure][Figure][Figure]
option statementOO4, 7OO7O7O7O7
report statementOOOOOOO
blobtovarchar statement[Figure]O4[Figure][Figure][Figure][Figure][Figure]
fixtext_option statement[Figure]O4[Figure][Figure][Figure][Figure][Figure]
Legend:
Table reorg: Table reorganization
Table unldg:Table unloading
Table reldg: Table reloading
Data dict table reorg: Data dictionary table reorganization
Batch IX cr: Batch index creation
IX recr: Index re-creation
IX reorg: Index reorganization
R: Required.
O: Optional.
[Figure]: Ignored, if specified.
E: Results in an error, if specified.

1 If -i s is specified, this statement cannot be specified.

2 If -i n or -i s is specified, this statement cannot be specified.

3 If the -W option is specified, this statement cannot be specified.

4 This statement can be specified only when the -W option is specified.

5 Both unld_fund and reld_func statements must be specified.

6 Specify either the index or idxname statement.

7 This statement cannot be specified for a table reorganization with the synchronization point specification.

8 This statement is required if a table with columns of abstract data type is to be unloaded with -W bin specified.

(a) Relationship between control statements and options
Table containing a LOB column or abstract data type column provided by a plug-in (LOB attribute)

When a table containing a LOB column is to be reorganized, the unit of reorganization depends on the specification of the unload and lobunld statements. The following table shows the relationship between the unit of reorganization for a table containing a LOB column and the -j option in the unload and lobunld statements:

Reorganization unitunload statementlobunld statement
-j specified-j omitted-j specified-j omitted
LOB column structure base table and LOB dataYYNY
LOB column structure base tableNYN[Figure]
LOB dataN[Figure]NY

Y: Can be specified.

N: Cannot be specified.

[Figure]: Not required.

Index re-creation or reorganization

When an index is re-created or reorganized, you can use the idxname statement to process the index in units of indexes or use the index statement to process the index in units of index storage RDAREAs. The following table shows the relationship between the index re-creation or reorganization and the index, idxname, and idxwork statements:

Index re-creation or reorganization unitindex statementidxname statementidxwork statementsort statement
-k ixrc-k ixor
IndexesNROO[Figure]
Index storage RDAREAsRN[Figure]O[Figure]

R: Required.

O: Optional.

N: Cannot be specified.

[Figure]: Ignored, if specified.

(b) Files and directories specified in the control statements

The following rules apply to files and directories specified in the control statements:

  1. You must grant access privileges to the HiRDB administrator in advance. In some cases when control statements or operands are omitted, the system assumes that the applicable file is to be created in the /tmp or /usr/tmp directory; for this reason, you must also grant access privileges to the /tmp or /usr/tmp directory.
  2. For the names of the unload data files, index information files, and EasyMT's MT attribute files, assign unique names, regardless of their hosts.