Informix Tools - Compare Row
- Name
- Synopsis
- Description
- Options
- Examples
- Sample Output
- Returncodes
- Diagnostics
- Requires
- Author
- Download
cmp_row - Compare/Synchronize Data in an INFORMIX ER (Enterprise Replication) Environment
cmp_row -s db@db_srv:tab -t db@db_srv:tab [-k|-i|-u|-w #sec] [-S 'SQL-Select-Source' -T 'SQL-Select-Target']
cmp_row is an Esql/C-program that an be used to compare and synchronize data
located in distributed tables in an INFORMIX Enterprise Replication environment.
The rows from a source table are compared against the rows from a target table.
Inconsistencies are reported with the difference in the column values and the primary key
of the row. The automatic synchronisation of detected inconsistencies is possible.
- -s source table
- -t target table
- -k shared lock
- -i insert rows into target-table
- -u update rows in target-table
- -w wait for lock
- -S Source select
- -T Target select
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.
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.
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 (compare the results and check if always the same rows are reported), to avoid the blocking of other transactions.
This option instructs cmp_row to synchronize the rows in the target table with the values from the source table. If the row is missing in the target table, it will be inserted. After this synchronisation the rows from the source table and the rows from the target table will be consistent.
This option instructs cmp_row to synchronize the rows in the target table with the values from the source table. If any of the columns are different between source and target, the target row will be updated. After this synchronisation the rows from the source table and the rows from the target table will be consistent.
Number of seconds to wait for a lock to be released.
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.
This argument behaves in the same way as -S.
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 -u
-> compare and synchronize (insert/update) all rows from table "state"
between source and target
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"
Command: cmp_row -s stores_demo@iif930_shm:state -t stores_test@iif930_tcp:state -i -u -------- [2003-06-08 16:06:47] < connect to stores_eh1@iif930_shm as "conn_target" with concurrent transaction > [2003-06-08 16:06:47] < set isolation to committed read > [2003-06-08 16:06:48] < connect to stores_eh@iif930_tcp as "conn_source" with concurrent transaction > [2003-06-08 16:06:48] < set isolation to committed read > ================================================================================ Source Table : [stores_eh@iif930_shm:state] Target Table : [stores_eh1@iif930_tcp:state] Insert Table : [Yes] Update Table : [Yes] 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 INFO: Row successfully synchronized on target table PK(0)[code ][Character ]: AA WARNING : This row does not exist in the target table INFO: Row successfully synchronized on target table [2003-06-08 16:06:48] < [53] rows checked, READY !! > [2003-06-08 16:06:48] < OK : [1] rows updated in target table > [2003-06-08 16:06:48] < OK : [1] rows inserted into target table > [2003-06-08 16:06:48] < disconnect all >
This example shows two differences:
- PK(0)[code ][Character ]: CA
- PK(0)[code ][Character ]: AA
For the primary key (CA) there is a difference in column sname.
Source-Row: Calif-BEACH Target-Row: California
The primary key AA does not exist in the target-table.
Both rows have been automatically synchronized on the target table by cmp_row because the options -i and -u have been both specified.
cmp_row will deliver the following returncodes:
- 0=RET_OK
- 1=RET_ERR
- 2=RET_DIFF
Inconsistencies have been detected
Program finished successfully, no differences
Program encountered an error
Debugging can be turned on by setting the environment variable DEBUG_CMP_ROW before starting the program. The whole debug information can be saved to a file via i/o-redirection.
Example: -------- export DEBUG_CMP_ROW=1 cmp_row -s stores_demo@iif930_shm:state -t stores_test@iif930_tcp:state > cmp_row.out 2>&1 &
- C-Compiler plus Informix-ClientSDK to compile cmp_row
- Informix-ClientSDK or -Connect to execute cmp_row