Installing DB2 fixpack

Install DB2 Fix packs – Below steps are intended for Linux, Unix operating systems.

1) Perform the necessary tasks before installing a fix pack.
2) Choose a fix pack installation method and install the fix pack.
3) Perform the necessary tasks after installing the fix pack.
4) Apply the appropriate DB2 database product license.

1) Perform the necessary tasks before installing a fix pack

a) If you already have DB2 database product installed, the space required to install the fix pack is slightly greater than the space consumed by the existing DB2 database products. This space is only required temporarily during the fix pack installation process.

To determine the space used by the existing DB2 database products, perform the command:

du -h -s DB2DIR

where DB2DIR represents the location where the DB2 copy is installed.

b) Download the fix pack

c) Uncompress the fixpacks, use the below command

gunzip -c filename.tar.gz | tar -xvf - 

d) Before installing a fix pack, if there are DB2 database products installed in the selected installation path, you must stop all of the DB2 processes. If you have multiple DB2 copies, stop only the DB2 processes that are associated with the copy that you are updating.
Determine which instances are associated with the DB2 copy.

Issue the command: DB2DIR/instance/db2ilist

where DB2DIR represents the location where the DB2 copy is installed.

su - iname
db2 force application all
db2 terminate
db2stop
exit

If the DB2 Administration Server (DAS) belongs to the DB2 copy that you are updating, stop the DAS:
su - aname
db2admin stop
exit

where aname represents the DAS owner name.

e) Optional: On AIX, run slibclean to unload unused shared libraries from memory before installation:

/usr/sbin/slibclean

f) Disable the fault monitor processes. To stop the Fault Monitor Daemon, issue the command:

DB2DIR/bin/db2fm -i iname -D

where DB2DIR is the location where the DB2 copy is installed and iname represents the instance owner name. The command must be performed once for each instance in the DB2 copy.

g) Ensure all DB2 interprocess communications are cleaned for the instance to be updated. As the instance owner, run the following command at each physical partition:

$HOME/sqllib/bin/ipclean

2) Choose a fix pack installation method and install the fix pack.

There are 2 ways that you use the fixpack and installation methods that match the purpose.

1) Follow these instructions to install new DB2 database products at a particular fix pack level. The db2setup command is used to perform the installation.

Change to the directory that contains the fix pack image.

Launch the installation by issuing the command: ./db2setup

2) Follow these instructions if a DB2 database product is already installed and you want to apply a new fix pack level. The installFixPack command is used to install the fix pack.

You have two choices when it comes to fix pack installation; using a universal fix pack (all products) or a product-specific fix pack.

Why would I use the universal fix pack?
If I want to upgrade multiple products in place.
If I do not remember what I have installed to be able to install the fix pack in a new location without losing my current set of features or products.

Why would I use the product-specific fix pack?
If I have a single product and want to save the time to download the fix pack.
If system downtime is costly.
If I need to test the new fix pack without impacting my production databases.
If I need the ability to go back to the previous version quickly (system downtime in case of failure).
If I already have a fix pack installed and need to add new features or products to the fix pack.
Note: Many of these advantages can be achieved by installing from your original media to a new location and using the universal fix pack to upgrade features or products, however, there is a cost of extra installation time.

Launch the installation by issuing the installFixPack command. For example,

./installFixPack -b DB2DIR

where DB2DIR is the location of the DB2 database products that you want to update.

Note: I had seen below error in Linux environment.

Stopping DB2 Fault Monitor :.......Success
ERROR: The installFixPack command detected some DB2 libraries are still loaded
in memory and some applications might still be running in the current
installation copy.  All applications must be stopped. See the fix pack readme
for pre-installation instructions, and re-run the installFixPack command.
Alternatively, to override automatic checking, you can re-issue the
installFixPack command with the '-f db2lib' parameter. Note: If you re-issue
the installFixPack command with the '-f db2lib' parameter, after the DB2
instances are updated, some applications might not work properly and might need
to be restarted to function properly against the updated DB2 instance.

Preparing the system :.......Failure
Change to the directory that contains the fix pack image.

If you get error as above use the below command, and complete the install.

./installFixPack -b DB2DIR –f db2lib

3) Perform the necessary tasks after installing the fix pack.

a) For each instance, issue the command:

DB2DIR/instance/db2iupdt iname

where iname represents the instance name and DB2DIR represents the location where the DB2 copy is installed.

b) If the DB2 Administration Server (DAS) belongs to the DB2 copy where you installed the fix pack, issue the command:

DB2DIR/instance/dasupdt

where DB2DIR is the location where the DB2 copy is installed. If this DB2 copy is now running at a more recent fix pack level than all of the other DB2 copies, consider updating the DAS to belong to this DB2 copy.

c) Update the system catalog objects in your databases to support the fix pack.

db2updv97 -d dbname
where dbname represents the name of the database.

Note: Backup your database before running db2updv97. Some system objects might become unusable after moving back to an earlier fix pack, and your database will need to be restored.

d) Restart the instance , DAS admin

su - iname
db2start

su - dasuser
db2admin start

e) As part of a fix pack installation on the server, binding of the database utilities (IMPORT, EXPORT, REORG, the Command Line Processor) and the CLI bind files occurs automatically. However, if you install a fix pack on the client or an error occurs, you can manually bind the database utilities and the CLI bind files.

If you installed the fix pack on DB2 database products that have existing databases, perform the following commands once for each database:

db2 terminate
db2 CONNECT TO dbname user USERID using PASSWORD
db2 BIND path\db2schema.bnd BLOCKING ALL GRANT PUBLIC SQLERROR CONTINUE 
db2 BIND path\@db2ubind.lst BLOCKING ALL GRANT PUBLIC ACTION ADD 
db2 BIND path\@db2cli.lst BLOCKING ALL GRANT PUBLIC ACTION ADD 
db2 terminate

If you installed the fix pack on DB2 database products that have existing databases, rebind the packages by running the REBIND or db2rbind command.

db2rbind dbname -l  fullpath/logfile all 
fullpath refers to a valid path given with a name to log the rebind output

4) Apply the appropriate DB2 database product license.

INSTHOME/sqllib/adm/db2licm -a filename

Where INSTHOME represents the home directory of the instance owner and filename is the full path name and file name for the license file
Check the license information once after applying the license.

db2licm -l

All the commands used in the post are highlighted as Italic

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.