(a) spacelvl={0|1|3}
Specifies whether or not to execute space conversion on data during reorganization. Available space conversion levels are 0, 1, and 3.
- 0
- The system does not execute space conversion on the data.
- 1
- The system executes space conversion on the unload data during a reload operation.
- Criteria
- Specify this space conversion level in the following cases:
- To use the same space code consistently throughout the existing data
- When migrating table data to another system, the same space code is to be used consistently in the target system.
- 3
- The system executes the same space conversion as for 1 and the space conversion on unload data during an unload operation specifying the -W option.
- Criterion
- Specify this space conversion level to use the same space code consistently through the unload data, such as when using a UAP to process the unload data that is output with the -W option specified.
- Notes
- The following table shows whether or not space conversion is executed depending on the specification:
Type of pdrorg processing | Option | Value of spacelvl |
---|
-c | -k | -W | 0 | 1 | 3 |
---|
Reorganization | user | rorg | -- | N | Y#1 | Y#1 |
Unloading | user | unld | Specified | N | N | Y#2 |
Not specified | N | N | N |
Reloading | user | reld | -- | N | Y#1 | Y#1 |
Data dictionary table reorganization | dic | rorg | -- | N | N | N |
Y: Space conversion is executed.
N: Space conversion is not executed.
--: Not applicable.
- #1: If a table column being processed has the national or mixed character string type, the system converts space in the unload data during a reload operation as follows:
- National character string type
- The system converts two consecutive single-byte spaces in the unload data to one double-byte space, in units of two bytes from the top.
- Mixed character string type
- The system converts each double-byte space in the unload data to two consecutive single-byte spaces.
- #2: If a table column being processed has the national character string type, the system converts each double-byte space in the unload data to two single-byte spaces during an unload operation.
When the character codes are utf-8, the system converts one double-byte space (3 bytes) to two single-byte spaces. For MCHAR, the system sets trailing single-byte spaces up to the definition length. For MVARCHAR, the data length remains shortened.
- If the table contains an abstract data type, space conversion depends on the abstract data type reloading method shown as follows:
Abstract data type reloading method | Constructor function's argument data type | Value of spacelvl in option statement |
---|
Omitted or 0 | 1 or 3 |
---|
Reloading from the unload data file obtained by the plug-in's unload facility | -- | N | N |
Using a constructor function to reload from the unload data file obtained by the plug-in's constructor parameter reverse creation function | NCHAR, NVARCHAR, MCHAR, MVARCHAR | N | Y# |
Other | N | N |
Reloading without using any of these facilities | -- | N | N |
Y: Space conversion is executed.
N: Space conversion is not executed.
--: Not applicable.
- #: Space conversion is executed for the data type of the constructor function argument (argument type specified in the func operand of the reld_func statement for the corresponding abstract data type).
- If the column used as the cluster key or index key contains a national character string type and the data is to be unloaded in the order of key values, the system unloads the data in the order of their values stored in the database, regardless of the spacelvl option specification. Therefore, if space conversion is executed during an unload operation specifying the -W option, the output data may not be sorted in the order of key values. In this case, you can sort data in the order of key values by reorganizing the data without space conversion after the data load operation.
In the case of a table partitioned and stored at multiple servers, the data is not always unloaded in the order of key values regardless of the execution of space conversion, as is the case with the data unload operation in the order of cluster key or index key values. To store data in the order of key values, you need to sort the data after the unload operation.
- If you specify spacelvl=1 or spacelvl=3 in the option statement to reorganize a database that contains more than one kind of space code, the following events occur:
- The data unloaded in the order of key values may no longer be sorted in the order of key values. In this case, the data can be stored in the order of key values by reorganizing it again without space conversion.
- If the column used as the unique key index or primary key index is subject to space conversion, duplicate key values may result in the index. In this case, determine whether or not it must be a unique key or primary key and modify the corresponding data (such as by changing the key value or deleting unneeded rows) before executing space conversion.
- If the column used as the partitioning key is subject to space conversion, the storage RDAREA may change, depending on the partitioning conditions. In this case, specify the -g option so that data can be processed even if its storage location changes. If the table contains LOB columns, specify the -g and -j options.
(b) tblfree = {percentage-of-unused-space| ([percentage-of-unused-space], percentage-of-free-pages-in-segment)}
Specify this operand to change the percentage of free space specified with CREATE TABLE (value of PCTFREE) for storing data during a reload operation.
For the reorganization in units of schemas, the specified value takes effect on all tables.
- Criteria
- Suppose that you have specified a non-zero value as the percentage of free space during table definition, and a space shortage has occurred while processing this table.
- In this case, if you execute pdrorg on the table in the RDAREA with no free space, a shortage of RDAREA may occur because the defined percentage of free space takes effect during the execution of pdrorg. In this case, if you specify the tblfree operand, you can complete the pdrorg processing without temporarily expanding the RDAREA.
- percentage-of-unused-space
- You can specify a value in the range from 0 to 99.
- percentage-of-free-pages-in-segment
- You can specify a value in the range of 0 to 50.
(c) idxfree = percentage-of-unused-area
If you are creating indexes, specify this operand to change the percentage of unused area specified with CREATE INDEX (value of PCTFREE). The permitted value range is from 0 to 99.
For the reorganization in units of schemas, the specified value takes effect on all tables.
- Criteria
- Suppose that you have specified a non-zero value as the percentage of unused space during index definition and a space shortage has occurred while processing this table. In this case, if you execute pdrorg on the indexes in the RDAREA with no free space, a shortage of RDAREA may occur because the defined percentage of free area takes effect during the execution of pdrorg. In this case, if you specify the idxfree operand, you can complete the processing without temporarily expanding the RDAREA.
(d) job = {job-name[, lines-count-between-synchronization-points][, CLR]|,,CLR}
Specify this operand to execute reorganization or reloading with the synchronization point specification.
Reorganization with the synchronization point specification or reload operation with the synchronization point specification is a method of reorganizing or reloading data in which a transaction is settled each time the specified number of data items are stored. If an error occurs while data is stored in the log acquisition mode (-l a) or pre-update log acquisition mode (-l p), this method enables you to restore the database in a short period of time without having to store data again from the beginning. For details about the error handling procedure, see When a utility terminates abnormally during execution of a reorganization with synchronization points set in the HiRDB Version 9 System Operation Guide.
- job-name ~<alphanumerics> ((1-3))
- Specify the name of the job that executes reorganization or reload operation with the synchronization point specification.
- This job name is used during re-execution in the event the utility terminates abnormally during reorganization or during a reload operation. If this job name is the same as a pdrorg job name, a malfunction results. Therefore, make sure that the specified job name is unique in the HiRDB system.
- lines-count-between-synchronization-points ~((1-1000)) <<100>>
- Specify the number of data items as an interval at which a transaction is to be settled. For example, a value of 100 sets a synchronization point at every one million data items.
- A small value settles a transaction after a small number of data items; therefore, rollback processing would take a short time in the event of abnormal termination. However, transaction generation and settlement and process restart occur frequently, resulting in a large overhead. On the other hand, a large value has a little effect on the performance, but the error recovery time would be long.
- An error results if you specify only the number of lines between synchronization points without specifying a job name.
- CLR
- Specify this operand if there is no need to re-execute pdrorg or if pdrorg is to be re-executed after clearing the current synchronization point information in the data base.
- If the utility terminates abnormally, the synchronization point information remains in the database. If this information is retained during the next execution, the utility may not function correctly.
- Note that if you specify CLR, the system clears all synchronization point information, whether it was specified by another user or utility.
- To determine if the synchronization point information is present in the database, use the database condition analysis utility (pddbst) to execute condition analysis in units of tables.
- Criterion
- This option is useful for loading a large amount of data, which takes a considerable amount of time to store the data. However, this has adverse effects on performance due to synchronization point processing. Additionally, for data pages, the number of pages required for storing data is greater than with normal reorganization because data storage begins in a new page at each synchronization point. Therefore, if there is not much free space in the RDAREA, we recommend that you do not use this option.
- Example of job operand specification
- Reorganizing or reloading data with a synchronization point specified for every one million data items
job=JOB,100 or job=JOB
- Clearing the current synchronization point information and specifying new synchronization points at every one million data items to execute reorganization or reload operation
job=JOB,100,CLR or job=JOB,,CLR
- Clearing the current synchronization point information and executing normal reorganization or reload operation
job=,,CLR
- Notes
- Reorganization with the synchronization point specification is not applicable to data dictionary tables.
- Synchronization point information is managed by the job name. Suppose that pdrorg(A) was executing reorganization or a reload operation with the synchronization point specification and it terminates abnormally. If you execute pdload(B) specifying the same job name for the same table, pdload(B) uses the synchronization point information that was left by pdrorg(A). Therefore, the job names must be controlled so that they are not duplicated.
- If reorganization or reload operation with the synchronization point specification terminates abnormally, you cannot execute the normal reorganization or reload operation (without the synchronization point specification) on the same table until you re-execute the reorganization or reload operation with the synchronization point specification with the same job name.
- The synchronization point information is managed in units of RDAREAs. For a row partitioned table, inconsistency occurs on the synchronization point information for the entire table in the following cases:
- Only some of the RDAREAs were re-initialized.
- An error occurred during reorganization or reload operation with the synchronization point specification in units of RDAREAs.
If the job name is the same, but the number of lines does not match, you cannot use the synchronization point information managed by each RDAREA for re-execution. If some of the RDAREAs have synchronization point information while the others do not, you can re-execute data loading using the retained synchronization point information. The following shows how the system handles reorganization or reload operation when the job name for synchronization point information is the same but the number of lines does not match:
Retained synchronization point information (number of lines between synchronization points) | Input begin line for reorganization or reload operation with synchronization point specification in units of tables |
---|
RDAREA 1 | RDAREA 2 | RDAREA 3 |
---|
0 | 0 | 0 | Item 0 |
0 | n | 0 | Item n |
n | 0 | m | Cannot be executed. |
n | m | p | Cannot be executed. |
0: No synchronization point information retained
n, m, p: Retained number of lines between synchronization points:
- Reorganization or reload operation with the synchronization point specification is not recommended if all the following conditions are met:
- A HiRDB parallel server configuration is used.
- The table is partitioned and stored at multiple servers.
- The -g option is omitted.
In this case, processing takes place at each server; therefore, in the event of an error, some servers may be unloading while others are reloading or some server processing may have already been terminated. In such cases, the user must specify an appropriate re-execution method for each server in the event of abnormal termination, resulting in complex operations.
- If reorganization or reload operation with the synchronization point specification terminates abnormally, you cannot modify the index creation method during re-execution (-i option).
- If EasyMT is remounted before a reorganization or reload operation with synchronization point specification terminates abnormally, mount volume one again during the re-execution.
- If reorganization or reload operation with the synchronization point specification terminates abnormally, and you re-initialize RDAREAs or execute the PURGE TABLE statement before re-execution, the existing synchronization point information is deleted.
- If you are executing reorganization or reload operation with synchronization point specification only on a LOB column structure base table and the processing terminates abnormally, specify CLR during the re-execution. Otherwise, the search performance on the reorganized LOB columns may be reduced.
- The maximum number of data items that can be controlled during reorganization or reloading with the synchronization point specification is 4,294,960,000. To reorganize more data than this amount, consider one of the following operations:
- Reorganizing in units of RDAREAs
- Creating multiple input data files for pdload by specifying -W and then performing data loading with synchronization point specification more than once with pdload
(e) exectime=pdrorg-execution-monitoring-time
~<unsigned integer> ((0-35791394))
Specifies the pdrorg execution monitoring time in minutes. When 0 is specified, the execution time of pdrorg is not monitored. If pdrorg does not terminate within the specified amount of time, the system terminates the pdrorg control process forcibly and acquires error information in order to determine the cause of the no-response.
If this operand is omitted, a value is selected from the system definition in the order listed below and that value takes effect:
- pd_utl_exec_time
- pd_cmd_exec_time
- Criterion
- If you specify a monitoring time for nighttime batch processing, and pdrorg is placed in no-response status due to an error, such as a communication error (including a transient error) or a disk error, pdrorg will terminate abnormally. This enables you to detect an error and start recovery processing at an early stage.
- Guideline for the specification value
- The purpose of this operand is to detect a no-response error, not to monitor the execution time of a long transaction. Therefore, the specified time must be sufficient for processing the applicable table. For example, to monitor the execution time of a pdrorg that should terminate in 7 to 8 minutes, specify exectime=20, not exectime=10. If the amount of data increases proportionally, the specified value should be re-evaluated as needed.
(f) tblstatus=clear
Specify this option to forcibly place the table in the normal status during re-execution of table reorganization. In such a case, table reorganization will not restart.
- Criteria
- Specify this option to forcibly place the table in normal status such, as when you want to use SQL statements to reference an RDAREA (table) that has been placed in reload-not-completed data status.
- Specify this option to reset the table to normal status, such as when you executed reorganization (-k rorg) but the utility terminated abnormally during batch index creation and you restored the table and index statuses by performing only index creation (such as batch index creation (-k ixmk) or index re-creation (-k ixrc)) without re-executing the reorganization (-k rorg).
- Notes
- You can specify this operand only when you specify -k rorg. If you specify this operand at any other time, an error results.
- If you specify this operand to place the table in normal status and then you reorganize the table, reorganization will not restart. In such a case, you must restore the table from its backup copy. If you reorganize the table without restoring it first, the unload data file is overwritten and the number of data items becomes invalid (zero).
(g) unldenq={tblenq|rdenq|nowait}
This option specifies whether or not other transactions are to be permitted to update the table during unloading (changes the lock mode of the table that is to be unloaded). Note that only the areas of the table that are not being accessed can be updated during unloading.
- tblenq
- Allows other transactions only to reference the table. In this case, the table is placed in the protected retrieval mode and the RDAREA is placed in the shared retrieval mode.
- rdenq
- Allows other transactions only to reference the RDAREA. In this case, the table is placed in the shared retrieval mode and the RDAREA is placed in the protected retrieval mode.
- nowait
- Allows other transactions to reference the table and update the RDAREAs that are not subject to unloading. In this case, only the RDAREA is placed in the shared retrieval mode, enabling the table being unloaded to be updated. Use this operand only when there is a guarantee that the table to be unloaded (RDAREA) will not be updated, because if data loading or reloading is performed using the unload data file created by unload processing at the same time that update processing is being performed, data duplication or data loss may occur.
- Example
- If only table T1 in RDAREA1 (shaded part) is unloaded with the table and RDAREA layout shown below, whether or not other transactions can update the table and RDAREA depends on the operand specification.
![[Figure]](figure/zu080620.gif)
- Explanation
- The following table shows whether or not other transactions can update the table and RDAREA:
unldenq value | Order during unloading | Lock mode | Whether other transactions can execute |
---|
RDAREA | Table | NW TBL | Table T1 | Table T2 |
---|
Table | Index | RDAREA1 | RDAREA2 | RDAREA1 | RDAREA2 |
---|
REF | UP | REF | UP | REF | UP | REF | UP |
---|
tblenq | Key | SR | SR | PR | -- | Y | N | Y | N | Y | Y | Y | Y |
Physical | -- |
rdenq | Key | PR | PR | SR | -- | Y | N | Y | Y | Y | N | Y | Y |
Physical | -- |
nowait | Key | SR | SR | -- | SR | Y | N# | Y | Y | Y | Y | Y | Y |
Physical | -- |
- Legend:
- NW TBL: NOWAIT Table
- REF: Referencing
- UP: Updating
- SR: Locked in the shared retrieval mode
- PR: Locked in the protected retrieval mode
- --: Not locked
- Y: Executable
- N: Not executable
- #: The user must ensure that data for table T1 in RDAREA1 will not be updated.
(h) charset={utf-16be|utf-16le}
Specifies the endian of the unload data file when UTF-8 column data is unloaded to a UTF-16 unload data file. You can specify this operand when the following conditions are satisfied:
- -W option is specified
- The unload data file is a pdload unload file in extended DAT format or fixed-size data format
- utf-16be: Unload data file uses big endian.
- utf-16le: Unload data file uses little endian.
If this operand is omitted, the utility assumes that the unload data file uses the UTF-8 character encoding.
When this operand is specified, pdrorg converts the character codes in order to unload UTF-8 column data as UTF-16 data. The following table lists the data types that result in character code conversion:
No. | Data type | Relationship between specification of the charset operand and character code conversion |
---|
Omitted | Specified |
---|
utf-16be | utf-16le |
---|
1 | CHAR, VARCHAR | No character set specified | N | YC | YC |
2 | EBCDIC character set | YU | N | N |
3 | UTF-16 character set | YC | N | YU |
4 | NCHAR, NVARCHAR | N | N | N |
5 | MCHAR, MVARCHAR | N | YC | YC |
6 | BINARY type | N | N | N |
7 | BLOB | N | N | N |
8 | Abstract data type | CHAR, VARCHAR | N | YC | YC |
9 | NCHAR, NVARCHAR | N | N | N |
10 | MCHAR, MVARCHAR | N | YC | YC |
11 | BINARY type | N | N | N |
12 | BLOB | N | N | N |
13 | Other | N | N | N |
14 | Other | N | N | N |
- Legend:
- YC: Character codes are converted and data lengths are changed during conversion.
- YU: Character codes are converted but data lengths remain unchanged by conversion.
- N: Character codes are not converted.
If the data lengths differ before and after conversion, pdrorg adjusts the data length as described below.
- When the data length is the same before and after conversion:
- When using a pdload unload file in extended DAT format (fixed-length character attribute column)
The utility left-aligns the data and unloads it up to the column definition length padded with spaces.
- When using a pdload unload file in extended DAT format (variable-length character attribute column)
The utility unloads the converted data as is.
- When using a pdload unload file in fixed-size data format
The utility left-aligns the data and unloads it up to the column definition length padded with the padding character.
- When the data length changes after conversion:
- When using a pdload unload file in extended DAT format (fixed-length character attribute column)
The utility left-aligns the data and unloads it up to twice the column definition length padded with spaces.
- When using a pdload unload file in extended DAT format (variable-length character attribute column)
The utility unloads the converted data as is.
- When using a pdload unload file in fixed-size data format
The utility left-aligns the data and unloads it up to twice the column definition length padded with the padding character.