NAME

cmp_row - Check the consistency of rows in an INFORMIX-ER (Enterprise Replication) configuration

SYNOPSIS

cmp_row -s db@db_srv:tab -t db@db_srv:tab [-k|-w #sec] [-S ``SQL-Select-Source'' -T ``SQL-Select-Target'']

DESCRIPTION

cmp_row is an esql/c-program which can be used to check the consistency of distributed tables in an INFORMIX-replication-environment.

The rows from primary-table are checked against the rows a secondary-table.

Inconsistencies are reported with the difference in the column-values and the primary key of the row.

OPTIONS

-s source table
This is the name of the source-table that should be the reference for the check.

The format of this parameter is db@dbserver:tablename.

-t target table
This is the name of the target-table which should be compared to the source-table.

The format of this parameter is db@dbserver:tablename.

-k shared lock
Place a shared lock on both tables during the check.

Be careful with this option, because the both tables will be locked during the check. That means that no parallel data-changes will be possible.

This option guarantees a consistent check, but it might be better to execute the program several times (checking if always the same differences are reported), to avoid the blocking of other transactions.

-w wait for lock
Number of seconds to wait for a lock to be released.

-S Source select
If you don't want to check the whole table, you can specify the select-statement.

This allows you to restrict the check in vertical (only named columns) and horizontal (where-clause> direction.

-T Target select
This argument behaves in the same way as -S.

EXAMPLES

 cmp_row -s stores7@dbsrv_1:state -t stores7@dbsrv_1:state -k
 -> compare all rows and all columns from table "state"
    with a share lock on both tables
 cmp_row -s stores7@dbsrv_1:state -t stores7@dbsrv_1:state \ 
         -S "select * from state where code matches 'C*'" \ 
         -T "select * from state"
 -> compare only the rows from table "state" which meet the where-clause
 cmp_row -s stores7@dbsrv_1:state -t stores7@dbsrv_1:state \ 
         -S "select sname from state" \ 
         -T "select sname from state"
 -> compare all rows from table "state" but only for column "sname"
 cmp_row -s stores7@dbsrv_1:state -t my_stores@dbsrv_1:my_state \ 
         -S "select sname from state" \ 
         -T "select my_sname from my_state"
 -> compare column "sname" from table "state" to column "my_sname" from table "my_state"

SAMPLE OUTPUT

 ================================================================================
 Source Table        :  [stores7@aesngrp:state]
 Target Table        :  [stores7@replgrp:state]
 Lock Table          :  [No]
 Wait for Lock (sec) :  [0]
 Isolation Level     :  [committed read]
 Explain On          :  [No]
 Source Select       :  [select * from state]
 Target Select       :  [select * from state]


 PK(#) -> Primary Key Column
 S-Row -> Source Row
 T-Row -> Target Row
 ================================================================================

 PK(0)[code              ][Character ]: CA  
 S-Row[sname             ]: Calif-BEACH      
 T-Row[sname             ]: California

 PK(0)[code              ][Character ]: AA  
 WARNING: This row does not exist in the target table

This example shows two differences:

RETURNCODES

cmp_row will deliver the following returncodes:

0=RET_OK Program finished successfully, no differences

1=RET_ERR Program encountered an error

2=RET_DIFF Inconsistencies have been detected

DIAGNOSTICS

Debugging can be turned on by setting the environment variable DEBUG_CMP_ROW before starting the script.

The whole debug information can be saved to a file with the use of i/o-redirection.

Example:

 export DEBUG_CMP_ROW=1
 cmp_row -s stores7@master_grp:state -t stores7@slave_grp:state > cmp_row.out 2>&1 &

REQUIRES

AUTHOR

eric@herber-consulting.de