When you observe that there are some data differences between source and target tables in DB2 Q replication, we can use these 2 utilities( asntdiff, asntrep ) which can compare the source and target and repair them based on our requirement to synchronize the data.
ASNTDIFF : asntdiff utility which can compare two relational tables and generate a list of differences between the two.
ASNTREP : asntrep utility will syncronize a source and target table by repairing differences between the two tables.
Important note : Its always good to synchronize the tables when there is less amount of activity or no activity going at source so that we can have perfectly synchronized table.
Below tutorial demonstrates the use of asntdiff to compare the source and target and asntrep to repair the target table based on differences generated by asntdiff. Below is the data populated on source and target without replication enabled. Then i have created the subscription for the table AUCTION with target having no load option so that it wont try to reload the data using source as we are in process syncing the tables
1) In this first step i have created a table with same definition in source and target and loaded data with some differences in both of them
db2inst1@murali:~$ db2 connect to testa
Database Connection Information
Database server = DB2/LINUXX8664 9.7.9
SQL authorization ID = DB2INST1
Local database alias = TESTA
db2inst1@murali:~$ db2 "select * from auction"
ITEMID CATEGORY PRICE
---------- ---------- -----------
A0001 HOME 54
A0002 GENERAL 17
A0003 SPARES 120
A0004 GENERAL 32
A0006 GENERAL 40
5 record(s) selected.
db2inst1@murali:~$ db2 connect to testb
Database Connection Information
Database server = DB2/LINUXX8664 9.7.9
SQL authorization ID = DB2INST1
Local database alias = TESTB
db2inst1@murali:~$ db2 "select * from auction"
ITEMID CATEGORY PRICE
---------- ---------- -----------
A0001 HOME 15
A0002 HOME 12
A0003 HOME 35
A0006 HOME 50
4 record(s) selected.
2) I'm here considering TESTA as source database/correct data and TESTB is having wrong data which i want to synchronize. Let us run the asntdiff utility to find the differences between the source and target tables. As showing below in the ending line we can see that it has given us the number of differences found between the source and target table: "5". These details can be found in database "TESTA", and it has stored the diff values in the difference table DIFFSCHEMA1.DIFF_AUCTION
db2inst1@murali:~$ asntdiff DB=TESTA SCHEMA=ASNTA DIFF_SCHEMA=DIFFSCHEMA1 DIFF=DIFF_AUCTION where="SUBNAME = 'AUCTION0001'"
2014-05-17-17.12.21.904722 ASN0600I "AsnTDiff" : "" : "Initial" : Program "asntdiff 9.7.9" is starting.
2014-05-17-17.12.22.005751 ASN4012I "AsnTDiff" : "ASNTA" : "Initial" : The program is comparing tables using the list of parameters following this message.
TDIFF TABLE = "DIFFSCHEMA1"."DIFF_AUCTION" ;
MAXIMUM COUNT = 100000 ;
CONNECT TO TESTA ;
SELECT "CATEGORY" AS "CATEGORY", "PRICE" AS "PRICE", "ITEMID" AS "ITEMID" FROM "DB2INST1"."AUCTION" ORDER BY 3 ;
CONNECT TO TESTB ;
SELECT "CATEGORY", "PRICE", "ITEMID" FROM "DB2INST1"."AUCTION" ORDER BY 3 ;
CREATE TABLE "DIFFSCHEMA1"."DIFF_AUCTION" (
"DIFF " CHAR(4),
"ITEMID" VARCHAR(10)
) ;
2014-05-17-17.12.22.416439 ASN4006I "AsnTDiff" : "ASNTA" : "Initial" : Between the source table and the target table, there are "0" common rows, "5" rows that are unique to the source table, and "4" rows that are unique to the target table.
2014-05-17-17.12.22.438578 ASN4010I "AsnTDiff" : "ASNTA" : "Initial" : Number of differences found between the source and target table: "5". The details can be found in database "TESTA", difference table ""DIFFSCHEMA1"."DIFF_AUCTION"".
----------------------------------------------------------------------
3) Let us see what differences it has identified between source and target and have stored in the difference table. As show below we can see it contains 4 UPDATES and 1 INSERT
db2inst1@murali:~$ db2 "select * from "DIFFSCHEMA1"."DIFF_AUCTION""
DIFF ITEMID
----- ----------
U 2 A0001
U 2 A0002
U 2 A0003
U 2 A0006
I 2 A0004
5 record(s) selected.
4) Now to repair the table at target we are running asntrep using the same difference table, that was created in earlier step. So it should perform 4 updates and 1 insert in target side. As shown in the ending line we can see The program applied the rows from the difference table to the target table as follows: "1" rows were inserted, "4" rows were updated, and "0" rows were deleted.
db2inst1@murali:~$ asntrep DB=TESTA SCHEMA=ASNTA DIFF_SCHEMA=DIFFSCHEMA1 DIFF=DIFF_AUCTION WHERE="SUBNAME = 'AUCTION0001'"
2014-05-17-17.13.49.984558 ASN0600I "AsnTRep" : "" : "Initial" : Program "asntrep 9.7.9" is starting.
CREATE TABLE "DIFFSCHEMA1"."DIFF_AUCTION-" (
"DIFF " CHAR(4),
"ITEMID" VARCHAR(10)
) ;
DELETE
FROM "DB2INST1"."AUCTION" T
WHERE EXISTS (
SELECT 1 FROM "DIFFSCHEMA1"."DIFF_AUCTION-" D
WHERE ( T."ITEMID" = D."ITEMID" OR
T."ITEMID" IS NULL AND D."ITEMID" IS NULL )
AND D."DIFF " LIKE 'D%' ) ;
INSERT INTO "DB2INST1"."AUCTION" (
"CATEGORY",
"PRICE",
"ITEMID" ) VALUES( ?, ?, ? ) ;
SELECT T.*
FROM ( SELECT "CATEGORY" AS "CATEGORY",
"PRICE" AS "PRICE",
"ITEMID" AS "ITEMID" FROM "DB2INST1"."AUCTION"
) AS T
WHERE EXISTS ( SELECT 1 FROM "DIFFSCHEMA1"."DIFF_AUCTION" AS D
WHERE ( T."ITEMID" = D."ITEMID" OR
T."ITEMID" IS NULL AND D."ITEMID" IS NULL )
AND D."DIFF " LIKE 'I%' ) FOR READ ONLY ;
UPDATE "DB2INST1"."AUCTION"
SET
"CATEGORY" = ?,
"PRICE" = ?
WHERE
"ITEMID" = ?
SELECT T.* FROM ( SELECT "CATEGORY" AS "CATEGORY", "PRICE" AS "PRICE", "ITEMID" AS "ITEMID" FROM "DB2INST1"."AUCTION" ) AS T WHERE EXISTS ( SELECT 1 FROM "DIFFSCHEMA1"."DIFF_AUCTION" AS D WHERE ( T."ITEMID" = D."ITEMID" OR T."ITEMID" IS NULL AND D."ITEMID" IS NULL ) AND D."DIFF " LIKE 'U%' ) FOR READ ONLY ;
2014-05-17-17.13.50.321421 ASN4019I "AsnTRep" : "ASNTA" : "Initial" : The program applied the rows from the difference table to the target table as follows: "1" rows were inserted, "4" rows were updated, and "0" rows were deleted.
----------------------------------------------------------------------
5) Now lets check the data in target to see if it really worked.
db2inst1@murali:~$ db2 connect to testb
Database Connection Information
Database server = DB2/LINUXX8664 9.7.9
SQL authorization ID = DB2INST1
Local database alias = TESTB
db2inst1@murali:~$ db2 "select * from auction"
ITEMID CATEGORY PRICE
---------- ---------- -----------
A0001 HOME 54
A0002 GENERAL 17
A0003 SPARES 120
A0006 GENERAL 40
A0004 GENERAL 32
5 record(s) selected.
NOTE:
From my experience when we need to synchronize big tables we need to specify the conditions on which asntdiff and asntrep should run, else it might take a lot of time to find the differences between source and target, also sometimes it also fails. Its always important to understand the business logic and use these utilities with utmost care to make sure we moving in right direction.
ASNTDIFF : asntdiff utility which can compare two relational tables and generate a list of differences between the two.
ASNTREP : asntrep utility will syncronize a source and target table by repairing differences between the two tables.
Important note : Its always good to synchronize the tables when there is less amount of activity or no activity going at source so that we can have perfectly synchronized table.
Below tutorial demonstrates the use of asntdiff to compare the source and target and asntrep to repair the target table based on differences generated by asntdiff. Below is the data populated on source and target without replication enabled. Then i have created the subscription for the table AUCTION with target having no load option so that it wont try to reload the data using source as we are in process syncing the tables
1) In this first step i have created a table with same definition in source and target and loaded data with some differences in both of them
db2inst1@murali:~$ db2 connect to testa
Database Connection Information
Database server = DB2/LINUXX8664 9.7.9
SQL authorization ID = DB2INST1
Local database alias = TESTA
db2inst1@murali:~$ db2 "select * from auction"
ITEMID CATEGORY PRICE
---------- ---------- -----------
A0001 HOME 54
A0002 GENERAL 17
A0003 SPARES 120
A0004 GENERAL 32
A0006 GENERAL 40
5 record(s) selected.
db2inst1@murali:~$ db2 connect to testb
Database Connection Information
Database server = DB2/LINUXX8664 9.7.9
SQL authorization ID = DB2INST1
Local database alias = TESTB
db2inst1@murali:~$ db2 "select * from auction"
ITEMID CATEGORY PRICE
---------- ---------- -----------
A0001 HOME 15
A0002 HOME 12
A0003 HOME 35
A0006 HOME 50
4 record(s) selected.
2) I'm here considering TESTA as source database/correct data and TESTB is having wrong data which i want to synchronize. Let us run the asntdiff utility to find the differences between the source and target tables. As showing below in the ending line we can see that it has given us the number of differences found between the source and target table: "5". These details can be found in database "TESTA", and it has stored the diff values in the difference table DIFFSCHEMA1.DIFF_AUCTION
db2inst1@murali:~$ asntdiff DB=TESTA SCHEMA=ASNTA DIFF_SCHEMA=DIFFSCHEMA1 DIFF=DIFF_AUCTION where="SUBNAME = 'AUCTION0001'"
2014-05-17-17.12.21.904722 ASN0600I "AsnTDiff" : "" : "Initial" : Program "asntdiff 9.7.9" is starting.
2014-05-17-17.12.22.005751 ASN4012I "AsnTDiff" : "ASNTA" : "Initial" : The program is comparing tables using the list of parameters following this message.
TDIFF TABLE = "DIFFSCHEMA1"."DIFF_AUCTION" ;
MAXIMUM COUNT = 100000 ;
CONNECT TO TESTA ;
SELECT "CATEGORY" AS "CATEGORY", "PRICE" AS "PRICE", "ITEMID" AS "ITEMID" FROM "DB2INST1"."AUCTION" ORDER BY 3 ;
CONNECT TO TESTB ;
SELECT "CATEGORY", "PRICE", "ITEMID" FROM "DB2INST1"."AUCTION" ORDER BY 3 ;
CREATE TABLE "DIFFSCHEMA1"."DIFF_AUCTION" (
"DIFF " CHAR(4),
"ITEMID" VARCHAR(10)
) ;
2014-05-17-17.12.22.416439 ASN4006I "AsnTDiff" : "ASNTA" : "Initial" : Between the source table and the target table, there are "0" common rows, "5" rows that are unique to the source table, and "4" rows that are unique to the target table.
2014-05-17-17.12.22.438578 ASN4010I "AsnTDiff" : "ASNTA" : "Initial" : Number of differences found between the source and target table: "5". The details can be found in database "TESTA", difference table ""DIFFSCHEMA1"."DIFF_AUCTION"".
----------------------------------------------------------------------
3) Let us see what differences it has identified between source and target and have stored in the difference table. As show below we can see it contains 4 UPDATES and 1 INSERT
db2inst1@murali:~$ db2 "select * from "DIFFSCHEMA1"."DIFF_AUCTION""
DIFF ITEMID
----- ----------
U 2 A0001
U 2 A0002
U 2 A0003
U 2 A0006
I 2 A0004
5 record(s) selected.
4) Now to repair the table at target we are running asntrep using the same difference table, that was created in earlier step. So it should perform 4 updates and 1 insert in target side. As shown in the ending line we can see The program applied the rows from the difference table to the target table as follows: "1" rows were inserted, "4" rows were updated, and "0" rows were deleted.
db2inst1@murali:~$ asntrep DB=TESTA SCHEMA=ASNTA DIFF_SCHEMA=DIFFSCHEMA1 DIFF=DIFF_AUCTION WHERE="SUBNAME = 'AUCTION0001'"
2014-05-17-17.13.49.984558 ASN0600I "AsnTRep" : "" : "Initial" : Program "asntrep 9.7.9" is starting.
CREATE TABLE "DIFFSCHEMA1"."DIFF_AUCTION-" (
"DIFF " CHAR(4),
"ITEMID" VARCHAR(10)
) ;
DELETE
FROM "DB2INST1"."AUCTION" T
WHERE EXISTS (
SELECT 1 FROM "DIFFSCHEMA1"."DIFF_AUCTION-" D
WHERE ( T."ITEMID" = D."ITEMID" OR
T."ITEMID" IS NULL AND D."ITEMID" IS NULL )
AND D."DIFF " LIKE 'D%' ) ;
INSERT INTO "DB2INST1"."AUCTION" (
"CATEGORY",
"PRICE",
"ITEMID" ) VALUES( ?, ?, ? ) ;
SELECT T.*
FROM ( SELECT "CATEGORY" AS "CATEGORY",
"PRICE" AS "PRICE",
"ITEMID" AS "ITEMID" FROM "DB2INST1"."AUCTION"
) AS T
WHERE EXISTS ( SELECT 1 FROM "DIFFSCHEMA1"."DIFF_AUCTION" AS D
WHERE ( T."ITEMID" = D."ITEMID" OR
T."ITEMID" IS NULL AND D."ITEMID" IS NULL )
AND D."DIFF " LIKE 'I%' ) FOR READ ONLY ;
UPDATE "DB2INST1"."AUCTION"
SET
"CATEGORY" = ?,
"PRICE" = ?
WHERE
"ITEMID" = ?
SELECT T.* FROM ( SELECT "CATEGORY" AS "CATEGORY", "PRICE" AS "PRICE", "ITEMID" AS "ITEMID" FROM "DB2INST1"."AUCTION" ) AS T WHERE EXISTS ( SELECT 1 FROM "DIFFSCHEMA1"."DIFF_AUCTION" AS D WHERE ( T."ITEMID" = D."ITEMID" OR T."ITEMID" IS NULL AND D."ITEMID" IS NULL ) AND D."DIFF " LIKE 'U%' ) FOR READ ONLY ;
2014-05-17-17.13.50.321421 ASN4019I "AsnTRep" : "ASNTA" : "Initial" : The program applied the rows from the difference table to the target table as follows: "1" rows were inserted, "4" rows were updated, and "0" rows were deleted.
----------------------------------------------------------------------
5) Now lets check the data in target to see if it really worked.
db2inst1@murali:~$ db2 connect to testb
Database Connection Information
Database server = DB2/LINUXX8664 9.7.9
SQL authorization ID = DB2INST1
Local database alias = TESTB
db2inst1@murali:~$ db2 "select * from auction"
ITEMID CATEGORY PRICE
---------- ---------- -----------
A0001 HOME 54
A0002 GENERAL 17
A0003 SPARES 120
A0006 GENERAL 40
A0004 GENERAL 32
5 record(s) selected.
NOTE:
From my experience when we need to synchronize big tables we need to specify the conditions on which asntdiff and asntrep should run, else it might take a lot of time to find the differences between source and target, also sometimes it also fails. Its always important to understand the business logic and use these utilities with utmost care to make sure we moving in right direction.
