When updatable online reorganization is underway, the pdorend command applies update processing to RDAREAs that have been placed in online reorganization hold status.
Reflection for online reorganization involves the following processing:
- When the pdorend command is executed, it starts the online reorganization application process for each server. It then reads the update log information on the replica RDAREAs in online reorganization hold status from the system log and then updates the original RDAREAs.
- After completing the application process, the command switches a current RDAREA from a replica RDAREA to an original RDAREA, changes the status of the original RDAREA from online reorganization hold to shutdown release, and then places the replica RDAREA in command shutdown and closed status.
- During execution of pdorend, the command locks the replica group configuration of the corresponding RDAREAs in order to synchronize the status of original and replica RDAREAs. If update processing takes place on a replica RDAREA while the lock is in effect, the command applies that update processing to the original RDAREA.
- Once the lock is placed, the command places any transaction that attempts to access an RDAREA in the replica group being processed in wait status until the current RDAREA switchover processing is completed. After the current RDAREA has been switched over, the transaction is allowed to access the original RDAREA.
- When the -u option is specified, the command stops reflection processing and releases the original and replica RDAREAs from online reorganization hold status. In this case, the current RDAREA remains set to the replica RDAREA.
- If the RDAREA subject to online reorganization contains a referenced table, a referencing table, and a check constraint table, the referencing table and check constraint table are placed in check pending status. If the -p option is specified, neither the referencing table nor the check constraint table is placed in check pending status.
You can execute the pdorend command only when HiRDB Staticizer Option has been installed and the pd_max_reflect_process_count and pd_inner_replica_control operands have both been specified in the system definition.
- -s server-name[,server-name]...
<identifier> ((1-8))
Specifies the names of the servers that contains the replica groups in which update processing on replica RDAREAs is to be applied to the original RDAREAs.
- Rules
- When the -s option is omitted, the command executes reflection processing on all servers that contain RDAREAs in online reorganization hold status.
- If a duplicate server name is specified, the command eliminates it from processing.
- You can specify a maximum of 128 server names. If you specify more than 128 server names, the command ignores the excess server names.
- If you specified in the pdorbegin command a table whose rows are partitioned among servers, processing is performed at all applicable servers in the batch mode. Therefore, you specify the -s option only when you want to limit the servers on which reflection processing is to be performed.
- If the processing target includes a shared RDAREA and you specify the -s option, you must also specify the -u option. When the -u option is not specified, specifying the -s option results in an error.
- When both the following conditions are satisfied, execute the pdorend command with the -s option specified. If the command is executed with the -s option omitted, an error results.
- The pdorbegin command was executed with the -t option specified (except when a shared table is specified).
- Some HiRDB units or servers are stopped or stopping.
- -n maximum-retries-count
<unsigned integer> ((0-10)) <<3>>
In order to synchronize original and replica RDAREAs, the command locks the replica group configuration that contains RDAREAs in online reorganization hold status. This option specifies the maximum number of retries for each server in the event of a timeout on the locking process or on reflection processing after locking.
If the specified maximum number of retries is exceeded, the corresponding server processing terminates with an error. For details about the conditions under which retries occur, see Rules, below.
- -w maximum-wait-time-for-reflection-processing
<unsigned integer> ((60-3600)) <<600>>
Specifies the maximum wait time in seconds for applying to the original RDAREAs any update processing on the replica RDAREAs.
If the specified maximum wait time for reflection processing is exceeded, the server's processing is terminated with an error. If this occurs, either increase the value of the -w option or reduce the number of online application transactions while pdorend is executing.
- -t maximum-wait-time-for-transaction-processing
<unsigned integer> ((1-3600))
Specifies the maximum wait time (in seconds) for each server until the replica group configuration that contains the RDAREAs in online reorganization hold status is locked or until the reflection processing is completed after locking the replica group configuration in order to synchronize the original and replica RDAREAs.
When the -t option is omitted, the command assumes the value of the pd_lck_wait_timeout operand in the system definition. For details about the maximum wait time for transaction processing, see Rules, below.
- -m number-of-concurrent-online-reorganization-application-processes
<unsigned integer> ((1-8)) <<2>>
Specifies the number of concurrent online reorganization application processes for each back-end server that applies update processing on replica RDAREAs to original RDAREAs.
- Rules
- When the pdorend command is executed, as many online reorganization application processes are started as there is determined by the following formula:
Number of application processes to be started = M
S (processes)
M: Value of the -m option
S: Value of the -s option (if the -s option is omitted, the value of S is the number of back-end servers that contain RDAREAs in online reorganization hold status)
- Note that you must specify in advance in the pd_max_reflect_process_count operand in the system definition the number of online reorganization application processes that can be guaranteed in the entire HiRDB.
- Guidelines for specification value
- To determine the value to be specified, use the formula shown below. The system assumes a value of about 8 as the valid maximum number of concurrent processes due to bottlenecks such as reading log data.
Number of concurrent online reorganization application processes =
MIN(8,
(NLine
PLine/100)
Ntrn
(m + n)/n
TSql
)
NLine: Average number of lines updated by a single transaction for updating RDAREAs in a SDS or BES
PLine: Percentage of RDAREA updating subject to this processing in all RDAREA updating in the SDS or BESs (%)
Ntrn: Number of transactions per second for updating RDAREAs in the SDS or BESs
m: Time required for table reorganization (unit is the same as for n)
n: Time required for reflection processing (unit is the same as for m)
TSql: Average SQL execution time per line of RDAREA subject to this processing (seconds)
- For example, if NLine = 10, PLine = 20, Ntrn = 50, TSql = 0.003, and reflection processing time is about 1/5 of the time required for table reorganization, then the number of concurrent online reorganization application processes is 2.
- To avoid contention, we recommend that you set the number of concurrent processes to 1 in the following cases:
- When the ngram index of the table, which is subject to reflection processing, is updated frequently
- When an index with UNIQUE specified for a table which is subject to reflection processing is updated frequently
Specifies that in the event of an error, reflection processing is to be executed again from the system log application start point.
Specify the -z option only when an error has occurred on the server. Do not specify this option, however, if you will re-execute the pdorend command after an error has occurred at the server.
- Criteria
- If an error occurs on the primary volume that contains the original RDAREA while the pdorend command is executing, specify the -z option when you execute reflection processing from the system log application start point that is specified again in the pdorbegin command after you have restored the primary volume.
- -f reflection-processing-control-file-name
<path name> ((up to 255 characters))
Specifies the absolute path name of the file containing the control statements for reflection processing (reflection processing control file). When the -f option is omitted, the command assumes the default control statements that can be specified in the reflection processing control file.
You can specify the following control statements in the reflection processing control file:
Control statement | Description | Permitted number of statements |
---|
reflection | Specifies details of the reflection processing | 1 |
report | Specifies the file to which the results of reflection processing are to be output | 1 |
- reflection statement
- Format
reflection [sql_error={stop|skip}] [mvcelmwarn={stop|ignore}] |
- Explanation
- sql_error={stop|skip}
- Specifies the action to be taken in the event of an SQL error during reflection processing:
- stop
- Terminate the command with an error in the event of an SQL error. The processing is rolled back and the command is terminated at the previous commit point.
- skip
- Ignore specific SQL errors# and then resume reflection processing.
- If skip is specified, data integrity cannot be guaranteed between the original database obtained after updatable online reorganization and the replica database. Before specifying skip, carefully examine operations.
- Skipped SQL information is output to the file specified in the skip_info operand of the report statement. In such a case, the mapping key information is output to the skip information in order to identify the skipped SQL statements. If the mapping key is updated for the same row after the SQL statement was skipped, the mapping key information can no longer be used to identify the skipped row.
- # The following SQL errors are ignored:
- Key duplication error (SQLCODE=-803, SQLWARN6=blank)
- No corresponding row (SQLCODE=100, SQLWARN6=blank)
- Number of elements in the column added by the ADD clause of the UPDATE statement exceeds the maximum number of elements (SQLCODE=-806, SQLWARN6=blank)
- mvcelmwarn={stop|ignore}
- Specifies the action to be taken in the event a warning error is output for an UPDATE statement that is executed during reflection processing because the UPDATE statement's SET or DELETE clause specifies an element number that does not exist in the repetition column:
- stop
- Terminate reflection processing with an error. The processing is rolled back and the command is terminated at the previous commit point.
- ignore
- Update only those elements and columns in the repetition column, ignoring updating of any element that is not in the repetition column, and then resumes reflection processing.
- When ignore is specified, data integrity cannot be guaranteed between the original database obtained after updatable online reorganization and the replica database. Before specifying ignore, carefully examine operations.
- The SQL information whose updating was ignored is output to the file specified in the skip_info operand of the report statement. In such a case, the mapping key information is output to the skip information output file in order to identify the SQL statements whose updating was ignored. If the mapping key is updated for the same row after the corresponding SQL statement was executed, the mapping key information can no longer be used to identify the corresponding row.
- report statement
- Format
report [skip_info=skipped-information-output-file-name] |
- Explanation
- skip_info=skipped-information-output-file-name
<path name> ((up to 235 characters)) - When sql_error=skip or mvcelmwarn=ignore is specified in the reflection statement, specifies the absolute path name of the file to which is to be output the information about the skipped SQL statements or the SQL statements for which updating of elements that do not exist in the repetition column was ignored. The name of the actual file that is created is "specified-file-name_server-name_process-ID".#
- # server-name: Name of the server (1 to 8 characters) on which the SQL error to be skipped occurred
- process-ID: ID of the process (1 to 10 characters) on which the SQL error to be skipped occurred
- The directory to which the skipped information output file is to be output must be the same at all server machines. If there is no applicable directory, a control statement error occurs.
- When this operand is omitted, the file is created under the following name:
/tmp/pdskipsql_server-name_process-ID
- If the specified skipped information output file is not found, the command creates a skipped information output file and outputs the skipped information. When the specified skipped information output file is found, the command adds the current skipped information to that file.
- If an output error occurs in the skipped information output file, the command cancels processing, performs rollback processing, and terminates with an error.
- Output information:
- The following information is output to the skipped information output file:
YYYY/MM/DD HH:MM:SS SVR(server-name) PID(process-ID) [1]
**** SKIP SQL INFORMATION **** [2]
TABLE NAME : "authorization-identifier"."table-identifier" [3]
SQL KIND : SQL-type [4]
SQLCODE : SQL-code [5]
SQLWARN : warning-information [6]
**** AFTER KEY INFORMATION **** [7]
COLUMN NAME : "column-name" [8]
DATA LENGTH : real-data-length [9]
DATA : data-value [10]
**** BEFORE KEY INFORMATION **** [11]
COLUMN NAME : "column-name" [12]
DATA LENGTH : real-data-length [13]
DATA : data-value [14]
**** UPDATE MULTI-VALUE COLUMN INFORMATION **** [15]
COLUMN NAME : "column-name" [16]
UPDATE ELEMENT NO : element-number[,element-number]... [17]
- Explanation
- Items 1 through 14 are output for each skipped SQL statement.
- If the SQL type is INSERT or UPDATE, items 7 through 10 are output; items 8 through 10 are output for each mapping key column.
- If the SQL type is UPDATE or DELETE, items 11 through 14 are output; items 12 through 14 are output for each mapping key column.
- If ignore is specified in the mvcelmwarn option and the SQL type is UPDATE SET (element specification), UPDATE ADD, or UPDATE DELETE, items 15 through 17 are output; items 16 and 17 are output for each repetition column to be updated.
- Date and time, server name, and process ID resulting in the SQL error
- Header
- Authorization identifier and table identifier
- Type of skipped SQL statement:
INSERT: Insertion
UPDATE SET, UPDATE ADD, and UPDATE DELETE: Updating
DELETE: Deletion
- SQLCODE of the skipped SQL statement
- Warning information
The warning information that was generated is output. If there are multiple warning information items, they are separated by the space. If there is no warning information, only SQLWARN: is output.
- Header for key information after updating
- Column name
- Real data length (decimal)
- Contents of data (in dump format (hexadecimal + character string display))
- Header for key information before updating
- Column name
- Real data length (decimal)
- Contents of data (in dump format (hexadecimal + character string display))
- Header for repetition column update information
- Name of repetition column
- Element number subject to updating
The element number subject to updating is output when the SQL type is one of the following:
UPDATE SET (element specification): Element number subject to updating1
UPDATE ADD: Start element number of the element to be added2
UPDATE DELETE: Element number subject to deletion1
If multiple elements are subject to updating, the element numbers are separated by the comma. If there are more than nine element numbers, a linefeed is performed after every ninth element.
1 If an SQL statement with * specified was executed, the actual element number that was updated is output.
2 If an SQL statement with * specified was executed, * is output as the element number subject to updating.
Specifies that the referencing table with a constraint definition for the referenced table in the RDAREA that is subject to online reorganization, the referencing table in the RDAREA that is subject to online reorganization, and the check constraint table in the RDAREA that is subject to online reorganization are not to be placed in check pending status after reflection processing. This option is applicable only when the pd_check_pending operand value is YES in the system definition.
- Rules
- Specify this option when a data integrity error does not occur after online reorganization. For details about the conditions that result in an integrity error in the referential constraints and check constraints, see the manual HiRDB Version 8 Installation and Design Guide.
- When this option is omitted, the command places the generation of the original RDAREA for the tables shown below in check pending status:
- Referencing table that has a constraint definition for the referenced table in the RDAREA that is subject to online reorganization
- Referencing table in the RDAREA that is subject to online reorganization
- Check constraint table in the RDAREA that is subject to online reorganization
- If the pdorend command results in an error after placing a table in check pending status, the processing is terminated and the table remains in check pending status.
- If check pending status cannot be set, the command displays the KFPT01018-W message and continues processing, ignoring the processing to place the applicable table in check pending status. When the KFPT01018-W message is displayed, check the applicable table for an integrity error according to the administrator's action indicated in the message.
- If there is a table in check pending status after reflection processing is completed, use pdconstck to check integrity. The applicable table cannot be accessed until it is released from check pending status. For details about how to check integrity, see the manual HiRDB Version 8 Installation and Design Guide.
- If the pd_check_pending operand's value is NOUSE in the system definition, the command does not change the table's check pending status, regardless of this option. When the pd_check_pending operand's value is NOUSE, executing the pdorend command may result in a data integrity error.
- Changes in check pending status
- The following describes the changes in the check pending status.
- For referenced tables:
- Table 2-6 shows the changes in the check pending status of the referencing table related to the referenced table in the RDAREA that is subject to online reorganization. The changes shown in Table 2-6 also apply when the referenced table has multiple related referencing tables.
Table 2-6 Changes in the check pending status when there is a referenced table
pd_check_pending operand value | -p option specification | Type of referencing table | Status of RDAREA storing the referencing table related to the referenced table | Referenced table's check pending status |
---|
USE | No | Non-partitioned table | Target of online reorganization | Y |
Not the target of online reorganization | Y |
Partitioned table | All table storage RDAREAs are targets of online reorganization | Y |
Some table storage RDAREAs are targets of online reorganization | Y |
None of the table storage RDAREAs is a target of online reorganization | Y |
Yes | Not applicable | Not applicable | ![[Figure]](figure/zueng033.gif) |
NOUSE | Not applicable | Not applicable | Not applicable | ![[Figure]](figure/zueng033.gif) |
- Legend:
- Y: The table information in all RDAREAs that constitute the referencing table is placed in check pending status.
: The status does not change.
- Example:
- Table 2-7 shows the targets of check pending status when USE is specified in the pd_check_pending operand, the -p option is omitted in the pdorend command, the referenced table T0 and the referencing tables T1 through T5 have a 1-to-1 referencing relationship with the configuration shown in Figure 2-7, and RDAREAs RD1 and RD2 are subject to online reorganization.
Figure 2-7 Example of configuration of referenced table and referencing tables
![[Figure]](figure/zu020170.gif)
Table 2-7 Example of changes in the check pending status of referenced tables (when referenced and referencing tables have a 1-to-1 correspondence)
Name of referencing table | RDAREA name | Check pending status setting |
---|
T1 | RD1 | Y |
T2 | RD1 | Y |
RD2 | Y |
T3 | RD1 | Y |
RD2 | Y |
RD3 | Y |
T4 | RD3 | Y |
T5 | RD3 | Y |
RD4 | Y |
- Legend:
- Y: Placed in check pending status.
- For referencing tables:
- Table 2-8 shows the changes in the check pending status when a referencing table is in an RDAREA that is subject to online reorganization. The changes shown in Table 2-8 also apply when the referenced table related to the referencing tables is not in the RDAREA subject to online reorganization.
Table 2-8 Changes in the check pending status of referencing tables
pd_check_pending operand value | -p option specification | Type of referencing table | Status of RDAREA storing referencing table | Referencing table's check pending status |
---|
USE | No | Non-partitioned table | Target of online reorganization | Y |
Not the target of online reorganization | ![[Figure]](figure/zueng033.gif) |
Partitioned table | All table storage RDAREAs are targets of online reorganization | Y |
Some table storage RDAREAs are targets of online reorganization | P |
None of the table storage RDAREAs is a target of online reorganization | ![[Figure]](figure/zueng033.gif) |
Yes | Not applicable | Not applicable | ![[Figure]](figure/zueng033.gif) |
NOUSE | Not applicable | Not applicable | Not applicable | ![[Figure]](figure/zueng033.gif) |
- Legend:
- Y: The table information in all RDAREAs that constitute a referencing table is placed in check pending status.
- P: Only the table information in the RDAREAs that constitute a referencing table and that are subject to online reorganization is placed in check pending status.
: The status does not change.
- Example:
- Table 2-9 shows the targets of check pending status when USE is specified in the pd_check_pending operand, the -p option is omitted in the pdorend command, referencing tables T1 through T5 have the configuration shown in Figure 2-8, and RDAREAs RD1 and RD2 are subject to online reorganization.
Figure 2-8 Example of configuration of referencing tables
![[Figure]](figure/zu020180.gif)
Table 2-9 Example of changes in the check pending status of referencing tables
Name of referencing table | RDAREA name | Check pending status setting |
---|
T1 | RD1 | Y |
T2 | RD1 | Y |
RD2 | Y |
T3 | RD1 | Y |
RD2 | Y |
RD3 | ![[Figure]](figure/zueng033.gif) |
T4 | RD3 | ![[Figure]](figure/zueng033.gif) |
T5 | RD3 | ![[Figure]](figure/zueng033.gif) |
RD4 | ![[Figure]](figure/zueng033.gif) |
- Legend:
- Y: Placed in check pending status.
: Not placed in check pending status.
- For check constraint tables:
- Table 2-10 shows the changes in the check pending status when a check constraint table is in an RDAREA that is subject to online reorganization.
Table 2-10 Changes in the check pending status of a check constraint table
pd_check_pending operand value | -p option specification | Type of check constraint table | Status of RDAREA storing check constraint table | Referencing table's check pending status |
---|
USE | No | Non-partitioned table | Target of online reorganization | Y |
Not the target of online reorganization | ![[Figure]](figure/zueng033.gif) |
Partitioned table | All table storage RDAREAs are targets of online reorganization | Y |
Some table storage RDAREAs are targets of online reorganization | P |
None of the table storage RDAREAs is a target of online reorganization | ![[Figure]](figure/zueng033.gif) |
Yes | Not applicable | Not applicable | ![[Figure]](figure/zueng033.gif) |
NOUSE | Not applicable | Not applicable | Not applicable | ![[Figure]](figure/zueng033.gif) |
- Legend:
- Y: The table information in all RDAREAs that constitute the check constraint table is placed in check pending status.
- P: Only the table information in the RDAREAs that constitute the check constraint table and that are subject to online reorganization is placed in check pending status.
: The status does not change.
- Example:
- Table 2-11 shows the target of check pending status when USE is specified in the pd_check_pending operand, the -p option is omitted in the pdorend command, check constraint tables T1 through T5 have the configuration shown in Figure 2-9, and RDAREAs RD1 and RD2 are subject to online reorganization.
Figure 2-9 Example of configuration of check constraint tables
![[Figure]](figure/zu020190.gif)
Table 2-11 Example of changes in the check pending status of check constraint tables
Name of referencing table | RDAREA name | Check pending status setting |
---|
T1 | RD1 | Y |
T2 | RD1 | Y |
RD2 | Y |
T3 | RD1 | Y |
RD2 | Y |
RD3 | ![[Figure]](figure/zueng033.gif) |
T4 | RD3 | ![[Figure]](figure/zueng033.gif) |
T5 | RD3 | ![[Figure]](figure/zueng033.gif) |
RD4 | ![[Figure]](figure/zueng033.gif) |
- Legend:
- Y: Placed in check pending status.
: Not placed in check pending status.
Specifies that reflection processing for online reorganization is to be cancelled.