Error SQL10013N SQLSTATE=42724 create wrapper for federation support

When we need to setup federation we need to first create a wrapper which will identify data source of the federated server.
We get this error SQL10013N as shown below, When we don't have Informix data source support installed in our server.

db2inst1@test-machine:~$ db2 "create wrapper drda"
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL10013N  The specified library "libdb2drda.so" could not be loaded.  
SQLSTATE=42724

Below commands list the features that are installed on the server, we can seen there is no Informix Data Source Support Installed on the server

db2inst1@test-machine:~$ db2ls -q -b /opt/ibm/db2/V9.7

Install Path : /opt/ibm/db2/V9.7

Feature Response File ID             Level   Fix Pack   Feature Description
---------------------------------------------------------------------------------------------------------------------
BASE_CLIENT                         9.7.0.6          6   Base client support
JAVA_SUPPORT                        9.7.0.6          6   Java support
SQL_PROCEDURES                      9.7.0.6          6   SQL procedures
BASE_DB2_ENGINE                     9.7.0.6          6   Base server support
JDK                                 9.7.0.6          6   IBM Software Development Kit (SDK) for Java(TM)
COMMUNICATION_SUPPORT_TCPIP         9.7.0.6          6   Communication support - TCP/IP
REPL_CLIENT                         9.7.0.6          6   Replication tools
DB2_DATA_SOURCE_SUPPORT             9.7.0.6          6   DB2 data source support
LDAP_EXPLOITATION                   9.7.0.6          6   DB2 LDAP support
INSTANCE_SETUP_SUPPORT              9.7.0.6          6   DB2 Instance Setup wizard
SPATIAL_EXTENDER_CLIENT_SUPPORT     9.7.0.6          6   Spatial Extender client
APPLICATION_DEVELOPMENT_TOOLS       9.7.0.6          6   Base application development tools

As we identified the problem now we shall install the Informix Data source support, i'm using response file installation for this as its a single component installation. Just copy below things and change the parameter "FILE" according to your already installed location of db2 server

PROD                        = ADVANCED_ENTERPRISE_SERVER_EDITION
FILE                        = /opt/ibm/db2/V9.7_01
LIC_AGREEMENT               = ACCEPT
INSTALL_TYPE                = CUSTOM  
COMP                        = INFORMIX_DATA_SOURCE_SUPPORT

I will save above 5 lines in /home/db2inst1/resp

Now i will install the single component informix_data_source_support using below command

db2inst1@test-machine:~/server$ sudo ./db2setup -r /home/db2inst1/resp 
WARNING:
DBT3534W  The db2prereqcheck utility determined that ASLR is set to ON and that this could cause issues with some tools.
WARNING:
   The 32-bit library file libpam.so is not found on the system.
DBI1191I  db2setup is installing and configuring DB2 according to the
      response file provided. Please wait.

A minor error occurred while installing "DB2 Advanced Enterprise Server
Edition " on this computer. Some features may not function correctly.
For more information see the DB2 installation log at "/tmp/db2setup.log".

Once done i have connected to database and was able to create wrapper successfully

db2inst1@test-machine:/opt/ibm/db2/V9.7_01/instance$ db2 connect to attempt

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.6
 SQL authorization ID   = DB2INST1
 Local database alias   = ATTEMPT

db2inst1@test-machine:/opt/ibm/db2/V9.7_01/instance$ db2 create wrapper drda
DB20000I  The SQL command completed successfully.

Changing the data type of a column in replicated table in DB2 Q Replication

db2inst1@sairam:~$ db2 describe table task

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
TASK_ID                         SYSIBM    INTEGER                      4     0 No    
TASK_INFO                       SYSIBM    VARCHAR                     10     0 Yes   
SALARY                          SYSIBM    VARCHAR                     10     0 Yes   

  3 record(s) selected.

We want to change SALARY column datatype from varchar(10) to char(20) 

Steps to change the data type:

1) Stop the subscription by passing capstop signal
2) stop the data capture changes to none on both databases
3) alter the column with the new data type on both sides
4) start the data capture changes to yes on both databaes
5) Start the subscription by passing capstart signal

1) db2inst1@sairam:~$ db2 connect to testb

   Database Connection Information

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

db2inst1@sairam:~$ db2 "insert into ASNTB.IBMQREP_SIGNAL (SIGNAL_TIME,SIGNAL_TYPE,SIGNAL_SUBTYPE,SIGNAL_INPUT_IN) values (CURRENT TIMESTAMP,'CMD','CAPSTOP','TASK0002')"
DB20000I  The SQL command completed successfully.

2) db2inst1@sairam:~$ db2 alter table task data capture none      - ( on both databases )
DB20000I  The SQL command completed successfully.

3) db2inst1@sairam:~$ db2 "alter table task alter column task_info set data type char(20)"     - ( on both databases )
DB20000I  The SQL command completed successfully.

4) db2inst1@sairam:~$ db2 alter table task data capture changes
DB20000I  The SQL command completed successfully.

5) db2inst1@sairam:~$ db2 "insert into ASNTA.IBMQREP_SIGNAL (SIGNAL_TIME,SIGNAL_TYPE,SIGNAL_SUBTYPE,SIGNAL_INPUT_IN) values (CURRENT TIMESTAMP,'CMD','CAPSTART','TASK0001')"   - ( on one side )
DB20000I  The SQL command completed successfully.

Resolving conflicts due to particular sqlstate in DB2 Q Replication

I had a situation when i tried to insert a row in source which was detected as conflict at target so Q Apply program stopped processing, so now if we want that transaction to be ignored we need to tell q apply program to ignore it .

2014-06-08-12.47.57.547884 ASN0552E  "Q Apply" : "ASNTA" : "BR00000AG012" : The program encountered an SQL error. The server name is "". The SQL request is "EXEC". The table name is "run SQL". The SQLCODE is "-668". The SQLSTATE is "57016". The SQLERRMC is "". The SQLERRP is "SQLDTBLR".
2014-06-08-12.52.03.789464 <brwzMain> ASN7524E  "Q Apply" : "ASNTA" : "BR00000" : The Q Apply program encountered an error or conflict for Q subscription  "STAFF0002" (receive queue  "ASNTB.TESTB_TO_ASNTA.TESTA.DATA", replication queue map "TESTB.ASNTB_TO_TESTA.ASNTA"). It stopped reading from the receive queue. The error occurred while applying a row from the transaction corresponding to the LSN "0000:0000:0000:04d6:82d3".

Below are steps the pass to ibmqrep_targets to ignore that transaction for particular sql state.

db2inst1@sairam:~$ db2 connect to testa

   Database Connection Information

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

db2inst1@sairam:~$ db2 "update asnta.ibmqrep_targets set oksqlstates='57016' where subname='STAFF0002'"
DB20000I  The SQL command completed successfully.

Then it had successfully ignored that transaction alone and q apply started processing the Receive queue.

Resetting Q subscriptions that are in inconsistent states in DB2 Q Replication

Q subscriptions are considered to have inconsistent states when the state (active, inactive, new) is different between the source and target servers. In such cases we can't activate the subscriptions without bringing them into same state(inactive, new). I was able to propagate a situation where there is a table "STAFF" for which the state between source and targets was different. 

Below are the subnames and their state from both databases we can observe the state as G for subname STAFF0001 in TESTA database, where as state as I for subname STAFF0002 in TESTB database 

db2inst1@sairam:~$ db2 connect to testa

   Database Connection Information

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

db2inst1@sairam:~$ db2 "select subname,state from asnta.ibmqrep_subs"

SUBNAME                                                                                                                              STATE
------------------------------------------------------------------------------------------------------------------------------------ -----
DBA0001                                                                                                                              A    
STAFF0001                                                                                                                            G    

  2 record(s) selected.

db2inst1@sairam:~$ db2 connect to testb

   Database Connection Information

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

db2inst1@sairam:~$ db2 "select subname,state from asntb.ibmqrep_subs"

SUBNAME                                                                                                                              STATE
------------------------------------------------------------------------------------------------------------------------------------ -----
DBA0002                                                                                                                              A    
STAFF0002                                                                                                                            I    

  2 record(s) selected.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Steps to reset the incosistent states in Q Replication

1) Stop capture and apply processes this is important before you update the state of subs and targets

2) UPDATE ASNTA.IBMQREP_SUBS SET STATE='I', STATE_TRANSITION=NULL WHERE SUBNAME='STAFF0001';
UPDATE ASNTA.IBMQREP_TARGETS SET STATE='I' WHERE SUBNAME='STAFF0002';

3) Start the capture and apply process now. We have state as INACTIVE in both sides so lets activate the subscription in the next step

4) INSERT INTO ASNTA.IBMQREP_SIGNAL (SIGNAL_TYPE, SIGNAL_SUBTYPE, SIGNAL_INPUT_IN) VALUES ('CMD','CAPSTART','STAFF0001');
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

db2inst1@sairam:~$ db2 connect to testb

   Database Connection Information

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

db2inst1@sairam:~$ db2 "select subname,state from asntb.ibmqrep_subs"

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

  2 record(s) selected.

db2inst1@sairam:~$ db2 connect to testa

   Database Connection Information

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

db2inst1@sairam:~$ db2 "select subname,state from asnta.ibmqrep_subs"

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

  2 record(s) selected.

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.