cdr check replicateset

The cdr check replicateset command compares the data on replication servers to create a report listing data inconsistencies. Optionally you can use the command to repair the inconsistent data within a replicate.

Syntax


1  cdr check replicateset?  %Connect Option  (1)
1  --master=data_server(2)
1 --nomaster
2  --replset=repl_set(3)
1 +  target_server
1  --all
3?  --name=task_name
3?   --verbose
3?   --firetrigger= 
4.1! off
4.1 on
4.1 follow
3?  --inprogress=recheck_time
3?   --background
3?   --skipLOB
3?  --since=start_time
3?  --process=number_processes
3?   --excludeTimeSeries
3?   --ignoreHiddenTSElements
3?  --checksum=checksum_function
3?  %Repair Options
Repair Options

1   --repair
1?   --extratargetrows= 
2.1! delete
2.1 keep
2.1 merge
1   --timestamp?  --deletewins
2?   --enable?  --timeout = seconds
2?   --allrepl
Notes:
  • 1 See Connect Option.
  • 2 Omit if you include the --timestamp option.
  • 3 Omit if you include the --allrepl option.
Element Purpose Restrictions Syntax
checksum_function Name of the checksum function to use during consistency checking. The function must be installed and registered on all replication servers. Long Identifiers
data_server Name of the database server to use as the reference copy of the data. Must be the name of an existing database server group in SQLHOSTS. Long Identifiers
number_processes The number of parallel processes to use for the command. The maximum number of processes Enterprise Replication can use is equal to the number of replicates in the replicate set.
recheck_time The number of seconds to spend rechecking transactions that might be listed as inconsistent because they are not yet applied on the target server. Must be a positive integer.
repl_set Name of the replicate set. Can be the name of a derived replicate set. Long Identifiers
seconds The number of seconds to wait for a disabled replication server to be recognized as active by other replication servers in the domain and how long to wait for control messages queued at peer servers to be applied at newly-enabled server. Must be an integer value from 0 to 60.
start_time The time from which to check updated rows. Can have one the following formats:
  • numberM = Include rows updated in the last specified number of minutes.
  • numberH = Include rows updated in the last specified number of hours.
  • numberD = Include rows updated in the last specified number of days.
  • numberW = Include rows updated in the last specified number of weeks.
  • "YYYY-MM-DD hh:mm:ss" = Include rows updated since this time stamp.
 The time stamp format follows the convention of the DBTIME environment variable.
target_server Name of a database server group to check. Must be the name of an existing database server group in SQLHOSTS. Long Identifiers
task_name The name of the progress report task. If you use an existing task name, the information for that task is overwritten.

Maximum name length is 127 bytes.

 Long Identifiers

The following table describes the cdr check replicateset options.

Long Form Short Form Meaning
--all -a Specifies that all servers defined for the replicate are checked.
--allrepl -A Specifies that all replicates, whether they are in a replicate set or not, are repaired.

You cannot use the --replset option with the --allrepl option.
--background -B Specifies that the operation is run in the background as an SQL administration API command.

The command and its result are stored in the command_history table in the sysadmin database, under the name that is specified by the --name= option, or the time stamp for the command if --name= is not specified.
--checksum= Specifies the name of an existing checksum function to use during consistency checking. By default, the checksum function that is provided with the database server is run.
--enable -E Enables replication on the target server if it was disabled by the cdr disable server command.
--deletewins -d Specifies that the replicate uses the delete wins conflict resolution rule.

You cannot use this option for replicates that include TimeSeries columns.
--excludeTimeSeries Specifies to prevent the checking of time series data.
--extratargetrows= -e Specifies how to handle rows that are found on the target servers that are not present on the server from which the data is being copied (data_server):
  • delete: (default) remove rows and dependent rows, based on referential integrity constraints, from the target servers
  • keep: retain rows on the target servers
  • merge: retain rows on the target servers and replicate them to the data source server. You cannot use this option for replicates that include TimeSeries columns.
--firetrigger= -T Specifies how to handle triggers at the target servers while data is synchronizing:
  • off: (default) do not fire triggers at target servers during synchronization
  • on: always fire triggers at the target servers even if the replicate definition does not have the --firetrigger option
  • follow: fire triggers at target servers only if the replicate definition has the --firetrigger option
--ignoreHiddenTSElements Specifies to avoid checking time series elements that are marked as hidden.
--inprogress= -i Specifies to spend more than the default time to recheck inconsistent rows that might be in the process of being applied on target servers. If the --inprogress= option is not set, inconsistent rows are rechecked for up to five seconds.
--master= -m Specifies the database server to use as the reference copy of the data.

You cannot use the --master option with the --timestamp option.
--name= -n Specifies that the progress of this command can be monitored. Information about the operation is stored under the specified progress report task name on the server on which the command was run.
--nomaster -N Specifies that the replicate is configured as a data consolidation system in which the multiple primary servers only send data and the single target server only receives data.
--process= -p Specifies to run the command in parallel, using the specified number of processes. At most, Enterprise Replication can use one process for each replicate in the replicate set. If you specify more processes than replicates, the extra processes are not used.

Not all replicates can be processed in parallel. For example, if replicates have referential integrity rules, the replicates with the parent tables must be processed before the replicates with the child tables.
--repair -R Specifies that rows that are found to be inconsistent are repaired.
--replset -s Specifies the name of the replicate set to check.

You cannot use the --replset option with the --allrepl option.
--skipLOB -L Specifies that large objects are not checked.
--since= -S Specifies the time from which to check updated rows. The replicate must be using the time stamp or delete wins conflict resolution rule.

You cannot use this option for replicates that include TimeSeries columns.
--timeout= -w Specifies the time to wait for a disabled server to be enabled.
--timestamp -t Specifies to repair inconsistent rows based on the latest time stamp among all the participants. The replicate must use the time stamp or delete wins conflict resolution rule.

You cannot use the --master option with the --timestamp option.

You cannot use this option for replicates that include TimeSeries columns.
--verbose -v Specifies that the consistency report shows specific values that are inconsistent instead of a summary of inconsistent rows.

Usage

Use the cdr check replicateset command to check the consistency of data between multiple database servers for a replicate set. The cdr check replicateset command compares all rows on all specified database servers against the data in the reference server and produces a consistency report. If you include the --verbose option, the report lists every inconsistent value; otherwise, the report summarizes inconsistent rows.

If you run this command as a DBSA instead of as user onedb, you must have INSERT, UPDATE, and DELETE permission on the replicated tables on all the replication servers in the domain.

If you want to monitor the progress of the check operation, include the --name option and specify a name for the progress report task. Then, run the cdr stats check command and specify the progress report task name.

Depending on the state of the data in your database when you run the cdr check command, the system might also run an UPDATE STATISTICS command.

If replicated transactions are active when the cdr check replicateset command is running, the consistency report might include rows that are temporarily inconsistent until those transactions are applied at the target server. By default, the cdr check replicateset command rechecks inconsistent rows for up to five seconds after the initial check is completed. If you find your transaction latency is longer than five seconds, you can extend the recheck time period by using the --inprogress option to specify a longer interval. After the initial recheck, inconsistent transactions are rechecked until there are no inconsistent transactions or the number of seconds specified by the --inprogress option elapses. In general, set the recheck time to a little longer than your average transaction latency because if repairing inconsistencies causes spooling in the send queue, transaction latency might increase during a repair. View your transaction latency with the cdr view apply command.

You can improve the performance of consistency checks by limiting the amount of data that is checked by using one or more of the following options:

  • Skip the checking of large objects with the --skipLOB option. If you find that your large objects do not change as much as other types of data, then skipping them can make a consistency check quicker.
  • Check from a specific time with the --since option. If the replicate uses the time stamp or delete wins conflict resolution rule and you regularly check consistency, you can limit the data that is checked to the data that was updated since the last consistency check.

You can significantly improve the performance of checking a replicate set by checking the member replicates in parallel. You specify the number of parallel processes with the --process option. For best performance, specify the same number of processes as the number of replicates in the replicate set. However, replicates with referential integrity constraints cannot be processed in parallel.

You can run a consistency check as a background operation as an SQL administration API command if you include the --background option. This option is useful if you want to schedule regular consistency checks with the Scheduler. If you run a consistency check in the background, provide a name for the progress report task by using the --name option so that you can monitor the check with the cdr stats check command. You can also view the command and its results in the command_history table in the sysadmin database. If you use the --background option as a DBSA, you must have CONNECT privilege on the sysadmin database and INSERT privilege on the ph_task table.

If you have large tables, you can speed consistency checking by indexing the ifx_replcheck shadow column.

If your replication system is configured for data consolidation and the primary servers include the S option in their participant definitions, you must include the --nomaster option.

The cdr check replicateset command repairs inconsistent rows so that they match the rows on the reference server. During a repair of inconsistent rows, the cdr check replicateset command uses direct synchronization as a foreground process when repairing inconsistent rows. The cdr check replicateset command with the --repair option performs the following tasks:

  1. Determines the order in which to repair tables if they have referential relationships.
  2. Creates a shadow replicate with the source server and target server as participants. The conflict resolution rule for the shadow replicate is always apply.
  3. Performs an index scan on the replication key index at both the source server and the target server to create a checksum and identify inconsistent rows.
  4. Replicates inconsistent rows from the source server to the target server by doing a dummy update of the source server, which might result in increased logging activity. Rows are not replicated to participants that include the S option in the participant definition because those participants only send data.
  5. Runs a check to determine whether any rows remain inconsistent. Rows can be temporarily inconsistent if not all transactions are complete on the target server.
  6. If any rows are inconsistent, reruns the check for up to five seconds, or for up to the number of seconds specified by the --inprogress option.
  7. Deletes the shadow replicate.
  8. Repeats steps 2 through 7 for each replicate in the replicate set.
  9. Displays the consistency report.

If you have disabled a server with the cdr disable server command, you can enable it and synchronize it by using the --enable option with the --repair option. You can optionally specify a timeout period with the --timeout option.

To repair all replicates, use the --allrepl option with the --repair option.

To repair replicate sets based on the latest time stamps among the participants instead of based on a master server, use the --repair option with the --timestamp option. If your replicates use the delete wins conflict resolution rule, also include the --deletewins option. A time stamp repair evaluates extra and mismatched rows according to the rules of the time stamp or delete wins conflict resolution rules.

You can run this command from within an SQL statement by using the SQL administration API.

Return codes

A return code of 0 indicates that the command was successful.

If the command is not successful, one of the following error codes is returned: 1, 5, 11, 17, 18, 31, 37, 48, 53, 54, 61, 75, 99, 101, 121, 166, 172, 174, 193, 194, 195, 200, 203, 204, 213.

For information about these error codes, see Return Codes for the cdr Utility

Example 1: Generate a consistency report

The following command uses two processes to generate a consistency report for each of the two replicates in the set in parallel for a replicate set named replset1, comparing the data on the server serv2 with the data on the server serv1: 
cdr check replicateset --master=g_serv1 --replset=replset_1 g_serv2 \
--process=2
The summary consistency report for the previous command might be: 
Jan 17 2010 15:46:45 ------   Table scan for repl1 start  --------

------   Statistics for repl1 ------
Node                  Rows     Extra   Missing  Mismatch Processed
---------------- --------- --------- --------- --------- ---------
g_serv1                 52         0         0         0         0
g_serv2                 52         0         0         0         0

Jan 17 2010 15:46:55 ------   Table scan for repl1 end   ---------

Jan 17 2010 15:46:46 ------   Table scan for repl2 start  --------

------   Statistics for repl2 ------
Node                  Rows     Extra   Missing  Mismatch Processed
---------------- --------- --------- --------- --------- ---------
g_serv1                 48         0         0         0         0
g_serv2                 48         0         0         0         0

Jan 17 2010 15:47:05 ------   Table scan for repl2 end   ---------

This report indicates that the replicate set is consistent on these servers.

The consistency report for replicate sets shows a series of consistency reports for individual replicates that has the same format as the reports run with the cdr check replicate command.

Example 2: Enable and synchronize a replication server

The following command enables a replication server named g_serv2 and repairs inconsistencies by time stamp on all of its replicate sets:

cdr check replicateset  --repair --enable\
--timestamp --allrepl g_serv2

The master server is not specified because the --timestamp option is used. The replicate set name is not specified because the --allrepl option is used.

Example 3: Repair inconsistencies based on time stamp

The following command repairs inconsistencies based on the most recent time stamps for all replicate on all replication servers:

cdr check replicateset --all --repair --timestamp --allrepl