Schema comparison

In Replication Server 15.0.1, the rs_subcmp function has been expanded to include schema comparison between tables and databases. This is useful in comparing schema between two databases that may have the same data but different schemas.

Table 4-5 and Table 4-6 enumerate the schema types and subtypes supported by rs_subcmp.

**Table 4-5: 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.

**Table 4-6: Schema sub-types 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

The command line and configuration file parameter set has been expanded to support the rs_subcmp schema comparison. Table 4-7 describes the new rs_subcmp command line parameters.

**Table 4-7: Command line parameters for schema comparison**
Parameter name	Description	Valid values
-x	comparison flag	0 (default) – data comparison. 1 – database schema comparison. 2 – table schema comparison.
-X	filter flag	If the value starts with “+”, only the schema types are selected for comparison, and subtypes are ignored. Otherwise, the schema types and sub types are both unselected and not used for comparison.
-I	interface file	Interface file location.

Table 4-8 describes the new configuration file parameters.

**Table 4-8: Configuration file parameters for schema comparison**
Item name	Description	Valid values
SCHEMAFLAG	comparison flag	0 (default) – data comparison. 1 – database schema comparison. 2 – table schema comparison.
FILTER	filter flag	If the value starts with “+”, only the schema types are selected for comparison, and sub-types are ignored. Otherwise, the schema types and subtypes are both unselected and not used for comparison.
IFILE	interface file	Interface file location.

Examples

Example 1 Compares all schemas between two databases using the config.cfg file:

rs_subcmp -f config.cfg

The configuration file contains the following:

PDS = PASE
RDS = R2ASE
PDB = pubs2
PTABLE = authors
RTABLE = authors
PUSER = sa
RUSER = sa
PPWD =
RPWD =
SCHEMAFLAG = 1

Example 2 Compares schema between two databases without a configuration file:

rs_subcmp -Spds -srds -Dpdb -drdb -Usa -usa -Psa_pwd
          -psa_pwd -x1

Example 3 Compares schema of two databases excluding index, trigger, and datatype:

rs_subcmp -Spds -srds -Dpdb -drdb -Usa -usa -Psa_pwd
          -psa_pwd -x1 -XitD

Example 4 Compares all table schemas and user schemas:

rs_subcmp -Spds -srds -Dpdb -drdb -Usa -usa -Psa_pwd
          -psa_pwd -x1 -X+TU

Report and reconciliation files

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.