This subsection describes how to use the update-SQL output facility.

(a) Prerequisites

To use the update-SQL output facility, the following conditions must be satisfied:

• The source Datareplicator's processes can be started (recovered from errors such as CPU errors).
• The following files in the source system are in normal status (recovered from errors such as disk errors):
  
  • Extraction system definition file
  • Extraction environment definition file
  • Transmission environment definition file
  • Extraction definition file
  • Extraction definition preprocess file
  • Extraction information queue file
  • Extraction master status file
  • Extraction server status file
  • Data linkage file
  • Duplexing control file
  • HiRDB system log

(b) Command

To execute the update-SQL output facility, execute the hdestart command with the -s -L options specified. For details about the command, see the hdestart command.

(c) Process to be used

When you use the update-SQL output facility, the update-SQL output process starts. The following describes the details of the update-SQL output process:

The actions of the update-SQL output process are the same as of the transmission process except for the following:

• The update-SQL output process does not establish connection with or send data to the target Datareplicator.
• The update-SQL output process does not update the extraction server status file.
• The update-SQL output process does not terminate, even if HiRDB normal termination or linkage stop log information (pdrplstop) is detected.
• The update-SQL output process does not terminate even when hdeevent -n 0 is executed.
• The update-SQL output process is run with the nodemst method, even when sendmst is specified in the sendcontrol operand in the extraction system definition.
• When a target system is in the reduced mode, the reduced-mode operation continues even if false is specified in the overwrite_continue operand in the transmission environment definition.

This subsection describes the update-SQL file that is output by the update-SQL output facility.

(a) Output target

The update-SQL file is output to the following destination:

If any existing file has this name, the information is added to that file.

If there is no untransmitted information in the extraction information queue file, no update-SQL file is created.

(b) Output format

One row of untransmitted information is output to the update-SQL file as one SQL statement. The data output as SQL statements includes only the transactions that were committed; no data is output for any transaction that was rolled back. The output transactions are sorted in the order they were committed. Update information for which processing is underway is not output.

The following shows the format of the data that is output:

INSERT statement

/* update-date */ INSERT INTO authorization-identifier.table-name(column-name,column-name,...) VALUES(value#1,value,...);

UPDATE statement^#2

/* update-date */ UPDATE authorization-identifier.table-name SET3 column-name=value#1,column-name=value, ... WHERE#4 column-name=value AND column-name=value ...;

DELETE statement

/* update-date */ DELETE FROM authorization-identifier.table-name WHERE#4 column-name=value#1 AND column-name=value ...;

PURGE TABLE statement^#5

/* update-date */ PURGE TABLE authorization-identifier.table-name;

Event^#6

/* ****-**-** **:**:** */ /* HDEEVENT event-code */

End of transaction

/* ****-**-** **:**:** */ COMMIT;

#1

The table below shows the format and contents of the column values that are output. For repetition columns, *MCOL* is output as the column value regardless of whether the value is the null value or a non-null value.

Column attribute	Character set	-H option in the hdestart command	Output format	Output value	Remarks
CHAR or VARCHAR	Omitted	Omitted	Character format (Example: 'aaa')	Value in the corresponding column	A control code is output as a period.
		Specified	Hexadecimal format (Example: X'C3C3C3')		--
	Specified	Omitted	Character format (Example: 'aaa')	Value obtained by converting the character codes of the corresponding column's value to the database locale	A control code is output as a period.
		Specified	Hexadecimal format (Example: X'C3C3C3')		--
MCHAR or MVARCHAR	--	Omitted	Character format (Example: M'')	Value in the corresponding column	A control code is output as a period.
		Specified	Hexadecimal format (Example: X'81C141')		--
NCHAR or NVARCHAR	--	Omitted	Character format (Example: N'')		A control code is output as a period.
		Specified	Hexadecimal format (Example: X'81C181C2')		--
INTEGER or SMALLINT	--	--	Decimal format (Example: 100)		--
DECIMAL	--	--	Decimal format (Example: 100)		If an error occurs while data in packed format is being converted to a character string, that column is output in hexadecimal format.
FLOAT or SMALLFLT	--	--	Floating-point number format (Example: 1.0...0E+02)		--
DATE	--	--	Character format (Example: '2008-04-15')		If an error occurs while data in packed format is being converted to a character string, that column is output in hexadecimal format.
TIME	--	--	Character format (Example: '16:15:30')
TIMESTAMP	--	--	Character format (Example: '2008-04-15 16:15:30')
INTERVAL YEAR TO DAY	--	--	Decimal format (Example: +12340102.)
INTERVAL HOUR TO SECOND	--	--	Decimal format (Example: +112233.)
BLOB	--	--	Fixed character string	"*BLOB*"	The corresponding fixed character string ("*BLOB*" or "*BINARY*") is output also for SUBSTR operation. The corresponding fixed character string ("*BLOB*" or "*BINARY*") is output regardless of whether the value is the null or a non-null value.
BINARY	--	--		"*BINARY*"
ADT	--	--	Fixed character string	"*ADT*"	The corresponding fixed character string "*ADT*" is output regardless of whether the value is the null value or a non-null value.

Legend:

--: Not applicable

#2

When update information for repetition columns is output, the following limitations apply:

• UPDATE ADD, UPDATE SET, and UPDATE DELETE are all output as UPDATE SET.
• Even for update processing with element specification, neither subscript for column name nor asterisk (*) is output.

#3

If variable-length data with a defined length of 256 bytes or more has not been updated, that column is not output to the SET clause.

#4

The mapping key column is output to the WHERE clause. If the column value is NULL, the output format is column-name IS NULL.

#5

If update information for PURGE TABLE is detected for a partitioned table spanning multiple servers, the operation depends on whether the prg_eventno operand is specified in the transmission environment definition. The following table describes the operation depending on the specification value:

Operand specification	Operation
Specified	The facility outputs the event with the number specified in the prg_eventno operand, and then resumes processing.
Omitted	The facility ignores the corresponding PURGE TABLE.

#6

Although event code 0 is not sent to the target Datareplicator, it is output to this file.

If multiple events are issued within a single transaction, only the last event that was issued is output. If the event was issued by the hdeevent command, COMMIT is output immediately after output of the event.

(c) Output example

The following shows an output example of an update-SQL file:

/* 2004-12-29 19:18:00 */ INSERT INTO "USR1"."T1"("C1","C2") VALUES(1,'a');
/* 2004-12-29 19:18:30 */ UPDATE "USR1"."T1" SET "C1"=2,"C2"='b' WHERE "C1"=2;
/* 2004-12-29 19:19:00 */ DELETE FROM "USR1"."T1" WHERE "C1" IS NULL;
/* 2004-12-29 19:19:30 */ PURGE TABLE "USR1"."T1";
/* ****-**-** **:**:** */ /* HDEEVENT 200 */
/* ****-**-** **:**:** */ COMMIT;

To execute update-SQL output:

1. Terminate the source Datareplicator, if it is active.
   This prevents connection from being established with the target system if an invalid option is specified in step 3 or 6.
   

   hdsstop -t immediate

2. If true was specified in the overwrite operand in the transmission environment definition, terminate the source Datareplicator and change the overwrite operand to false.
   This prevents the operation from being switched to reduced mode if the extraction information queue file becomes full in step 3.
   
3. Start the source Datareplicator's extraction processing.
   

   hdestart -e

4. Use the following method to check that all update information has been extracted from the system log information:
   
   • Make sure that the source Datareplicator's error information file does not contain any KFRB00042-E messages (extraction information queue file is full).
     If a KFRB00042-E message has been issued, add an extraction information queue file, and then restart extraction processing.^#
     

     hdemodq

   #

   There can be a maximum of 16 extraction information queue files. If the extraction information queue file becomes full while there are already 16 extraction information queue files, some update information has not been output.

   • Check the extraction status in the HiRDB system log information to make sure that extraction of all required update information has been completed.
     Compare System Log Extract Point in the execution results of the pdlogls and pdls -d rpl -j commands to make sure that the latter has passed the former. The following shows an example:
     Output example of pdlogls
     

     $ pdlogls -d sys -s flora370 HOSTNAME : flora370(151739) Group Type Server Gen No. Status Run ID Block No. log10 sys sds01 1 oc-d--u 3e6835a9 1 40 log11 sys sds01 0 os----- 00000000 0 0

     Output example of pdls
     

     $ pdls -d rpl -j -s flora370 SYSTEMID : HRD1(150621) Data replication : Y UNITID : unt1(150621) Data replication : Y SERVER NAME : sds01 Extract Database : Y Extract Status : C System Log Extract Point : Run ID Group Gen No. BLock No. 3e6835a9 log10 1 41 System Log Sync Info : Run ID Group Gen No. BLock No. 3e6835a9 log10 1 13

     According to the result of pdlogls, the last block number is block 40 in log10, while it is block 41 in log10 with pdls. This indicate that extraction of all the update information has been completed.
     
5. Terminate the source Datareplicator.
   

   hdestop

6. Delete all update-SQL files, and then start update-SQL output processing on the source Datareplicator.
   

   hdestart -s -L

7. Execute the hdestate command to verify that the values of Queue write position and Queue current pos are the same.
   For details about Queue current pos, see the hdestate command.
   
8. Terminate the source Datareplicator.
   

   hdestop

9. Open the update-SQL file with a program such as a text editor and check the untransmitted data.
   If you need to send untransmitted data to the target, start transmission processing. If there is no need to send data, initialize or partially initialize the source Datareplicator in order to delete the untransmitted data stored in the extraction information queue file.
   

In the messages displayed by Datareplicator, the transmission and output processes are both indicated as Sender process. To determine whether a message was intended for the transmission process or the output process, check the message output process name that is displayed in the message.

• If system switchover occurs during execution of the update-SQL output facility while server is specified in the nodecontrol operand in the extraction system definition, Datareplicator performs the processing described in the following table.
  

  Table 9-16 Datareplicator processing when system switchover occurs during execution of the update-SQL output facility

  System switchover status	Datareplicator processing
  System switchover occurs at the system where MST is located	After system switchover is completed, MST is started, but it rejects a connection request from NMT that is running in the update-SQL output status. As a result, all NMTs and MSTs are terminated.
  System switchover occurs at the system where NMT is located	After system switchover is completed, NMT is started,# but it rejects a connection request from MST that is running in the update-SQL output status. As a result, the NMT where system switchover occurred is terminated.

  Legend:
  MST: Extraction master process
  NMT: Extraction node master process
  #
  Because NMT is started by the hdestart_n command, both extraction and transmission processes are started.