rs_subcmp

Description

An executable program that compares the data of a replicated table to the primary version of the table. rs_subcmp also performs schema comparison between replicated and primary tables and between replicated and primary databases. These features aid in finding—and optionally reconciling—missing, orphaned, and inconsistent rows and schemas. On UNIX systems, this 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. See the Replication Server installation and configuration guides for your platform for more information.

For rs_subcmp to work, the SYBASE environment variable, and the library path environment variable must be set. If you use rs_subcmp for schema comparison, ensure that rs_subcmp can locate the ddlgen executable file and that the ddlgen can successfully run in your Replication Server environment. See the Usage section for instructions.

rs_subcmp is intended to reconcile Sybase databases only.

Syntax

rs_subcmp [-R | -r] [-v] [-V] [-z[1 | 2]]
 [-f config_file] [-F]
 -S primary_ds [-D primary_db]
 -s replicate_ds [-d replicate_db]
 -t table_name [-T primary_table_name]
 -c select_command [-C primary_select_command]
 -u user [-U primary_user]
 [-p passwd] [-P primary_passwd]
 [-B primary_init_batch]
 [-b replicate_init_batch]
 [-n num_iterations] [-w wait_interval]
 [-e float_precision] [-E real_precision]
 [-k primary_key_column [-k primary_key_column]...]
 [-i identity_column]
 [-l text_image_column_name
 [-l text_image_column_name]...]
 [-L text_image_length_in_kilobytes]
 [-N text_image_column_name
 [-N text_image_column_name]...]
 [-Z language]
 [-o sort_order]
 [-O sort_order]
 [-J rs_subcmp_charset]
 [-j rep_charset]
 [-a replicate_column_name primary_column_name
 [-a replicate_column_name primary_column_name]...]
 [-q unicode_sort_order]
 [-Q unicode_sort_order]
 [-x schema_flag]
 [-X filter_flag]
 [-I interface_file]

Parameters

The following return codes can be returned by rs_subcmp:

You can create a file containing rs_subcmp parameters and specify it on the command line using the -f flag. Each line in the configuration file consists of a parameter name, an equal sign (=), and a value.

The following table lists the parameters that can be used in the rs_subcmp configuration file and the corresponding command line option for each parameter.

• The select commands specified by -c (RSELECT) and -C (PSELECT) must return columns with the same names and datatypes from both the primary and the replicate databases.

• You must have a clustered index on the primary key or an order by clause in the select command. select commands must order rows based on the primary key. If rs_subcmp does not receive rows in the correct order, it may delete rows in the replicate table.

• Do not select rs_address datatypes with the -c or -C options. If replicate tables contain columns using the rs_address datatype, the primary and replicate versions of these columns may not be identical. Replication Server filters out updates to these columns so as not to replicate them unnecessarily.

• rs_subcmp logs into the primary and replicate databases and executes the supplied select commands. It verifies that the commands return the same columns, based on the name and datatype of each column. If the returned columns match, rs_subcmp compares the primary and replicate rows and creates these lists:

  • Missing rows – rows at the primary, but not at the replicate

  • Orphaned rows – rows at the replicate, but not at the primary

  • Inconsistent rows – rows at the replicate and the primary with matching primary keys, but differences in other columns

• After the three lists are compiled rs_subcmp iterates for the specified number of times, checking:

  • If missing rows appear at the replicate

  • If orphaned rows disappear from the replicate

  • If inconsistent rows match

  • If the new replicate row value matches the primary row value from the previous iteration

• After the specified number of iterations, the contents of the three lists are printed to the standard output if you specified the -V option.

• rs_subcmp reconciles missing, orphaned, and inconsistent rows if you specify the -R or -r option.

• If you specify the -r option, rs_subcmp reconciles the primary and replicate copies. It passes the final lists and modifies the replicate table as follows:

  • Inserts rows remaining in the missing rows list

  • Deletes rows remaining in the orphaned rows list

  • Updates inconsistent rows to match the primary rows

• If you specify the -R option, rs_subcmp reconciles the replicate table to the primary version in the same way as with the -r option. However, before it inserts a missing row or deletes an orphaned row, it logs into the primary database and performs a select on the row to verify that:

  • The row still exists (in the case of a missing row in the replicate table), or

  • The row does not exist (in the case of an orphaned row in the replicate table).

• If the values in an identity column for a row are inconsistent, rs_subcmp reconciles them by deleting the row in the replicate database before inserting the row from the primary database.

• Unlike other datatypes, inconsistencies in text, unitext, or image values are not stored in a list. To reconcile a missing or inconsistent row that contains a text or image value, rs_subcmp logs back into the primary database and re-executes the select statement. If the inconsistent or missing row is found, rs_subcmp modifies the replicate table by updating or inserting the row. However, if the inconsistent or missing row is not found in the primary table, rs_subcmp takes the following actions:

  • For an inconsistent row, rs_subcmp deletes the row from the replicate table

  • For a missing row, rs_subcmp takes no action

• Using the Adaptive Server option set textsize as part of the select statement can limit the amount of text compared. For example, the following example shows the effect of setting the textsize to 10. The first select statement returns 30 characters of text:

  set textsize 30 select * from zetext
  
  a           b            c
  --------    ---------    -----------
  abba        apples       odd one here
  beta        banana       rotten
  caro        celery       not carrots
  

  The next select statement sets the size of the text to 10:

  1> set textsize 10 select * from zetext
  2> go
  a           b           c
  

  --------    ---------   ------------
  abba        apples      odd one
  beta        banana      rotten 
  caro        celery      not carrots
  ---------------------------------------------------
  
  (3 rows affected)
  

• rs_subcmp provides support for international environments with the -Zlanguage, -o sort_order, -O sort_order, -q unicode_sort_order, -Q unicode_sort_order, -J rs_subcmp_charset, and -j rep_charset options.

• rs_subcmp performs character set conversion when comparing and reconciling the replicate and primary versions of a table. The method is similar to how Replication Server converts character sets, so you can expect to see similar results.

  For example, if the primary and replicate data server’s character sets are incompatible, no conversion takes place. If the character sets are incompatible but a single character from the primary data server’s character set has no representation in the replicate server’s character set, the character is replaced with a “?” and processing continues.

• rs_subcmp uses the character set of the replicate data server in all operations involving user data. To specify the replicate data server’s character set, use the -j command line option or the RCHARSET configuration file parameter.

  rs_subcmp does not have a parameter for the primary data server’s character set because all data operations are done in the replicate data server’s character set. The program depends on the primary data server to convert all character data to the replicate data server’s character set. This is comparable to how Replication Server works during subscription materialization.

• You can also specify a character set for rs_subcmp if it is different from the replicate data server’s character set. To do this, use the -J command line option or the SCHARSET configuration file parameter. When you specify a character set, rs_subcmp converts its string-type configuration parameters from the rs_subcmp character set to the replicate data server’s character set.

• The following requirements apply for specifying character sets and sort orders in rs_subcmp:

  • All characters in object names (including servers, databases, tables, and column names) must be compatible with the rs_subcmp_charset and rep_charset character sets; otherwise rs_subcmp will fail to execute.

  • If the character sets of the replicate and primary data servers differ, the replicate data server’s character set must be installed at the primary data server. This enables the primary data server to do character set translation.

  • If the replicate and primary data servers use different sort orders and the where clause of the select statement includes character or text datatypes, results may be confusing. To avoid confusion, run rs_subcmp first without the -r or -R (reconcile) options and with the -V (visual) option to see the potential effects on your data.

• You can specify nonUnicode sort order in two ways: using the -o option or using the -O option.

• If you specify the -o option, rs_subcmp:

  1. Performs a simple binary comparison of the primary key columns.

  2. If the primary keys match, rs_subcmp performs a binary comparison of the remaining columns. If they don’t match, an inconsistent row is reported.

  3. If the primary key columns do not match, rs_subcmp compares them using the specified sort order.

     • If the primary key columns don’t match, the row is reported missing or orphan.

     • If the primary key columns test equal using the sort order, the row is reported inconsistent.

• If you specify the -O option, rs_subcmp:

  • Performs a column comparison using the specified sort order for all columns of types char, varchar, and text.

  • Does not perform a binary comparison.

• If no sort order is specified, rs_subcmp performs a simple binary comparison on each column of the primary and replicate row.

• You can specify Unicode sort order in two ways: using the -q option or using the -Q option.

• If you specify the -q option, rs_subcmp:

  1. Performs a simple binary comparison of the Unicode primary key columns.

  2. If the primary keys match, rs_subcmp performs a binary comparison of the remaining columns. If they don’t match, an inconsistent row is reported.

  3. If the primary key columns do not match, rs_subcmp compares them using the specified sort order.

     • If the Unicode primary key columns don’t match, the row is reported missing or orphan.

     • If the primary key columns test equal using the sort order, the row is reported inconsistent.

• If you specify the -Q option, rs_subcmp:

  • Performs a column comparison using the specified sort order for all Unicode columns.

  • Does not perform a binary comparison.

• If no sort order is specified, rs_subcmp performs a simple binary comparison on each Unicode column of the primary and replicate row.