Q capture program stops giving Websphere MQ error 2058 ASN0575E

Problem statement: Websphere MQ error 2058 ASN0575E while issuing the WebSphere MQ command "MQCONN" on object "*"

Solution: The above error is very common error for most of the users when a Queue manager tries to access a queue or when it tries to issue MQCONN call on the queues which are not belonging to it then it gives us an error.

ASNCLP SESSION SET TO Q REPLICATION;
CREATE MQ SCRIPT CONFIG TYPE B
MQSERVER 1 NAME TESTA MQHOST "fedorabox" MQPORT 2416 QMANAGER QMTA QNAME_QUAL ASNTA, 
MQSERVER 2 NAME TESTB MQHOST "fedorabox" MQPORT 2417 QMANAGER QMTB QNAME_QUAL ASNTB;
=======================================================================
In the above setup following are the configurations.

Database 1 : TESTA
Database 2 : TESTB
QManager name for TESTA database is: QMTA
QManager name for TESTB database is: QMTB
Schema that we are using for TESTA database is ASNTA under which control tables, replication queue maps and all replication related objects are created in this schema.
Schema that we are using for TESTB database is ASNTB under which control tables, replication queue maps and all replication related objects are created in this schema.

========================================================================
ASNCLP SESSION SET TO Q REPLICATION;
SET OUTPUT MULTIDIR;
SET RUN SCRIPT NOW STOP ON SQL ERROR ON;
SET BIDI NODE 1 SERVER DBALIAS TESTA ID "DB2INST1" PASSWORD "db2inst1" SCHEMA ASNTA;
SET BIDI NODE 2 SERVER DBALIAS TESTB ID "DB2INST1" PASSWORD "db2inst1" SCHEMA ASNTB;
CREATE CONTROL TABLES FOR NODE 1;
CREATE CONTROL TABLES FOR NODE 2;
CREATE REPLQMAP "TESTA.ASNTA_TO_TESTB.ASNTB" (NODE 1, NODE 2);
CREATE REPLQMAP "TESTB.ASNTB_TO_TESTA.ASNTA" (NODE 2, NODE 1);

Above script defines control tables for NODE1 and NODE2 and replication queue maps for NODE1 and NODE2
========================================================================

1) When i run the first set of commands using asnclp it will give scripts for BIDIRECTIONAL replication between two databases TESTA and TESTB and setup Queue managers QMTA for TESTA, QMTB for TESTB and sets replication schema ASNTA for TESTA and ASNTB for TESTB

2) When i run the second set of commands using asnclp it will give script for setting up control tables and replication queue maps between both the database viceversa

Everything ran successfully Queue managers QMTA and QMTB are up and running and all necessary objects are created. But when i start the Q capture program and when it tries to issue MQCONN call it wasnt able to identify the queues and gave me MQ error 2058, because in the above script i didnt specify queue manager name for NODE1 and NODE2.

SET QMANAGER QMTA FOR NODE 1;
SET QMANAGER QMTB FOR NODE 2;

When i added above two lines to second script that is setting the which qmanager to which node, then all the objects are created with the right Queue managers. Before running this script again i have dropped the schema ASNTA and ASNTB from both sides.

ASNCLP SESSION SET TO Q REPLICATION;
SET OUTPUT MULTIDIR;
SET RUN SCRIPT NOW STOP ON SQL ERROR ON;
SET BIDI NODE 1 SERVER DBALIAS TESTA ID "DB2INST1" PASSWORD "db2inst1" SCHEMA ASNTA;
SET BIDI NODE 2 SERVER DBALIAS TESTB ID "DB2INST1" PASSWORD "db2inst1" SCHEMA ASNTB;
SET QMANAGER QMTA FOR NODE 1;
SET QMANAGER QMTB FOR NODE 2;
CREATE CONTROL TABLES FOR NODE 1;
CREATE CONTROL TABLES FOR NODE 2;
CREATE REPLQMAP "TESTA.ASNTA_TO_TESTB.ASNTB" (NODE 1, NODE 2);
CREATE REPLQMAP "TESTB.ASNTB_TO_TESTA.ASNTA" (NODE 2, NODE 1);

Then i was able to start the capture program and apply program and was able to successfully start the replication. Below link has helped me diagnosing the error
http://www-01.ibm.com/support/docview.wss?uid=swg21166938

DB2 Q Replication setup for an active active solution PART 2

I'm back after my 5 day continuous night shifts.. Its Saturday today fresh day to start with PART 2 to quick setup of DB2 Q Replication for active active solution

We have installed DB2 on Fedora box in PART 1 , now let us install Websphere MQ on top of it.

Installation of Websphere MQ 7.1 :

1) Untar the installation files
tar -xvf WMQ_7.1.0.3_TRIAL_LNX_X86_64_ML.tar.gz

2) we need to accept the license before proceeding for installation of MQ
sudo ./mqlicense.sh -accept

3) Now lets install Websphere MQ using below command
rpm -ivh MQSeries*

4) After MQ installed we need to set the current installation as primary for that we need to run setmqinst command from installation path like below.
sudo ./mqsetinst -i -p /opt/mqm
After it executes we should see this messages like below.

[db2inst1@fedorabox bin]$ sudo ./setmqinst -i -p /opt/mqm/
114 of 114 tasks have been completed successfuly.

'Installation1' (/opt/mqm) set as the Primary Installation.

5) Run command dspmq to see installed version of MQ

[db2inst1@fedorabox bin]$ dspmqver

Name:        WebSphere MQ
Version:     7.1.0.3
Level:       p710-003-130724
BuildType:   IKAP - (Production)
Platform:    WebSphere MQ for Linux (x86-64 platform)
Mode:        64-bit
O/S:         Linux 3.6.10-4.fc18.x86_64
InstName:    Installation1
InstDesc:    
InstPath:    /opt/mqm
DataPath:    /var/mqm
Primary:     Yes
MaxCmdLevel: 711

If this MQ installation is not first time on your machine, you need to run crtmqpkg command to create unique set of packages to install in your system. In order to do this we need to install pax before running crtmqpkg command

sudo apt-get install pax
sudo ./crtmqpkg PROD
after the packages are created a path is displayed where those are created we need to go to that directory and install the available RPM's for MQ .
cd /var/tmp/mq_rpms/PROD/

sudo crtmqpkg test  
sudo rpm -ivh MQSeries*

================= End of installation Websphere MQ ===========================

A prerequisite for using Q Replication is that databases should be enabled for archive logging. use below script that creates a database , setup archive logging, and take an offline backup . see that you create the needed directories that used for archive log location, Put it everything in a file and run it.

create database testa;

create database testb;

update db cfg for testa using LOGARCHMETH1 DISK:/home/db2inst1/archivelogs/testa;

backup database testa to /dev/null;

update db cfg for testb using LOGARCHMETH2 DISK:/home/db2inst1/archivelogs/testb;

backup database testb to /dev/null;

db2 -stvf create.sql -z create.out

Now lets create Queue Manager and it objects. Use below script

ASNCLP SESSION SET TO Q REPLICATION;

CREATE MQ SCRIPT CONFIG TYPE B

MQSERVER 1 NAME TESTA MQHOST "fedorabox" MQPORT 2416 QMANAGER QMTA QNAME_QUAL ASNTA, 

MQSERVER 2 NAME TESTB MQHOST "fedorabox" MQPORT 2417 QMANAGER QMTB QNAME_QUAL ASNTB; 

Change the "fedorabox" hostname to your machine name. 

Important points to understand here:

CREATE MQ SCRIPT command will generate scripts for creating all of the WebSphere MQ objects that are needed for Q Replication. Lets run the script using asnclp command line, Once done we can see 4 files created 2 files ending with .sh is for unix/linux OS , 2 files ending with .bat is for windows OS.

[db2inst1@fedorabox scripts]$ asnclp -f create_mq_objects.asn

[db2inst1@fedorabox scripts]$ ls -lrt

total 40

-rw-rw-r--. 1 db2inst1 db2inst1  240 Apr 12 19:49 create_mq_objects.asn

-rw-rw-r--. 1 db2inst1 db2inst1 5329 Apr 12 19:50 qrepl.testa.mq_windows.bat

-rw-rw-r--. 1 db2inst1 db2inst1 5330 Apr 12 19:50 qrepl.testa.mq_aixlinux.sh

-rw-rw-r--. 1 db2inst1 db2inst1 4671 Apr 12 19:50 qrepl.testb.mq_windows.bat

-rw-rw-r--. 1 db2inst1 db2inst1 4666 Apr 12 19:50 qrepl.testb.mq_aixlinux.sh

-rw-rw-r--. 1 db2inst1 db2inst1  412 Apr 12 19:50 qreplmsg.log

Now switch to mq user and run the 2 files one for testa other for testb.

sh qrepl.testa.mq_aixlinux.sh
sh qrepl.testb.mq_aixlinux.sh

After running that we should be able to see there are 2 Queue managers that should up and running as show below.

[db2inst1@fedorabox mqm]$ dspmq
QMNAME(QMTA)                                              STATUS(Running)
QMNAME(QMTB)                                              STATUS(Running)

Lets create a table named "dba" with columns as below and run on both the databases TESTA and TESTB. 

[db2inst1@fedorabox scripts]$ db2 connect to testa
   Database Connection Information
 Database server        = DB2/LINUXX8664 9.7.6
 SQL authorization ID   = DB2INST1
 Local database alias   = TESTA
[db2inst1@fedorabox scripts]$ db2 "create table dba(id num not null,name varchar(10), topic varchar(10))"
DB20000I  The SQL command completed successfully.
[db2inst1@fedorabox scripts]$ db2 "alter table dba add primary key(id)"
DB20000I  The SQL command completed successfully.

[db2inst1@fedorabox scripts]$ db2 connect to testb
   Database Connection Information
 Database server        = DB2/LINUXX8664 9.7.6
 SQL authorization ID   = DB2INST1
 Local database alias   = TESTB

[db2inst1@fedorabox scripts]$ db2 "create table dba(id num not null,name varchar(10), topic varchar(10))"
DB20000I  The SQL command completed successfully.
[db2inst1@fedorabox scripts]$ db2 "alter table dba add primary key(id)"
DB20000I  The SQL command completed successfully.

Now we use below script to create control tables,replication queue maps and a sample subscription for table DBA.

Important points to understand here:

create control tables statement will create control tables which store information about Q replication and its sources and targets.
create replqmap statement will create replication queue maps which are needed to identify websphere mq queues on both the servers that is source and target. One replication queue map defined from source to target and one for target to source.
create qsub statement will create a queue subscription that maps source table to target table.

ASNCLP SESSION SET TO Q REPLICATION;
SET OUTPUT MULTIDIR;
SET RUN SCRIPT NOW STOP ON SQL ERROR ON;
SET BIDI NODE 1 SERVER DBALIAS TESTA ID "DB2INST1" PASSWORD "db2inst1" SCHEMA ASNTA;
SET BIDI NODE 2 SERVER DBALIAS TESTB ID "DB2INST1" PASSWORD "db2inst1" SCHEMA ASNTB;
SET QMANAGER QMTA FOR NODE 1;
SET QMANAGER QMTB FOR NODE 2;
CREATE CONTROL TABLES FOR NODE 1;
CREATE CONTROL TABLES FOR NODE 2;
CREATE REPLQMAP "TESTA.ASNTA_TO_TESTB.ASNTB" (NODE 1, NODE 2);
CREATE REPLQMAP "TESTB.ASNTB_TO_TESTA.ASNTA" (NODE 2, NODE 1);
SET TABLES(NODE 1 "DBA");
CREATE QSUB SUBTYPE B FROM NODE TESTA.ASNTA SOURCE HAS LOAD PHASE I START AUTOMATICALLY YES TARGET LOAD TYPE 0;

Now run the script using asnclp command line 
asnclp -f replication_setup.asn

This will  create control tables, replication queue map and subscription for table DBA. Once done with above script now lets start the capture and apply programs on both sides.

Issue this commands using db2inst1 ID

nohup asnqcap capture_server=TESTA capture_schema=ASNTA &
nohup asnqcap capture_server=TESTB capture_schema=ASNTB &
nohup asnqapp apply_server=TESTA apply_schema=ASNTA &
nohup asnqapp apply_server=TESTB apply_schema=ASNTB &

Lets replicate the data now, Insert data from TESTA and look if its replicated to TESTB database.

[db2inst1@fedorabox mqm]$ db2 connect to TESTA
   Database Connection Information
 Database server        = DB2/LINUXX8664 9.7.6
 SQL authorization ID   = DB2INST1
 Local database alias   = TESTA

[db2inst1@fedorabox mqm]$ db2 "insert into dba values(1,'Murali','db2')"
DB20000I  The SQL command completed successfully.

[db2inst1@fedorabox mqm]$ db2 "select * from dba"
ID      NAME       TOPIC  
------- ---------- ----------
     1. Murali     db2    
  1 record(s) selected.

[db2inst1@fedorabox mqm]$ db2 connect to testb
   Database Connection Information
 Database server        = DB2/LINUXX8664 9.7.6
 SQL authorization ID   = DB2INST1
 Local database alias   = TESTB

[db2inst1@fedorabox mqm]$ db2 "select * from dba"
ID      NAME       TOPIC  
------- ---------- ----------
     1. Murali     db2    
  1 record(s) selected.

So the data is replicating and we just did it.... :) that's all with this tutorial on DB2 Q Replication.

In my next post i will post the common issues that i have encountered when did my first setup of Q Replication. People post your comments here ..... and lets start exploring more on Q Replication.

DB2 Q Replication setup for an active active solution PART 1

How to setup DB2 Q Replication

We are about a setup DB2 Q Replication that demonstrates active active solution. Active - Active  simply means changes happening at source should go to target and at same time changes happening at target should go to source.

High level steps:

1) Install DB2 9.7 on Fedora 18 OS - I'm Fedora OS as its free edition supported by REDHAT community.
2) Install Websphere MQ 7.1 for messaging system between 2 DB2 databases.
3) Create MQ objects needed for active active Q replication.
4) setup a replication for a sample table to demonstrate active active replication.

Below is the setup of two databases named TESTA and  TESTB for demonstration of active - active Q replication between two db2 databases. Below is how the setup look like.

Links to download the needed software's

1) Fedora 18 or above ( Free copy ) from here :- http://fedoraproject.org/en_GB/get-fedora-all

2) DB2 9.7 AESE ( trail 90 days ) from here https://www14.software.ibm.com/webapp/iwm/web/preLogin.do?lang=en_us&source=swg-dm-db297trial&S_CMP=web_dw_rt_swd

3) Websphere 7.1 (trail 90 days ) from here :- www.ibm.com/webspheremq/downloads

Installation procedure of DB2 9.7 on Fedora OS

After installing fedora and Vmware tools you will be able to copy the downloaded trails files to the host machine to proceed with installation. An advice here when you try to copy to host machine, Use the command with sudo access ( systemctl mask tmp.mount ), This will turn off any filesystem from being mounted on /tmp, forcing it to be on / instead. To turn it on again, run ( systemctl unmask tmp.mount )

1) Its always a best practice to do a prerequisite check before installing DB2

[db2inst1@fedorabox server]$ cd server/
[db2inst1@fedorabox server]$ sudo sh db2prereqcheck
[sudo] password for db2inst1:
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.

Above you can see that there is a warning that libpam.so is missing. Now lets install it from internet. run sudo yum install pam-devel.i686   in your terminal

Ok lets run the prereqcheck again and see.

[db2inst1@fedorabox server]$ sudo sh db2prereqcheck

[sudo] password for db2inst1: 

WARNING:

DBT3534W  The db2prereqcheck utility determined that ASLR is set to ON and that this could cause issues with some tools.

above warning can be ignored.

2) now lets install the db2 using db2setup

[db2inst1@fedorabox server]$ sudo sh db2setup

we get a GUI prompt like ( shown below ) which will allow us to use various options for installation, Just go ahead proceed with all steps and at the end when you click finished , it will do the actual work of installing DB2 with the selected options 

When all the steps are completed lets set the db2profile in user home directory add below 3 lines to profile file of db2inst1 ( run vi .bash_profile )

if [ -f /home/db2inst1/sqllib/db2profile ]; then

        sh /home/db2inst1/sqllib/db2profile

fi

execute the profile file to take changes into effect 

run       . .bash_profile 

3) Now lets start the instance.

[db2inst1@fedorabox ~]$ db2start

04/07/2014 15:17:17     0   0   SQL1063N  DB2START processing was successful.

SQL1063N  DB2START processing was successful.

That's it with DB2 Installation on Fedora OS and we can proceed to step 2 which will be preceded in the next post as PART 2

DB2 Q Replication it made me stuck for more than a month

I'm back with fresh concepts and ideas in my mind, those were the things that made me stuck with issues for at least more than a month and was not able to blog anything until i learnt something practical out of it myself. So what is that interesting topic in DB2?

DB2 Q Replication and Event Publishing.

Since the time i started working on replication it made me so interested that i wanted to implement and see how it works.

DB2 Q Replication captures the changed data from source and send committed transactions as messages using a messaging system called IBM WebSphere Message Queues to the target.

Changes that happen at source are captured by Capture program by reading DB2 recovery logs and put them as messages on the WebSphere message queues where the apply program resides at target reads messages from queues and write back changes to respective targets. below figure explains it.

I'm my next post i will give the implementation of DB2 Q replication between two DB2 databases.

How to create federation between two DB2 databases

The other day developer were asking if they access production table in their test databases. Here when we suggested them that we have Federation support across multiple RDMS vendors.

So lets get started quickly on how to create federation between two DB2 databases

It involves 5 steps Enable database to support federation, Create wrapper, server, usermapping, nicknames . First we need to check whether Federation support is switched on for our database, If you see that FEDERATION is set to off go head and update to YES and recycle the instance

db2inst1@test-machine:~$ db2 get dbm cfg | grep -i FEDERATED
 Federated Database System Support           (FEDERATED) = NO

db2inst1@test-machine:~$ db2 update dbm cfg using FEDERATED YES
DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed 
successfully.

db2inst1@test-machine:~$ db2stop
19/02/2014 17:41:06     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.

db2inst1@test-machine:~$ db2start
02/19/2014 17:41:10     0   0   SQL1063N  DB2START processing was successful.

SQL1063N  DB2START processing was successful.

db2inst1@test-machine:~$ db2 get dbm cfg | grep -i FEDERATED
 Federated Database System Support           (FEDERATED) = YES

Wrapper : The CREATE WRAPPER statement registers a wrapper with a federated server. A wrapper is a mechanism by which a federated server can interact with certain types of data sources,The default wrapper name for the DB2 family data sources is DRDA.

Syntax : CREATE WRAPPER <wrappername> 

Server : The CREATE SERVER statement defines a data source to a federated database.

Syntax : CREATE SERVER <servername> TYPE <datasourcename> VERSION <serverversion> WRAPPER <wrappername> AUTHORIZATION "<remoteauthid>" PASSWORD "<password>" OPTIONS (DBNAME 'dbname') 

Usermapping : The CREATE USER MAPPING statement defines a mapping between an authorization ID that uses a federated database and the authorization ID and password to use at a specified data source.

Syntax : CREATE USER MAPPING for <authname> SERVER <servername> OPTIONS (REMOTE_AUTHID 'username',REMOTE_PASSWORD 'password')

Nicknames : The CREATE NICKNAME statement defines a nickname for a data source object. we use this nick name to fetch records from the federated database.

Syntax: : CREATE NICKNAME <nickname> for <servername>.<schenaname.tablename>

Now let me take a scenario and explain it using two db2 databases. My aim is to access table in TESTA database from the database MURALI 


murali@test-machine:~$ db2 connect to murali

   Database Connection Information

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

create wrapper drda
DB20000I  The SQL command completed successfully.

create server fedserver TYPE DB2/UDB VERSION 9.7 WRAPPER drda  AUTHORIZATION "murali" password "murali" OPTIONS(DBNAME 'TESTA')
DB20000I  The SQL command completed successfully.

create user mapping for murali SERVER fedserver OPTIONS(REMOTE_AUTHID 'db2inst1',REMOTE_PASSWORD 'db2inst1')
DB20000I  The SQL command completed successfully.

create nickname mytable for fedserver.db2inst1.tab1
DB20000I  The SQL command completed successfully.

murali@test-machine:~$ db2 connect to murali

   Database Connection Information

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

murali@test-machine:~$ db2 "select * from mytable"

ID      NAME      
------- ----------
     1. murali    
     2. sharath   
     3. balaji    
     4. praks     

  4 record(s) selected.

Post your comments and doubts about this topic here.