Saturday, May 17, 2014

Using ASNTDIFF and ASNTREP utilities to sync the data between source and target tables in DB2 Q replication

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.

Adding a new column to replicated table in DB2 Q Replication

This tutorial will help you understand on how to add a new column to table which is already replicated, This tutorial applies for Q Replication with bidirectional setup ( active - active solution ) that i was using in my earlier posts. Description of table is below to which we shall add one new column, name it as ADDRESS VARCHAR(10)

db2inst1@murali:~$ db2 describe table dba

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
NAME                            SYSIBM    VARCHAR                     10     0 Yes
ID                              SYSIBM    INTEGER                      4     0 No  

  2 record(s) selected.


Basic steps are like this

1) Check the subscription of the table on source and target and see if it active before we add a new column
2) Add the column with normal alter table add column command to the source table
3) Pass the signal to replication so it can understand that we have added a new column to table
4) Check on the target side if the column is added or not

1)

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 subname,state from asnta.ibmqrep_subs where source_name='DBA'"

SUBNAME                                                                                                                              STATE
------------------------------------------------------------------------------------------------------------------------------------ -----
DBA0001                                                                                                                              A  

  1 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 subname,state from asntb.ibmqrep_subs where source_name='DBA'"

SUBNAME                                                                                                                              STATE
------------------------------------------------------------------------------------------------------------------------------------ -----
DBA0002                                                                                                                              A  

  1 record(s) selected.


2)

db2inst1@murali:~$ db2 "alter table dba add column address varchar(10)"
DB20000I  The SQL command completed successfully.

3)

db2inst1@murali:~$ db2 "insert into ASNTA.IBMQREP_SIGNAL(SIGNAL_TIME,SIGNAL_TYPE,SIGNAL_SUBTYPE,SIGNAL_INPUT_IN,SIGNAL_STATE) values ( CURRENT TIMESTAMP,'CMD','ADDCOL','DBA0001;ADDRESS','P' )"
DB20000I  The SQL command completed successfully.

4)

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 describe table dba

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
NAME                            SYSIBM    VARCHAR                     10     0 Yes
ID                              SYSIBM    INTEGER                      4     0 No  
ADDRESS                         SYSIBM    VARCHAR                     10     0 Yes

  3 record(s) selected.

All the commands used in this post are highlighted in ITALIC

Wednesday, May 14, 2014

Learning DB2 Basics

Its always good to learn basics so let me share you some best books and tutorials.

1) Learning db2 visually with examples 2nd edition 2008 - this is my favorite book for DB2 learners . I got a soft copy luckily somewhere in internet, Often i read it on GS3 when i get bored ..



Link to download : https://www.mediafire.com/?cuy2zr07roj3et1  ( please don't share any alternative links as i want to see the original downloads count )

2) Want to learn db2 in video tutorials, luckily we have one for that as well. We can even download offline copy to watch it. You have to signup using Google or some... Its has two parts of tutorials. Once you read everything you can also take test to evaluate your learning's.

1) DB2 Essential Training I  (DB101EN)
2) DB2 Essential Training II (DB102EN)





Tuesday, May 13, 2014

Automatically reroute your client requests in DB2 HADR in the event of failure

You can use automatic client reroute (ACR) with high availability disaster recovery (HADR) to transfer client application requests from a failed database server to a standby database server.

Today i will show how to implement it practically and see how it works. I'm using the same setup that i have did in my earlier post on how to setup DB2 HADR, so refer to that and setup in your environment.


Basic steps are like these.

1) Setup HADR pair and see that they are in peer mode.
2) UPDATE ALTERNATIVE SERVER configuration on the primary and standby database.
3) Catalog the primary database at the standby node/instance
4) takeover the hadr on standby database
5) run the query to test it and see if we are connected to standby from client


1) Setup HADR and check if HADR pair is in peer state or not

db2pd -db char -hadr


2)
on primary:
db2 update alternate server for database char using hostname sairam port 65000

on standby:
db2 update alternate server for database char using hostname sairam port 64000

3)
on standby:

db2 catalog tcpip node primeno remote sairam server 64000
db2 catalog database char as apple at node primeno

from standby instance connect to primary database which is cataloged as local database

db2inst2@sairam:~$ db2 connect to apple user db2inst1
Enter current password for db2inst1:

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.9
 SQL authorization ID   = DB2INST1
 Local database alias   = APPLE

db2inst2@sairam:~$ db2 "select * from  tab1"

COL1    
-----------
          1
          2
          3

  3 record(s) selected.

Note : Dont close this session as we need to check the same query from same session as described in step 5

4)

on standy: use a different session to give this command

Issue a takeover on the standby database to switch the HADR role

db2inst2@sairam:~$ db2 takeover hadr on database char
DB20000I  The TAKEOVER HADR ON DATABASE command completed successfully.

5)

from the same session from step 3 - Issue the same select query again, It will display us the warning message as connection to old database has been lost and trying to connect to new primary database. As show below If you give select query with out ur isolation level it throws error because we are connected to standby database and we need to maintain UR isolation level.

db2inst2@sairam:~$ db2 "select * from  tab1"
SQL30108N  A connection failed but has been re-established. Special register
settings might have been replayed. Host name or IP address of the new
connection: "sairam". Service name or port number of the new connection:
"64000".  Reason code: "1".  SQLSTATE=08506

db2inst2@sairam:~$ db2 "select * from  tab1"

COL1    
-----------
SQL1773N  The statement or command requires functionality that is not
supported on a read-enabled HADR standby database. Reason code = "1".

db2inst2@sairam:~$ db2 "select * from  tab1 with ur"

COL1    
-----------
          1
          2
          3

  3 record(s) selected.

All commands that i used in this post are highlighted in ITALIC 

Friday, May 9, 2014

8 steps to setup DB2 HADR solution

High Availability disaster Recovery solution HADR in DB2 is one of robust ways of maintaining two db2 databases in SYNC for high availability solutions.

We can use primary database for read,write operations and standby database for read operations or completely for disaster solution in case if primary fails.
Setting up HADR in DB2 is one of the easiest one that i ever worked, it took me only half hour to setup and test the solution.

Basic step are like these.
create a database on one machine/instance
set HADR DB parameters on primary database
backup database on primary
restore the image in standby machine/instance
set HADR DB parameters on standby database
start hadr on standby first then on primary database
Validate the HADR setup

Here is how my setup look like




1) db2 create database samsung

2) db2 update db cfg for samsung using logarchmeth1 DISK:/home/db2inst1/logs

3) Setting up HADR cfg parameters on primary database

update db cfg for samsung using HADR_LOCAL_HOST sairam
update db cfg for samsung using HADR_LOCAL_SVC 64000
update db cfg for samsung using HADR_REMOTE_HOST sairam
update db cfg for samsung using HADR_REMOTE_SVC 65000
update db cfg for samsung using HADR_REMOTE_INST db2inst2
update db cfg for samsung using LOGINDEXBUILD ON

4) Take an offline backup to be used for setting HADR, move the backup image (from the primary machine/instance) to the standby machine/instance

5) restore the database on standby instance

db2 restore db samsung taken at 20140509113342 on /home/db2inst2/ into apple

6) Setting up HADR cfg parameters on standby database

update db cfg for apple using HADR_LOCAL_HOST sairam
update db cfg for apple using HADR_LOCAL_SVC 65000
update db cfg for apple using HADR_REMOTE_HOST sairam
update db cfg for apple using HADR_REMOTE_SVC 64000
update db cfg for apple using HADR_REMOTE_INST db2inst1

7) Starting up HADR on the standby server

db2 start hadr on database apple as standby

8) Starting up HADR on the primary server

db2 start hadr on database samsung as primary

Finally validating the HADR setup is important.

create a table and populate some 5 rows at primary site, switch the between the roles and check if the table available in standby database.