-
Title
What is the significance of the option “rowdata” in Compare? -
Description
What is the significance of the option “rowdata” in Compare?
-
Cause
General information
-
Resolution
When running compare or repair, it creates a file of the type .sql that contains the SQL statements that are required to be applied to the target table to bring it in sync with the source table. Starting with SharePlex 8.6.3 and up, the SQL statements contain data in the form of hash values which cannot be applied directly to the target tables in the database. Since they are generated by a hash algorithm, it is not possible to convert them to the SQL statements to be applied to the target. If wishing to generate a SQL file that is usable for applying of such SQLs, use the “rowdata” option in compare. Here is a sample syntax assuming that the source and target table names are same and source is replicating to one target only (for simplicity sake):
sp_ctrl>compare OWNER.TABLE_NAME log rowdata
The following illustrates the difference contents of SQL file with default option and “rowdata” option:
sp_ctrl (alvsupu07:5438)> compare sha86.table1 log rowdata
comparing 1 of 1 objects
compare started; job id 7
bash-3.00$ cat declt_7-1_syam_10.1.2.33_p25095_01.sql
/*
* IMPORTANT! This file is for information purposes only.
* Do not use it to repair target tables for out-of-sync rows.
* The data in this file was recorded when the compare command was run
* & may be different from the current data in the database.
* To repair out-of-sync rows, please use the repair command.
* Compare Report
*
* Job ID : 7
* Schema : SHA86
* Table : TABLE1
* Repair : Off
* Key Compare : Off
* Select Hint :
* Log File : declt_7-1_syam_10.1.2.33_p25095
* Date : Fri May 13 18:01:46 2016
*
*/
/* source rowid='AAA+7wAAHAAAKrVAAA' */
insert into "SHA86"."TABLE1" A (
"NUM_COL"
) values (
'1');
/*;;*/
/*
* Compare Results
*
* 1 source and 0 target rows compared successfully. .
* 1 out-of-sync row(s) found in this table.
* The SQL statements above are needed to bring this
* table back in sync.
*
* To bring this table back in sync, run the compare
* command again with the repair option.
* See SharePlex documentation for more details.
*
* Inserts : 1
* Updates : 0
* Deletes : 0
*
*/
Here is how the .sql file looks when the compare runs without “rowdata” option (the default way). Notice the values are only mentioned as hash.
sp_ctrl (alvsupu07:5438)> compare sha86.table1
comparing 1 of 1 objects
compare started; job id 8
bash-3.00$ cat declt_8-1_syam_10.1.2.33_p25479_01.sql
/*
* IMPORTANT! This file is for information purposes only.
* Do not use it to repair target tables for out-of-sync rows.
* The data in this file was recorded when the compare command was run
* & may be different from the current data in the database.
* To repair out-of-sync rows, please use the repair command.
* Compare Report
*
* Job ID : 8
* Schema : SHA86
* Table : TABLE1
* Repair : Off
* Key Compare : Off
* Select Hint :
* Log File : declt_8-1_syam_10.1.2.33_p25479
* Date : Fri May 13 18:57:11 2016
*
*/
/* source rowid='AAA+7wAAHAAAKrVAAA' */
insert into "SHA86"."TABLE1" A (
hash_val1,
hash_val2
) values (
'2342552567'
,'0');
/*;;*/
/*
* Compare Results
*
* 1 source and 0 target rows compared successfully. .
* 1 out-of-sync row(s) found in this table.
* The SQL statements above are needed to bring this
* table back in sync.
*
* To bring this table back in sync, run the compare
* command again with the repair option.
* See SharePlex documentation for more details.
*
* Inserts : 1
* Updates : 0
* Deletes : 0
*
*/
bash-3.00$