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[-w]|fixtext[,[padding-character][,{cr|crlf}]]}
(8) -g
(9) -j
(10) -u authorization-identifier
(11) -n {batch-output-local-buffer-sectors-count[,div]| batch-output-local-buffer-sectors-count,[div],local-buffer-sectors-count-for-random-access | ,,local-buffer-sectors-count-for-random-access}
(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][,information-message-output-suppression-level]
(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) Whether character codes are converted

When you execute pdrorg on a table containing a column for which a character set has been defined, whether character codes are converted depends on the -k and -W option values. The table below shows the option values and whether character codes are converted.

Table 8-14 Option values and whether character codes are converted

Specification of -kSpecification of -W
SpecifiedOmitted
UOC usedUOC not usedUOC usedUOC not used
rorgDuring unloading----YN
During reloading----YN
unldYYYY
reld------D
ixmk--------
ixrc--------
ixor--------
Legend:
Y: Character codes are converted
D: Character codes are converted if the character codes in the unload data file differ from the character codes of the column for which a character set has been defined
N: Character codes are not converted
--: Not applicable
(b) 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.

If you delay the OS time and execute batch index creation with the wrong index information file specified, data integrity might be lost between the table and the index. For details, see the HiRDB Version 9 System Operation Guide.

(2) -c object-of-processing

~<<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 authorization identifier or table identifier is enclosed between double quotation marks ("), it is treated as being case sensitive. If it is not enclosed between double quotation marks ("), it is treated as 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.
(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 you start reorganization of a data dictionary table, you should make a backup copy of the data dictionary RDAREA (if the unit is shut down during reload processing or a space shortage occurs in the RDAREA while a table used for RDAREA extension is being reloaded, you will need to use the backup copy to restore the RDAREA to its status before reorganization).

You can specify the following data dictionary table identifiers. Note that the same data dictionary table identifier cannot be specified more than once.

No.Table identifierRemarks
1SQL_PHYSICAL_FILES--
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--
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--
19SQL_DIV_COLUMN
20SQL_REFERENTIAL_CONSTRAINTS
21SQL_ALIASES
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_USAGE_PRIVILEGESTable used by the system
41SQL_IOS_GENERATIONSNone
42SQL_TRIGGERS
43SQL_TRIGGER_ACTCONDTable used by the system
44SQL_TRIGGER_COLUMNSNone
45SQL_TRIGGER_DEF_SOURCE
46SQL_TRIGGER_USAGE
47SQL_PARTKEY
48SQL_PARTKEY_DIVISION
49SQL_AUDITS
50SQL_AUDIT_REGULARIZETable used by the system
51SQL_KEYCOLUMN_USAGENone
52SQL_TABLE_CONSTRAINTS
53SQL_CHECKS
54SQL_CHECK_COLUMNS
55SQL_DIV_TYPE
56SQL_SYSPARAMS
57SQL_DB_MANAGEMENTTable used by the system
58SQL_DB_STATE_ANALYZED
59SQL_PUBLICVIEW_SAME_USERS
60SQL_INDEX_XMLINFNone
61SQL_SEQUENCES

(4) -r RDAREA-name

~<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 between double quotation marks ("), it is treated as being case sensitive. If it is not enclosed between double quotation marks ("), it is treated as all uppercase letters. Also note that if an RDAREA name contains a space, the entire RDAREA name must be enclosed between 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 8.4.3 Whether 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

~<<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.14 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. 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 9 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 = (1,328 + 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 9 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 HiRDB Version 9 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 configuration.
Notes
  1. When you specify the index information output mode (-i n), you need to execute batch index creation (-k ixmk) with pdrorg after reloading data into 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.
  3. When you specify the index information output mode (-i n), make sure that you do not rename the index (with ALTER INDEX) until batch index creation (-k ixmk) is completed. Once you rename the index, batch index creation can no longer be performed. If you have renamed an index before batch index creation was completed, use ALTER INDEX to restore the original index name, perform batch index creation, and then rename the index.
  4. When you specify the index information output mode (-i n), make sure that you do not rename an RDAREA containing the index until batch index creation (-k ixmk) is completed. Once you rename an RDAREA, batch index creation can no longer be performed. If you have renamed an RDAREA containing an index before batch index creation was completed, use pdmod to restore the original RDAREA name, perform batch index creation, and then rename the RDAREA.
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-20 Index creation method for a partitioning key index and non-partitioning key index 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-20 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. When you specify -i c or -i n, the utility creates as many index information files as number of indexes [Figure]RDAREAs storing the table. Because these files are opened at the same time during data reloading, their number might exceed the maximum number of files permitted per process. If the maximum number is exceeded, performance might suffer, so we recommend that you perform data loading for each RDAREA.
    The maximum number of file descriptors that can be used per process is the physical limit or 7,872, whichever is smaller.
(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[-w]|fixtext[,[padding-character][,{cr|crlf}]]}

Specifies that the unload data file is used as the input data file to the database load utility. This option is also specified to export tables and indexes.

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.

If you export data, specify -W bin -w.

For details about the format of the unload data file that is output by pdrorg specifying the -W option, see 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 ~<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), _ (underscore)
  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 space, enclose the entire separator character in double quotation marks.
{cr|crlf}
Specifies the line break 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 line break code.
crlf: Outputs 0x0d, 0x0a as the line break code.
When specifying either cr or crlf by omitting a separator character, use a comma to separate them (for example -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 9 SQL Reference.
For the output format when the sup option is specified, see 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[ -w]
Specifies that the output data is in binary format.
You must also specify the -w option when you export data.
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 line break code to be used.
cr:
Use the 1-byte line break code (0x0a).
crlf:
Use the 2-byte line break 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.If this option is specified, hirdb cannot be specified in the -f option.
  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 edition of HiRDB to the Windows edition, 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 a column of the CHAR type for which a UTF-16 character set has been specified is used as a partitioning key for key range partitioning or matrix partitioning of a table (key range partitioning with a boundary value specified) and the length of the partitioning key exceeds the defined length, that table cannot be exported.
  7. If you specify the -W option for a table with LOB columns or columns of an abstract data type with the LOB attribute, either the real data or the null value is output. The following table shows the relationship between the -W option and the data that is output.

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

    Column data type-W option
    bindat or extdatfixtext
    Pre-defined data typeBINARY typeReal dataReal dataNot output
    BLOB typeReal dataNull valueNot output
    OtherReal dataReal dataReal data
    Abstract data typeBLOB attribute#Real dataNull valueNot output
    OtherReal 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 configuration, 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 configuration, 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 configuration, the system assumes that the -g option is specified (even if it is omitted) when a table is reorganized in units of schemas or a table or index is exported with the -W bin -w option specified.
  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.
  4. If the -g option is specified for unloading a table divided and stored in multiple servers in a HiRDB parallel server configuration, unloading is performed sequentially for each server. In such a case, the performance of parallel processing is degraded.

(9) -j

Specifies that both the base table and the LOB data of a table for which an abstract data type with both LOB columns and the LOB attribute has been defined are to be reorganized, unloaded, or reloaded at the same time. The -j option is ignored if specified for a table containing no abstract data type with LOB columns and the LOB attribute.

Do not specify this option when you are reorganizing only the base table or only LOB data of a table for which LOB columns are defined. The following shows the control statements to be specified:

Target of reorganization processingControl statement to be specified
Base tableunload
LOB datalobunld

The -j option is always assumed for the following processing:

This option is mandatory for the following processing:

The tables below show whether reloading to the same table is supported and whether data can be migrated between separate tables.

Table 8-16 Whether reloading to the same table is supported

No.Unload file creation conditionWhether reloading is supported
Target tableOptionControl statement-j option
LOB column or LOB attribute-k-jOmittedSpecified
1Omittedrorg--unloadYY
2unld#1--unloadYY
3SpecifiedrorgOmittedunloadYN
4lobunldYN
5unload, lobunldNN
6Specified#2unloadNY
7unld#1Omitted#3unloadNot applicableNot applicable
8SpecifiedunloadNY
Legend:
--: Not affected by the -j option.
Y: Can be reloaded.
N: Cannot be reloaded (if attempted, a runtime error occurs).
#1
Unloading with the -W option specified is not subject to reloading.
#2
This includes when the -j option is assumed.
#3
Unloading is not supported in this case (if attempted, an error occurs).

Table 8-17 Whether data can be migrated between tables

No.Target tableMigration methodWhether data can be migrated
LOB column or LOB attributeExtracting dataAdding data
1Nopdrorg -k unld -W ...pdload ...Y
2pdrorg -k unld -j ...pdrorg -k reld -j ...YN
3pdrorg -k unld ...pdrorg -k reld ...YN
4Yespdrorg -k unld -W ...pdload ...Y
5pdrorg -k unld -j ...pdrorg -k reld -j ...YN
6pdrorg -k unld ...pdrorg -k reld ...N
Legend:
Y: Data can be migrated (recommended).
YN: Data can be migrated (not recommended).
N: Data cannot be migrated.

(a) Rules
  1. This option is ignored if specified for a table containing no LOB column.
  2. The system assumes that the -j option is specified (even if it is omitted) when a table is reorganized in units of schemas or a table or index is exported with the -W bin -w option specified.
  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 a HiRDB parallel server configuration, unloading is performed sequentially for each server at the expense of a reduction in parallel-processing performance.

(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. If an authorization identifier is enclosed between double quotation marks ("), it is treated as being case sensitive. If it is not enclosed between double quotation marks ("), it is treated as all uppercase letters. If you are using sh (Bourne shell), csh (C shell), or ksh (Korn shell), you must enclose the entire character string between single quotation marks (').

(11) -n {batch-output-local-buffer-sectors-count[,div]| batch-output-local-buffer-sectors-count,[div],local-buffer-sectors-count-for-random-access | ,,local-buffer-sectors-count-for-random-access}

Specifies the number of local buffer sectors that will be used when data is read from the database during an unload operation or when data is written to the database during a reload operation. If the -n option is specified but the number of batch output local buffer sectors or the number of local buffer sectors for random access is not specified, the corresponding local buffer is not used. If the -n option is omitted, the local buffer is not used. In such a case, the system uses the global buffer to output one page at a time.

batch-input/output-local-buffer-sectors-count ~<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.
For the number of batch input/output local buffer sectors, we recommend that you specify a value in the range from 16 to 32. Determine the maximum value to specify by using the formula 184 kilobytes [Figure] page length.
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 ~<unsigned integer> ((4-125000))
Specifies the number of random access local buffer sectors. The random access local buffer is used for index pages. Specify this operand when you create indexes in the index update mode. In the index update mode, index pages with a B-tree structure are referenced each time a row is stored. If you use the random access local buffer, you can reduce the effects on online processing of global buffer contention because these index pages are placed in the corresponding buffer.

The table below shows the recommended -n option value.

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

ConditionTable typeColumn definitionTable partitioning in server
YesNo
During unloadingFIX table---n x, div-n x
Non-FIX table---n is omitted.
During reloading and reorganizationFIX table---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 is omitted.
Other-n x, div-n x
Legend:
x: Number of batch output local buffer sectors
--: 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. The figure below shows the relationship between pdrorg and the buffer.

    Figure 8-21 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. Table 8-19 Relationships between conditions and the buffer to be used (during an unload operation by pdrorg) and Table 8-20 Relationships between conditions and the buffer to be used (during a reload operation by pdrorg) describe the relationships between the conditions and the buffer.

    Table 8-19 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--Y--Y--
    Non-FIX tableY--Y----
    RDAREA storing LOB columnY--Y----
    RDAREA storing LOB attributesY--Y----
    Index pageWhen searching key values from index (when -b is specified)Y------Y
    Directory pageY--Y----
Legend:
Y: Used
--: Not used

Table 8-20 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--Y--Y--
Non-FIX tableWhen a variable-length character string or BINARY string with column length greater than 256 bytes is definedY--Y----
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--Y----
Other--Y--Y--
RDAREA storing a LOB columnY--Y----
RDAREA storing LOB attributesY--Y----
Index pageWhen searching key values from table (when -i c or -i n is specified)--Y--Y--
When creating index concurrently (when -i s is specified)Y------Y
When executing batch index creation (when -i c is specified)Y------Y
Directory pageY--Y----
Legend:
Y: Used
--: 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
    = 1,024 [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
Specifies that EasyMT is to be used as the unload data file or LOB data unload file.
hirdb
Specifies that a HiRDB file is to be 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.

It might not be permissible to specify this option, depending on the index definition condition. The table below shows whether the -b option can be specified.

Table 8-21 Whether the -b option can be specified

ConditionWhether specifiable
Index typeDefinition condition
B-tree indexSubstructure index--N
OtherExceptional key value specifiedN
Columns composing the index contains a repetition columnN
OtherY
Plug-in index--N
Legend:
Y: Can be specified
N: Cannot be specified. If specified, KFPL15323-E is output.
--: Not applicable

(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. If an index identifier is enclosed between double quotation marks ("), it is treated as being case sensitive. If it is not enclosed between double quotation marks ("), it is treated as all uppercase letters. Also note that if an index identifier contains a space, the entire index identifier must be enclosed between 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--------

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

--: 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#--
Index information file allocated by HiRDBY#Y#
ixmkIndex information file created by pdload or pdrorgY--
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--

Y: Deleted.

--: 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][,information-message-output-suppression-level]

progress-message-output-interval ~<unsigned integer> ((1-1000)) <<10>>
Specifies in units of 10,000 rows the interval at which execution status messages are to be output. If you specify lvl2 in the -m option, the execution status message is also output to the work file for troubleshooting information.
Criterion
Specify this option to change the default value of 10,000 rows.
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.
information-message-output-suppression-level ~<<lvl0>>
Specifies an option for suppressing output of information messages to the standard output at the terminal that executed the utility, the syslogfile, and the message log file. Even when output to all three (standard output, the syslogfile, and message log file) is suppressed, troubleshooting information is output to a work file. The table below shows the available options and the message output destinations.

Table 8-22 Option values and message output destinations

Option valueDescriptionOutput destination
Standard outputsyslogfile and message log fileWork file
lvl0Output is not suppressedYYN
lvl1Output to the standard output is suppressedNYN
lvl2Output to the standard output, the syslogfile, and message log file is suppressedNNY
Legend:
Y: Messages are output
N: Messages are not output

Messages are output to a work file for troubleshooting purposes (to check the utility operation). We recommend that you normally specify lvl1 if there is no need to check the messages.
Work file
If you specify lvl2 in the -m option, a work file is created for each server each time the utility is executed. Therefore, we recommend that you specify a directory in the pd_tmp_directory operand so that the work files will be deleted periodically by HiRDB with the pdcspool command. The table below shows the work file output destinations.

Table 8-23 Work file output destinations

pd_tmp_directory operand in the system definitionTMPDIR environment variableWork file output destination
Specified--Directory specified in the pd_tmp_directory operand
OmittedSpecifiedDirectory specified in the TMPDIR environment variable
Omitted/tmp directory
Legend:
--: Not applicable

If output of information messages to the work file fails due to a file open error or an I/O error, an error message is displayed, but processing continues.
HiRDB creates work files automatically. The file names are always in the format PDRORG-aa-bb. The following explains the file names:
  • PDRORG: Fixed prefix indicating that the message was output by pdrorg.
  • aa: Process's server name when the inner replica facility is not used. If the inner replica facility is used, this character string consists of the process's server name, GN, and generation number.
  • bb: File creation time and process ID.

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

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

If an error, such as a communication error, occurs at the server where the command was executed, the command may stop responding and the application may stop. To 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

~<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 this option is omitted, the current RDAREA becomes subject to processing.
  2. 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.
  3. 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.

(19) control-information-filename

~<path name>

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 subsections 8.9.3 through 8.9.18. Note that comments cannot be specified in a control information file.

Control statementpdrorg's function
Table reorgTable unldgTable reldgData dict table reorgBatch IX crIX recrIX reorg
mtguide statement
emtdef statement
OOOO------
unload statementRRRR------
index statementO--O--RR#6R#6
idxname statement----------R#6R#6
idxwork statementO#1--O#1----OO
sort statementO#2--O#2--OO--
lobunld statementOO#3OO------
unlduoc statementOO--EEEE
tblname statement----O--------
array statement--O#4----------
unld_func statementO#5O#8----------
reld_func statementO#5--O--------
constraint statementO--O--------
option statementOO#4, #7OO#7O#7O#7O#7
blobtovarchar statement--O#4----------
fixtext_option statement--O#4----------
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.
--: 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--
LOB dataN--NY

Y: Can be specified.

N: Cannot be specified.

--: 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--
Index storage RDAREAsRN--O--

R: Required.

O: Optional.

N: Cannot be specified.

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