2.88 pdorend (Execute reflection processing for online reorganization)

Organization of this section
(1) Function
(2) Executor
(3) Format
(4) Options
(5) Rules
(6) Notes
(7) How to reference SQL trace information files

(1) Function

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:

  1. When the pdorend command is executed, it starts the pdorend 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.
  2. 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.
  3. During execution of pdorend, the command temporarily locks the replica group configuration of the corresponding RDAREAs in order to synchronize the status of original and replica RDAREAs. As a result, processing on the replica RDAREAs is placed in lock-release wait status. For details, see the description of the -t option.
  4. 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.
  5. 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.
  6. 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.

(2) Executor

HiRDB administrator

(3) Format

 pdorend [-s server-name[,server-name]...]

         {[-n maximum-retries-count] [-w maximum-wait-time-for-reflection-processing]

           [-t maximum-wait-time-for-transaction-processing]

           [-m number-of-concurrent-pdorend-application-processes] [-z]

           [-f reflection-processing-control-file-name] [-p]

           |[-u]}

(4) Options

(a) -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
  1. When the -s option is omitted, the command executes reflection processing on all servers that contain RDAREAs in online reorganization hold status.
  2. For a HiRDB single server configuration, you must omit the -s option.
  3. If a duplicate server name is specified, the command eliminates it from processing.
  4. You can specify a maximum of 128 server names. If you specify more than 128 server names, the command ignores the excess server names.
  5. 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.
  6. 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.
  7. 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.
(b) -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.

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

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

(e) -m number-of-concurrent-pdorend-application-processes ~<unsigned integer> ((1-8)) <<2>>

Specifies the number of concurrent pdorend application processes for each back-end server that applies update processing on replica RDAREAs to the original RDAREAs.

Rules
The following formula is used to determine the number of pdorend application processes to be started when the pdorend command is executed:

Number of pdorend application processes to be started = M [Figure] 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 pdorend application processes throughout the entire HiRDB that can be guaranteed.
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 pdorend application processes =
MIN(8, [Figure](NLine [Figure] PLine/100) [Figure] Ntrn [Figure] (m + n)/n [Figure] TSql[Figure])

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 pdorend 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
(f) -z

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.
(g) -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. Note that comments cannot be specified in the reflection processing control file.

You can specify the following control statements in the reflection processing control file:

Control statementDescriptionPermitted number of statements
reflectionSpecifies details of the reflection processing1
reportSpecifies the file to which the results of reflection processing are to be output1

You must specify the reflection statement together with the report statement. You must also specify the sql_trace operand in the reflection statement and the sqltrc_dir operand in the report statement.

reflection statement
Format

 reflection [sql_error={stop|skip}]

          [mvcelmwarn={stop|ignore}]

          [sql_trace={none|err|all}]

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#1 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#2 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.
#1: 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>)
#2: The mapping key is a unique key, defined in the table (columns specified in CREATE UNIQUE INDEX, CREATE TABLE PRIMARY KEY, CREATE TABLE PRIMARY CLUSTER KEY, or CREATE TABLE UNIQUE CLUSTER KEY) and whose component columns are all NOT NULL constraint, that is used to identify the rows in the original RDAREA that correspond to the updated rows in the replica RDAREA. If there is more than one corresponding unique key, the cluster key, primary key, or index with the smallest index ID is used, in this order, as the mapping key.
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.
sql_trace={none|err|all}
Specifies whether trace information for an SQL statement issued to the master database during reflection processing is to be output.
none:
Does not output trace information for an SQL statement issued to the master database during reflection processing.
err:
Outputs trace information and skip information for an SQL statement issued to the master database during reflection processing if the SQL statement results in an error.
all:
Outputs trace information and skip information for an SQL statement issued to the master database during reflection processing. When all is specified, the statement outputs this information regardless of the termination status of the executed SQL statement (normal or abnormal termination).
Rules
  1. When you specify this option, you must also specify the sqltrc_dir operand in the report statement.
report statement
Format

 report [skip_info=skipped-information-output-file-name]

   [sqltrc_dir=SQL-trace-information-file-destination-directory-name
                   ,SQL-trace-information-file-size]

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
Rules
  1. 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.
  2. When this operand is omitted, the file is created under the following name:
    skip_info optionpd_tmp_directory operand in the system definition
    SpecifiedOmitted
    TMPDIR environment variable#
    SpecifiedOmitted
    Specifieddirectory-specified-in-skip_info-operand//specified-file-name_server-name_process-ID
    Omitteddirectory-specified-in-pd_tmp_directory/pdskipsql_server-name_process-IDdirectory-specified-in-TMPDIR/pdskipsql_server-name_process-ID/tmp/pdskipsql_server-name_process-ID
    #: Environment variable set in the process server process (pdprcd) that is started from init (OS process).

  3. 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.
  4. If an output error occurs in the skipped information output file, the command cancels processing, performs rollback processing, and terminates with an error.
Formula for estimating the size of a skipped information output file:
You can use the following formula to estimate the size (in bytes) of a skipped information output file:
n
[Figure](270 + (563 [Figure] (A + B)) + C)
1
n: Number of skipped SQL statements
The following table explains the variables used in this formula:
VariableExplanation
ANumber of post-update mapping key component columns.
Add this value only when the SQL type is INSERT or UPDATE. The value to be added depends on whether the -e option is specified in the pdorbegin command (to apply only the updated columns).
If the -e option is not specified in the pdorbegin command:
Add the number of mapping key component columns.
If the -e option is specified in the pdorbegin command:
Add the number of updated mapping key columns.
BNumber of pre-update mapping key component columns. Add this value only when the SQL type is UPDATE or DELETE.
CAdd this value only when ignore was specified in the mvcelmwarn option and there are repetition columns.

     m
50 + ([Figure](51 + [Figure](D [Figure] 9)[Figure] [Figure] 81))
     1


m: Number of repetition columns per SQL statement
DNumber of updated elements. Add the following value depending on the SQL type:
UPDATE SET (element specification): Number of elements subject to update processing
UPDATE ADD: 1
UPDATE DELETE: Number of elements subject to deletion processing
Information output to the skip information output file
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.
  1. Date and time, server name, and process ID resulting in the SQL error
  2. Header
  3. Authorization identifier and table identifier
  4. Type of skipped SQL statement:
    INSERT: Insertion
    UPDATE SET, UPDATE ADD, and UPDATE DELETE: Updating
    DELETE: Deletion
  5. SQLCODE of the skipped SQL statement
  6. 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.
  7. Header for key information after updating
  8. Column name
  9. Real data length (decimal)
  10. Contents of data (in dump format (hexadecimal + character string display))#1
  11. Header for key information before updating
  12. Column name
  13. Real data length (decimal)
  14. Contents of data (in dump format (hexadecimal + character string display))#1
  15. Header for repetition column update information
  16. Name of repetition column
  17. 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 updating#2
    UPDATE ADD: Start element number of the element to be added#3
    UPDATE DELETE: Element number subject to deletion#2
    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 a character set has been defined for the corresponding column, the defined character codes are used to output the data.
#2: If an SQL statement with * specified was executed, the actual element number that was updated is output.
#3: If an SQL statement with * specified was executed, * is output as the element number subject to updating.
sqltrc_dir=SQL-trace-information-file-destination-directory-name,SQL-trace-information-file-size
Specifies the destination directory for the SQL trace information that is output by the reflection statement with err or all specified in the sql_trace operand as well as the size of the SQL trace information file.
SQL-trace-information-file-destination-directory-name ~<path name> (up to 223 characters))
Specifies the absolute path name of the destination directory for the SQL trace information. The following two files are created in the specified directory:
  • pdorsqltrc1_server-name_process-ID
  • pdorsqltrc2_server-name_process-ID
    server-name (maximum 8 characters): Server name subject to reflection processing that outputs the SQL trace information
    process-ID (maximum 10 characters): Process ID of the reflection SQL execution process that outputs the SQL trace information
Rules
  1. When you specify this operand, you must also specify the sql_trace operand in the reflection statement.
  2. Before you execute pdorend, you must create the directory specified in this operand on all the server machines that contain the server subject to reflection processing. If the directory has not been created, pdorend will result in an error.
  3. If a new SQL trace information file is created and a file with the same name already exists, that file will be overwritten. If necessary, you should first copy that file to a different directory or change its name.
  4. If swapping occurs on the files to which the SQL trace information is output, the target file will be overwritten. Therefore, if necessary, you should copy the file to a different directory or change its name before swapping occurs. When file swapping occurs, the KFPT01020-I message is output to the syslogfile and to the message log file.
  5. If an access error occurs on the SQL trace information file, pdorend cancels processing, rolls back, and then terminates with an error.
  6. When update information is dumped, an I/O operation occurs for each row of data that is output. For the number of update information output operations, see the description of variable D (update data length) in Guidelines for the size of the SQL trace information file.
SQL-trace-information-file-size ~((0, 4096-2144337920))
Specifies the size (in bytes) of the SQL trace information file. When you specify the sqltrc_dir operand, you must also specify the size of the SQL trace information file.
If you specify 0, the maximum size for the HiRDB file system is assumed. In such a case, if the amount of SQL trace information to be output exceeds the maximum file size for the HiRDB file system, pdorend will result in an error.
If you specify a value in the range from 4,096 to 2,144,337,920 and the size of the SQL trace information file exceeds the specified file size, files are swapped in order to output the SQL trace information. When the output destination is switched, the size of the source file might become larger by a maximum of 3 megabytes than the file size specified here.
Guidelines
If the specified file size is too small for the amount of SQL trace information to be output, frequent swapping of the SQL trace information files occurs, which might have adverse effects on performance. As a guideline, use the value obtained from the following formula (in the case of a table with reserved columns, the value obtained from the formula includes information for the reserved columns):
[Figure]
n: Number of tables updated in the replica database during updatable online reorganization
SQL-trace-size-per-table (bytes):
(E + A) [Figure] B + 200 [Figure] B[Figure] 100
The table below explains the variables used in the formula:
VariableDescription
ASize of SQL trace information.
For row updating:#4
400 + 100 [Figure] (1 + number of columns for mapping key) + C + D
For column updating:
400 + 100 [Figure] (number of update table component columns#1 + number of columns for mapping key) + C + D
BNumber of update-SQL statements.
The number of update-SQL statements depends on the specification in the reflection statement, as shown below:
When sql_trace=err is specified:
  • When sql_error=stop was specified or the sql_error operand was omitted, and mvcelmwarn=stop was specified or the mvcelmwarn operand was omitted:
    1
  • When the specification of sql_error and mvcelmwarn operands is other than the above:#2
    Number of update-SQL statements that might result in an SQL error during updatable online reorganization among all the update-SQL statements for the corresponding table in the replica database
When sql_trace=all is specified:
  • When sql_error=stop was specified or the sql_error operand was omitted, and mvcelmwarn=stop was specified or the mvcelmwarn operand was omitted:
    Number of update-SQL statements on the corresponding table in the replica database during updatable online reorganization + 100
  • When the specification of sql_error and mvcelmwarn is other than the above:#2
    Number of update-SQL statements on the corresponding table in the replica database during updatable online reorganization[Figure] (ratio of the number of update-SQL statements that might result in an SQL error to the total number of update-SQL statements[Figure] 100 + 1) + 100
CLength of update data.
For row updating#4:
(Length of table row[Figure] 16) [Figure] 100
For column updating:
  • When reflecting only updated columns:
    {(Sum of the lengths defined for the columns of fixed-length data type in the updated columns#1 + sum of the lengths defined for the columns of variable-length data type in the updated columns#1 + sum of the lengths defined for the compressed columns in the updated columns#5) [Figure] 16} [Figure] 100
  • When reflecting all columns including the updated columns:
    {(Sum of the lengths defined for the columns of fixed-length data type in the columns constituting the updated table#1 + sum of the lengths defined for the columns of variable-length data type in the columns constituting the updated table#1, #3 + sum of the lengths defined for the compressed columns in the columns constituting the updated columns#5) [Figure] 16} [Figure] 100
DSize of SQLCSNA information. Add the size of the SQLCSNA information only when a character set is defined for the table.
4,400 + ([Figure](number of update table component columns)#1 + number of columns for mapping key) [Figure] 8[Figure][Figure] 100)
ESize of the skipped information output file.
See the description of the skip_info operand.
#1
For a repetition column, add defined length [Figure]number of elements to be updated.
#2
This applies to the following combinations:
[Figure]sql_error=skip, mvcelmwarn=ignore
[Figure]sql_error=skip and mvcelmwarn=stop are specified or the mvcelmwarn operand is omitted
[Figure]sql_error=stop is specified or the sql_error option is omitted and mvcelmwarn=ignore is specified
#3
For the BLOB or BINARY type, add the update data length only when data is updated.
#4
If any of the following SQL statements is executed on a FIX table that does not contain any column for which a character set (other than the one for the predefined data type) is defined, rows are updated:
[Figure]INSERT statement
[Figure]UPDATE statement (when columns other than the updated columns are reflected)
#5
This value is added if all the following conditions are satisfied:
[Figure]The data type of compressed columns is BINARY.
[Figure]If reflection processing is performed on a table containing BINARY-type compressed columns, there is a compressed column whose data is expanded.
For details about compressed columns whose data is expanded during reflection processing, see Performing reflection processing on compressed tables in the HiRDB Version 9 Staticizer Option Description and User's Guide.

Information output to the SQL trace information file:
The following shows the information that is output to the SQL trace information file:

YYYY/MM/DD HH:MM:SS  SVR(server-name) PID(process-ID) [1]
**** SQL TRACE INFORMATION **** [2]
 ** EXECUTION SQL ** [3]
   UPDATE "repjobp"."ANA_0001" SET "C01"=?,"C02"=? WHERE "C01"=? [4]
 ** SQL EXECUTION RESULT ** [5]
   SQLCODE : SQL-code [6]
   SQLWARN : warning-information [7]
 ** SQLDA ** [8]
   Address  Loc      +0+1+2+3 +4+5+6+7 +8+9+a+b +c+d+e+f 0123456789​abcdef [9]
   400b3010 00000000​ 53514c44 41202020​ 00000000​ 00030003​ SQLDA   ........
 ** SQLVAR ** [10]
   Address  Loc      +0+1+2+3 +4+5+6+7 +8+9+a+b +c+d+e+f 0123456789​abcdef
   400b3020 00000000​ 00f40001 00020000​ 400c07b8 400c07b0 ........@...@...
   400b3030 00000010​ 00f40001 00020000​ 400c07c8 400c07c0 ........@...@...
   400b3040 00000020​ 00f40001 00020000​ 400c07a8 400c07a0 ........@...@...
                                   :
 ** SQLCSNA ** [11]
   Address  Loc      +0+1+2+3 +4+5+6+7 +8+9+a+b +c+d+e+f 0123456789​abcdef
   400b4040 00000000​ 53514c43 534e4120 00000000​ 00000000​ SQLCSNA ........
   400b4050 00000010​ 00050005​ 000a0001 00000000​ 00000000​ ................
                                   :
 ** UPDATE INFORMATION **
   Address  Loc      +0+1+2+3 +4+5+6+7 +8+9+a+b +c+d+e+f 0123456789​abcdef [12]
   400c0700 00000000​ 2a2a5442 4c552a2a 0002007​a 03000000​ **TBLU**...z....
   400c0710 00000010​ 00000000​ 00000000​ 00000001​ 00000044​ ...............D
   400c0720 00000020​ 00000002​ 00000058​ 4540236​c 45402383​ .......XE@#lE@#.
   400c0730 00000030​ 00000001​ 0000013​f 00000000​ 0000026​e .......?.......n
   400c0740 00000040​ 00000000​ 4d430001 00000000​ 000000a0 ....MC..........
   400c0750 00000050​ 000000a8 00000002​ 55430001​ 00000100​ ........UC......
   400c0760 00000060​ 00000000​ 00000000​ 00000000​ 00000000​ ................
   400c0770 00000070​ 000000b0 000000b8 00000002​ 55430002​ ............UC..
   400c0780 00000080​ 00000000​ 00000000​ 00000000​ 00000000​ ................
   400c0790 00000090​ 00000000​ 000000c0 000000c8 00000002​ ................
   400c07a0 000000a0 00000000​ 00000000​ 00010000​ 00000000​ ................
   400c07b0 000000b0 00000000​ 00000000​ 00030000​ 00000000​ ................
   400c07c0 000000c0 00000000​ 00000000​ 0001              ..........
YYYY/MM/DD HH:MM:SS  SVR(server-name) PID(process-ID) [13]
**** SKIP SQL INFORMATION ****
 TABLE NAME : "authorization-identifier"."table-identifier"
            :
            :
YYYY/MM/DD HH:MM:SS  SVR(server-name) PID(process-ID) [4]
**** SQL TRACE INFORMATION ****
 ** EXECUTION SQL **
   COMMIT

Explanation
  1. Date and time, server name, and process ID that executed the SQL statement
  2. Header for SQL trace information
  3. Header for the information about the executed SQL statement
  4. SQL statement
    Name of executed SQL statement or COMMIT statement.
    For an SQL statement, items [1] through [12] are displayed. For a COMMIT statement, items [1] through [4] are displayed.
    Note that the displayed SQL statement might differ from the SQL statement that updated the replica database, for example in the following cases:
    [Figure]When reflection processing is performed, the operand for reflecting only the updated columns was not specified.
    [Figure]The number of updated columns + number of columns for mapping key exceeds 30,000 (maximum number of ? parameters).
    [Figure]A rollback SQL statement was executed.
    [Figure]Rows were updated (see notes in Guidelines for the size of the SQL trace information file).
  5. Header for the result of the executed SQL statement
  6. SQLCODE of the executed SQL statement
  7. Warning information for the executed SQL statement (SQLWARNn (n: 0 to F))
    Information about the issued warning is displayed. If multiple warnings were issued, their information items are delimited by the space. If there were no warnings, only SQLWARN: is displayed.
  8. Information about the SQL descriptor area (SQLDA)
    For details about the SQL descriptor area, see the HiRDB Version 9 UAP Development Guide.
  9. Dump output header
    The table below shows the headers and the information that is output:
    HeaderOutput information
    AddressAddress (hexadecimal)#1
    LocOffset from the beginning (hexadecimal)#1
    +0+1+2+3 +4+5+6+7 +8+9+a+b +c+d+e+fContents of memory (hexadecimal)#2
    0123456789​abcdefContents of memory (in characters)#2
    #1
    The address is output in 4 bytes (8 digits) in the 32-bit mode HiRDB and in 8 bytes (16 digits) in the 64-bit mode HiRDB.
    #2
    If a character set has been defined for the corresponding column, the contents are displayed in the defined character encoding.

  10. Information about the SQL descriptor area (SQLDA)
    For details about the SQL descriptor area, see the manual HiRDB Version 9 UAP Development Guide.
  11. Character set descriptor area (SQLCSNA)
    This information is displayed only when there is a table column for which a character set has been defined.
    For details about the character set descriptor area, see the HiRDB Version 9 UAP Development Guide.
  12. Update information
    Update information when the SQL statement was executed. For the format of the update data for each data type, see the HiRDB Version 9 UAP Development Guide.
    If a column of a data type shown below has the compression specification (COMPRESSED), compressed or expanded data is output as update information. The data that is output depends on whether there is a column whose compressed data size is greater than the defined length.
    Data type with compression specificationColumn whose compressed data size is greater than the defined length
    NoneExists
    BINARY typeCompressed dataExpanded data
    XML typeCompressed dataCompressed data
  13. Skip information
    This information is displayed together with the SQL trace information. For details about the information that is displayed, see Information output to the skip information output file.
(h) -p

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
  1. 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 9 Installation and Design Guide.
  2. 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
  3. 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.
  4. 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.
  5. 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 9 Installation and Design Guide.
  6. 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:
The table below 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-14 also apply when the referenced table has multiple related referencing tables.

Table 2-17 Changes in the check pending status when there is a referenced table

pd_check_pending operand value-p option specificationType of referencing tableStatus of RDAREA storing the referencing table related to the referenced tableReferenced table's check pending status
USENoNon-partitioned tableTarget of online reorganizationY
Not the target of online reorganizationY
Partitioned tableAll table storage RDAREAs are targets of online reorganizationY
Some table storage RDAREAs are targets of online reorganizationY
None of the table storage RDAREAs is a target of online reorganizationY
YesNot applicableNot applicable--
NOUSENot applicableNot applicableNot applicable--
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-18 Example of changes in the check pending status of referenced tables (when referenced and referencing tables have a 1-to-1 correspondence) 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 below, and RDAREAs RD1 and RD2 are subject to online reorganization.

Figure 2-9 Example of configuration of referenced table and referencing tables

[Figure]

Table 2-18 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 tableRDAREA nameCheck pending status setting
T1RD1Y
T2RD1Y
RD2Y
T3RD1Y
RD2Y
RD3Y
T4RD3Y
T5RD3Y
RD4Y
Legend:
Y: Placed in check pending status.
For referencing tables:
The table below 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 this table also apply when the referenced table related to the referencing tables is not in the RDAREA subject to online reorganization.

Table 2-19 Changes in the check pending status of referencing tables

pd_check_pending operand value-p option specificationType of referencing tableStatus of RDAREA storing referencing tableReferencing table's check pending status
USENoNon-partitioned tableTarget of online reorganizationY
Not the target of online reorganization--
Partitioned tableAll table storage RDAREAs are targets of online reorganizationY
Some table storage RDAREAs are targets of online reorganizationP
None of the table storage RDAREAs is a target of online reorganization--
YesNot applicableNot applicable--
NOUSENot applicableNot applicableNot applicable--
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-20 Example of changes in the check pending status of referencing tables 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 below, and RDAREAs RD1 and RD2 are subject to online reorganization.

Figure 2-10 Example of configuration of referencing tables

[Figure]

Table 2-20 Example of changes in the check pending status of referencing tables

Name of referencing tableRDAREA nameCheck pending status setting
T1RD1Y
T2RD1Y
RD2Y
T3RD1Y
RD2Y
RD3--
T4RD3--
T5RD3--
RD4--
Legend:
Y: Placed in check pending status.
--: Not placed in check pending status.
For check constraint tables:
The table below 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-21 Changes in the check pending status of a check constraint table

pd_check_pending operand value-p option specificationType of check constraint tableStatus of RDAREA storing check constraint tableReferencing table's check pending status
USENoNon-partitioned tableTarget of online reorganizationY
Not the target of online reorganization--
Partitioned tableAll table storage RDAREAs are targets of online reorganizationY
Some table storage RDAREAs are targets of online reorganizationP
None of the table storage RDAREAs is a target of online reorganization--
YesNot applicableNot applicable--
NOUSENot applicableNot applicableNot applicable--
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-22 Example of changes in the check pending status of check constraint tables 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 below, and RDAREAs RD1 and RD2 are subject to online reorganization.

Figure 2-11 Example of configuration of check constraint tables

[Figure]

Table 2-22 Example of changes in the check pending status of check constraint tables

Name of referencing tableRDAREA nameCheck pending status setting
T1RD1Y
T2RD1Y
RD2Y
T3RD1Y
RD2Y
RD3--
T4RD3--
T5RD3--
RD4--
Legend:
Y: Placed in check pending status.
--: Not placed in check pending status.
(i) -u

Specifies that reflection processing for online reorganization is to be cancelled.

Rules
  1. Note that when the -u option is specified, the command assumes error handling.
  2. If the pdorend command is unable to apply the necessary lock, it waits until the value of the pd_lck_wait_timeout operand in the system definition is reached.
  3. When the processing target includes a shared RDAREA, processing is performed at all back-end servers in the batch mode. Therefore, an error occurs if the command is unable to release all applicable RDAREAs from online reorganization hold status. If online reorganization hold cannot be released for any of the servers, for a reason such as a specific back-end server is inactive, you can specify the -s option to release online reorganization hold.
Criteria
Specify the -u option if an error has occurred on the applicable server. If you specify this option at any other time, reflection processing is disabled and transaction accesses may not be switched to the original RDAREA. If this happens, the system resumes online applications using the replica RDAREA on the secondary volume. To have the online applications access the original RDAREA, you must terminate HiRDB once.

(5) Rules

  1. The pdorend command can be executed only while HiRDB is active.
  2. The pdorend command must be executed at the server machine that contains the single server or where the system manager is located.
  3. To execute the pdorend command, the data dictionary RDAREA must be in the following status:
    • Open and shutdown release status
  4. Whether or not the pdorend command can be executed depends on the RDAREA status. For details, see C.1 RDAREA status transitions.
  5. The figure below shows the relationship between the maximum wait time for reflection processing and transaction processing.

    Figure 2-12 Relationship between the maximum wait time for reflection processing and transaction processing

    [Figure]

    Explanation
    At the end of reflection processing, the command synchronizes the original and replica RDAREAs. If this synchronization processing results in a timeout, the command retries as many time as specified in the -n option.
    The values of the -n, -w, and -o options are monitored for each server.
    When the KFPH27040-I message has been displayed, both reflection processing and current RDAREA switchover processing have been completed for the corresponding server.
    If the remaining value of the -w option is less than the value of the -t option, the former is replaced with the latter.
  6. When the processing target includes a shared RDAREA, all back-end servers are processed. Therefore, if either of the following is true, an error occurs:
    • Reflection processing cannot be completed from replica RDAREAs to original RDAREAs at the updatable back-end server that is subject to processing.
    • The current RDAREAs cannot be changed from replica RDAREAs to original RDAREAs at all back-end servers.
  7. When the pdorend command is executed, deadlock may occur for a referencing or updating transaction. By specifying pd_deadlock_priority_use=Y in the system definition and a deadlock priority value for the operation command in the pd_command_deadlock_priority operand, you can specify whether the referencing or updating transaction or the operation command is to take control in the event of deadlock. To make the pdorend command result in an error, specify a larger deadlock priority value for the operation command. In this case, re-execute the pdorend command after a specific period of time, because the pdorend command results in an error after releasing all the locks that were placed by the command. To give precedence to the pdorend command and make the referencing or updating transaction result in an error, specify a smaller deadlock priority value for the operation command.

(6) Notes

  1. The result of pdorend command can be checked with the pdls -d org command or on the basis of the return code. The return codes are as follows:
    0: Normal termination
    4: Warning termination#1
    8: Abnormal termination
    12: Abnormal termination (an event occurred that prevented the command from displaying an error message)#2
    #1: One of the following events occurred:
    - The corresponding SQL statement was skipped because processing was successful at some of the servers or an SQL error to be skipped occurred.
    - An UPDATE statement with the SET or DELETE clause specified and containing an element number that does not exist in the repetition column was executed and a warning error occurred, but processing continued. For details about the action to be taken in the event of a warning error, see the mvcelmwarn operand of the reflection statement.
    - The status of some tables could not be changed to check pending status, but the corresponding processing was ignored and the next processing was executed.
    #2: Check the error message in the syslogfile at the host where the single server or dictionary server is located, eliminate the cause of the error, and then re-execute the command. If no error message has been output to the syslogfile, contact the customer engineer.
  2. To synchronize the original and replica RDAREAs, the pdorend command locks the inner replica and replica group configurations for the RDAREAs in online reorganization hold status. If another transaction is accessing an RDAREA in the replica group, the pdorend command waits until that transaction terminates. After reflection processing is completed and the current RDAREA has switched over to the original RDAREA, the inner replica and replica group configurations are unlocked and become accessible again to transactions.
  3. If the pdorend command results in a timeout, increase the values of the -w, -t, and -n options. If the pdorend command still results in timeouts, reduce the number of transactions that access the replica RDAREAs, and then re-execute the command.
  4. In the event of an error during execution of the pdorend -u command, there is no need to re-execute the pdorend command if all RDAREAs at the applicable server have been released from online reorganization hold status. To check the status of RDAREAs at a particular server, use the pddbls command.
  5. If a referenced table is in an RDAREA subject to online reorganization, and an RDAREA containing a referencing table related to that referenced table is not subject to online reorganization, the related referencing table is also placed in check pending status at the extension of the pdorend command. Deadlock may result if another program, such as a UAP, is accessing the referencing table that is to be placed in check pending status. Therefore, if a referenced table is in an RDAREA that is subject to online reorganization, also make the RDAREAs containing the referencing tables related to that referenced table subject to online reorganization. You can use the pdrdrefls -c command to check the RDAREAs that contain the referencing tables related to the referenced table. For details about how to determine whether or not a referenced table has related referencing tables, see the manual HiRDB Version 9 Installation and Design Guide.
  6. If you select utf-8 or utf-8_ivs as the character encoding in the pdsetup command, you can use a file with a BOM as the reflection processing control file for pdorend. Note that even when a file with a BOM is used as the reflection processing control file for pdorend, the BOM is skipped. No BOM is included in the file that is output by pdorend.

(7) How to reference SQL trace information files

This subsection explains how to reference SQL trace information files by using the example table definition and SQL statement shown below with a 32-bit mode HiRDB.

Table definition

CREATE TABLE ANA_0001 (
   C01        SMALLINT NOT NULL,
   C02        SMALLINT NOT NULL);

Executed SQL statement

UPDATE ANA_0001 SET C01=3,C02=1 WHERE C01=1;

The figure below shows the SQL trace information file that was output.

Figure 2-13 SQL trace information file that was output (example)

[Figure]

Explanation
  1. Check the number of ? parameters in the SQL descriptor area (SQLDA).
    In this example, 0x0003 indicates that there are 3 ? parameters.
  2. Check the data code.
    The format of the update data in the update information varies according to the type of the data code. In this example, 0xf4 indicates the SMALLINT type.
  3. Check the structure of the variable area.
    In this example, 0x0001 indicates the simple structure.
  4. Check the indicator's address in the update information.
    The following shows the value of the indicator in the 32-bit mode:
    NULL: 0xffff (-1)
    Other than NULL: 0x0000 (0)
    In this example, 0x400c07b0 is the indicator's address. The size of a SMALLINT-type indicator is 2 bytes. In this example, 0x0000 indicates that the address is not the null value. The size of the indicator depends on the data code. For details, see SQL data types and C data descriptions in the HiRDB Version 9 UAP Development Guide.
  5. If the indicator's address is not NULL in 4, check the length of the updated data.
    The storage location for the data length depends on the data type. The following shows the location of the data length for each data type:
    Data typeLocation of data length
    Variable-length character string
    Variable-length national character string
    Variable-length mixed character string
    BINARY
    BLOB
    SGMLTEXT
    XML
    FREEWORD
    Item 6 in Figure 2-13 SQL trace information file that was output (example)
    OtherItem 5 in Figure 2-13 SQL trace information file that was output (example)
    For details, see Data codes and data lengths set in the SQL Descriptor Area and SQL data types and C data descriptions in the HiRDB Version 9 UAP Development Guide.
    In this example, the data length is stored in 5 because it is fixed-length data (SMALLINT). The data length is 0x0002, which indicates that the length of update data in the update information is 2 bytes.
  6. Check the address of the data in the update information.
    In this example, the data is stored at the location beginning at 0x400c07b8. Because the length of update data in the update information is 2 bytes, the update data corresponding to the first ? parameter in the SQL statement output to the SQL trace information is 0x0003, which is 2 bytes of data from the data address.
    The data format depends on the data type. For details, see Data codes and data lengths set in the SQL Descriptor Area and SQL data types and C data descriptions in the HiRDB Version 9 UAP Development Guide.
  7. If hybrid is selected as the processing method for Real Time SAN Replication, executing the pdorend command results in a database synchronization wait for the remote site. This may result in an overhead of 2 seconds or more for each RDAREA specified in the -s option. If the database synchronization wait for the remote site fails, you must recover the remote site's database. For details about the error handling when Real Time SAN Replication is used, see the manual HiRDB Version 9 Disaster Recovery System Configuration and Operation Guide.