Using rs_subcmp to locate and correct inconsistencies

For Sybase databases, the standalone executable program rs_subcmp compares a replicate table to the primary version of the table, finding—and correcting if you so choose—missing, orphaned, and inconsistent rows. On UNIX systems, the program is called rs_subcmp. On PC systems, the program is called subcmp.

The rs_subcmp program is located in the bin subdirectory of the Sybase release directory. Refer to the Replication Server installation and configuration guides for your platform for more information.

The program works by logging in to the primary data server and the replicate data server, and selecting and comparing rows from both tables.

Because some differences between primary and replicate data can be attributed to latency, rs_subcmp first identifies inconsistencies, and then performs iterations a specified number of times. rs_subcmp waits for any updates to be replicated before removing the corrected rows from its list.

It is best to use rs_subcmp when latency is low to avoid the program having to perform several iterations through the data.

You can instruct rs_subcmp to display inconsistent rows on the standard output, correct them, or both display and correct them.

Creating a configuration file avoids the need for complex command lines, which are prone to errors. Here is an rs_subcmp configuration file that compares the sales table in the pubs2 database in the data servers TOKYO_DS and SYDNEY_DS:

PDS=TOKYO_DS
RDS=SYDNEY_DS
PDB=pubs2
RDB=pubs2
RTABLE=sales
RSELECT=select * from sales \
		order by stor_id, ord_num
RUSER=sa
KEY=stor_id
KEY=ord_num
RECONCILE=Y
RECONCILE_CHECK=Y
WAIT=15
NUM_TRIES=5
VISUAL=Y

The PTABLE, PSELECT, and PUSER parameters, which are used for the primary database, are not shown in this example. Their values are the same as those of corresponding parameters in the replicate databases, so they need not be included in the configuration file.

The RSELECT line and the PSELECT line (if used) must be entered on one line. To continue a line onto the next line (row), precede each newline character with a backslash as, for example:

RSELECT=select * from sales \
		order by stor_id, ord_num

When you execute rs_subcmp, you can override values in the configuration file with command line options. For example, rather than changing the name of the TOKYO_DS data server to TOKYO_DS2 in the configuration file, you can specify it on the command line, using the -S flag, as the following example illustrates:

rs_subcmp -f sales_cmp -S TOKYO_DS2 > sales_badrows

In this example, the -f option specifies a configuration file name, sales_cmp. If the VISUAL parameter is set to “Y” in the configuration file (equivalent to the -V command line option), a list of the inconsistent rows is generated. In this example, the output is redirected to a file.

Schema comparison is useful in comparing schema between two databases that may have the same data but different schemas.

For example, if you want to compare all schemas between two databases using the config.cfg file:

rs_subcmp -f config.cfg

A report file which details the comparison result between two tables or two databases is created after every schema comparison. The report file is named reportPROCID.txt. If inconsistencies exist, rs_subcmp creates a reconciliation script named reconcilePROCID.sql. The report file and the reconciliation script are saved in the same directory where you issued the rs_subcmp.

See rs_subcmp in Chapter 7, “Executable Programs” of the Replication Server Reference Manual for detailed information in using schema comparison.

To verify the reconciliation of statements before execution, a reconciliation file can be created using the rs_subcmp command. You can use the command line option -g with rs_subcmp or you can set the configuration file parameter RECONCILE_FILE to “Y” to indicate the creation of a reconciliation file.

Hash algorithm improves the performance of rs_subcmp and compresses the data in primary and replicated tables. The compressed data is then fetched by rs_subcmp.

Instead of taking the entire row of data during comparison between the primary table and replicated table, rs_subcmp now transfers only the compressed data of each data row from the primary or replicated tables, and then verifies or reconciles inconsistencies between them.

For an improve rs_subcmp performance, use the command line parameters -h or -H or their equivalent configuration file parameters FASTCMP or HASH_OPTION.

The rs_subcmp program has a large number of options, which you can specify on the command line or in a configuration file. Refer to Chapter 7, “Executable Programs,” in the Replication Server Reference Manual for a list of these configuration file parameters and command line options.