rs_subcmp

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 Windows 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] [-g] [-h]]
 [-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]
 [-H normalization_option]

Parameters

Examples

Usage

  • Run rs_subcmp when primary changes do not occur.

  • The SYBASE environment variable, and the library path environment variable must be set for rs_subcmp to work.

    Set the SYBASE environment variable to the Sybase release directory.

    Set the library path variable to $SYBASE/$SYBASE_OCS/lib (UNIX) or %SYBASE%\%SYBASE_OCS%\lib (Windows):
    • For Solaris and Linux, the library path variable is LD_LIBRARY_PATH.

    • For HP, the library path variable is SHLIB_PATH.

    • For RS6000, the library path variable is LIBPATH.

    • For Windows, the library path variable is PATH.

  • rs_subcmp must be able to locate and successfully run the ddlgen executable file for schema comparison to work. You can use the DDLGENLOC environment variable to set the location of ddlgen. If DDLGENLOC is not set, rs_subcmp looks for ddlgen at its default location, which is at %SYBASE%\ASEP\bin\ddlgen. To ensure that ddlgen runs successfully, the environment variables that ddlgen uses must be set correctly.

    The SYBROOT environment variables must also be set to the SYBASE environment variable.

  • The following requirements apply to rs_subcmp:
    • If you provide a configuration file and also use command line options, the command line values override the values in the configuration file.

    • The lowercase options -d, -c, -u, -p, and -t provide values for both primary and replicated data. Use the uppercase options to override the values for primary data.

    • The only required uppercase option is -S.

    • The primary key specified with -k must be unique. If you do not specify any primary key columns with the -k option, all columns are considered to be part of the primary key.

    • Use a positive integer in -L to specify a new value, overwriting the default value of 26K, for the byte length of text and image columns:

      -L = <new_value>

      For instance, if you want text and image columns to be 65,536 bytes, enter:

      -L = <64>
  • These options can be used to specify a non-default table owner or a different primary replicate table or column name:
    • For options -t, -T, -c, and -C, table owner information can be included (for example, ling.authors).

    • Owner, table, and column names specified for the -c option should be those of the replicate table.

    • Owner, table, and column names specified for the -C option should be those of the primary table.

    • The column name specified for the -k option is the column name of the replicate table.

  • rs_subcmp creates a report file after every schema comparison. The report file details the comparison result between two tables or two databases. 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 from which rs_subcmp executed.

  • The reconciliation file’s SQL statements cannot contain text, unitext, or image.

  • rs_subcmp creates a reconciliation file if you specify the -g option. The file is named reconcile_file_PROCID.sql and is located at the current working directory.

Return codes

The following return codes can be returned by rs_subcmp:

rs_subcmp Return Codes

Return code

Meaning

0

The replicated and primary tables are the same.

1

An error occurred while executing rs_subcmp.

2

The replicated and primary tables are different.

Configuration file

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.

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

rs_subcmp Configuration File Parameters

Configuration parameter

Command-line option

Value

PDS

-S

Primary data server name

RDS

-s

Replicate data server name

PDB

-D

Primary database name

RDB

-d

Replicate database name

PTABLE

-T

Primary table name

RTABLE

-t

Replicate table name

PUSER

-U

Primary user name

RUSER

-u

Replicate user name

PPWD

-P

Primary password

RPWD

-p

Replicate password

KEY

-k

Primary key element in replicate table

PINITBATCH

-B

Primary database connection initialization batch. Can span multiple lines if newline characters are preceded by a “\” (backslash). Up to 1024 characters per line and 64K characters total are allowed.

RINITBATCH

-b

Replicate database connection initialization batch. Can span multiple lines if newline characters are preceded by a “\” (backslash). Up to 1024 characters per line and 64K characters total are allowed.

PSELECT

-C

Primary select command. Can span multiple lines if newline characters are preceded by a “\” (backslash). Up to 1024 characters per line and 64K characters total are allowed.

RSELECT

-c

Replicate select command. Can span multiple lines if newline characters are preceded by a “\” (backslash). Up to 1024 characters per line and 64K characters total are allowed.

RECONCILE

-r

Reconcile differences (Y or N)

RECONCILE_CHECK

-R

Reconcile differences with primary verification (Y or N)

TRACE

-z

Enable trace with optional level (optional integer)

FPRECISION

-e

Expected floating point precision (integer—default is platform-dependent)

RPRECISION

-E

Expected real precision (integer—default is platform-dependent)

WAIT

-w

Seconds between comparisons (integer—default is 5 seconds)

NUM_TRIES

-n

Number of comparisons (integer—default is 10 iterations)

VISUAL

-V

Print results (Y or N)

IDENTITY

-i

identity column name in replicate table

TXT_IMG_LEN

-L

The longest value, in kilobytes, the data server returns for text, unitext, or image columns.

NO_LOG

-l

Do not log updates for this replicate text, unitext, or image column

NULLABLE

-N

The text, unitext, or image column in the replicate table accepts null values.

LANGUAGE

-Z

Language of rs_subcmp error and informational messages

SORT_ORDER

-o

Use the specified sort order to compare primary key columns.

SORT_ORDER_ALL_COLS

-O

Use the specified sort order to compare all columns.

SCHARSET

-j

Character set of rs_subcmp

RCHARSET

-J

Character set of the replicate data server

REP_PRI_COLNAME

-a

Replicate-Primary column name pair

UNICODE_SORT_ORDER

-q

The Unicode sort order rs_subcmp uses to compare Unicode primary key columns.

UNICODE_SORT_ORDER_ALL_COLS

-Q

The Unicode sort order rs_subcmp uses to compare all Unicode columns.

SCHEMAFLAG

-x

The rs_subcmp comparison type.

FILTER

-X

The filter used to indicate the schema and schema subtypes included or excluded in the schema comparison. See Schema types supported by rs_subcmp and Schema subtypes supported by rs_subcmp for a list of schema types and schema subtypes supported by rs_subcmp.

IFILE

-I

The interface file location.

RECONCILE_FILE

-g

Indicates whether to create a reconciliation file or not.

Values:
  • Y – create reconciliation file.

  • N – do not create reconciliation file.

Default: N

FASTCMP

-h

Indicates whether to perform fast comparison or not.

Values:
  • Y – perform fast comparison using compressed data.

  • N – perform normal comparison.

Default: N

HASH_OPTION

-H

Indicates the normalization option used for fast comparison. If this parameter is not included in the configuration file, rs_subcmp normalizes the data using native byte order and character set. See "Normalization options supported by rs_subcmp" table for a list of the normalization options supported by rs_subcmp.

Requirements for select commands

  • 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.

How rs_subcmp works

  • 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.

Reconciling inconsistencies

  • 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).

Reconciling IDENTITY columns

  • 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.

Reconciling text, unitext, or image datatypes

  • 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)

Using rs_subcmp in international environments

  • 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.

    Note: 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.

Requirements for character sets and sort orders

  • 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.

Using sort orders

  • 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.

Using Unicode sort orders

  • 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.

Schema Types Supported by rs_subcmp

Type

Description

A

All aliases in the database.

D

All defaults in the database.

E

All user-defined datatypes in the database.

G

All groups in the database.

R

All rules in the database.

T

All user tables in the database. Includes table elements such as indexes, keys, constraints, and triggers.

U

All users in the database.

V

All views in the database.

P

All procedures in the database.

Schema Subtypes Supported by rs_subcmp

Type

Description

c

Constraint

d

Bind default

f

Foreign key

g

Grant

i

Index

m

Procedure mode

p

Primary key

r

Bind rule

t

Trigger

Normalization Options Supported by rs_subcmp

Normalization option

Description

lsb

Normalizes all byte-order-dependent data to lsb-first (little-endian) byte order.

msb

Normalizes all byte-order-dependent to msb-first (big-endian) byte order.

unicode

Normalizes the character data to Unicode (UTF-16).

unicode_lsb

Normalizes lsb in conjunction with Unicode for platform independence.

unicode_msb

Normalizes msb in conjunction with Unicode for platform independence.