DB2 v9.1, DB2 v9.5 and DB2 v9.7 Learning

Part 6 : Configuring DB2 9.7 HADR between Primary and Standby

Part 8 : Performing some DB2 9.7 HADR failover and failback test

Part 9 : Converting DB2 9.7 HADR no-read-access standby to DB2 9.7 HADR read-only standby database

Part 10 : Configuring DB2 9.7 Automatic Client re-route (ACR)

Below are some of my reference which i used for writing this artical. You may also look at my twitter account for more useful DB2 links.

My twitter references

http://twitter.com/gilroygonsalves

-- Cleaning up failed DB2 HADR with TSA configuration

Option 1 => db2haicu -delete
Option 2 =>
                   a) On any node, login as "root" user
                   b) Drop the domain by force
                         rmrpdomain -f 
                   c) Unset the DBM CFG parameter CLUSTER_MGR

1) HADR with DB2 Express 9.5 and DB2 Control Center

2) HADR Wiki (Best Practice)

3) HADR using db2haicu in DB2 9.5

4) Configure HADR using IBM Data Studio

5) HADR Simulator

6) HA database environment using WebSphere Middleware

7) Improving HA in WebSphere Commerce using DB2 HADR

8) Implement DB2 HADR in a TSA domain

9) DB2 HADR setup with TSA using db2haicu and Virtual IP

10) Configure DB2 Universal Database for UNIX to use OpenSSH

11) Redbook - HADR option for DB2 on LUW (pg 259)

12) Password less SSH configuration

12) IBM TSA for DB2 HA (Blog)

13) Enable database high availability using DB2 HADR and Tivoli SA MP in an SAP environment

14) IBM DB2 High Availability Solution: IBM Tivoli System Automation for Multiplatforms (03/2008)

15) IBM DB2 High Availability Solution: IBM Tivoli System Automation for Multiplatforms

16) DB2 HADR - Case Study of Implementation

17) Build a highly available application platform for J2EE Part 5: Set up DB2 for HA using TSA

18) HA Configuration using IBM TSA with DB2 9.5 FP3 on IBM AIX v5.3

19) Integrating TSAMP with DB2 HADR v9.5

20) Restoring DB in a TSAMP automated DB2 HADR environment

21) DB2 HA configuration using TSA command - Page 113 - 118, Page 137 - 148

22) HA DB2 (Partitioned Database) using Tivoli System Automation

23) Introduction to Tivoli System Automation

24) DB2 Integrated Cluster Environment Deployment Guide Page 359 - 370

25) Clearing out IBM.RecoveryRM.log file

26) Startup/Shutdown Procedure for DB2 9.5/9.7 HADR in TSAMP environment for maintenenace reason

27) DB2 HADR Performance Issue Monitoring

28) Setting up TSAMP cluster for maintenance, including node reboot

29) db2stop but no failover in DB2 HADR in TSAMP environment

30) No failover after db2_kill issued in DB2 HADR in TSAMP environment

31) DB2 HADR resource group remains locked after successful takeover

32) SuspendedPropagated fro HADR group in TSAMP environment

33) ExcludedList for HADR resource group in TSAMP environment

34) How to stop RSCT (TSAMP) from rebooting a node

35) Nominal State (Desired State) vs Operational State

36) The "resetrsrc" command - A brief how to guide

37) Service IP shows "Failed Offline" state when failover attempted

38) "2612-023" error code when attempting "resetrsrc" on "Failed Offline" standby resource

39) Using DB2 HADR with TSAMP

DB2 9.7 HADR with TSA - Part10 - Configuring DB2 9.7 Automatic Client re-route (ACR)

2011-04-10T23:10:00.000+01:00

In this artical i will provide reference to the document which describe the DB2 9.7 Automatic Client Reroute (ACR) configuration and Limitation

Part 0 : DB2 9.7 HADR with TSA Part 00 - Introduction

Part 1 : Installing and configuring VMWare environment

Part 2 : Installing IBM tools for querying and monitoring purpose

Part 6 : Configuring DB2 9.7 HADR between Primary and Standby

Part 8 : Performing some DB2 9.7 HADR failover and failback test

Part 9 : Converting DB2 9.7 HADR no-read-access standby to DB2 9.7 HADR read-only standby database

A) Automatic Client Reroute (ACR) Configuration

http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.ha.doc/doc/c0011976.html

B) Automatic Client Reroute (ACR) Limitation

http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.ha.doc/doc/c0011977.html

DB2 9.7 HADR with TSA - Part 09 - Converting DB2 9.7 HADR no-read-access standby to DB2 9.7 HADR read-only standby database

2011-04-10T22:54:00.000+01:00

In this artical i will provide reference to document that describe the DB2 9.7 Read only Standby HADR configuration.

Part 0 : DB2 9.7 HADR with TSA Part 00 - Introduction

Part 1 : Installing and configuring VMWare environment

Part 2 : Installing IBM tools for querying and monitoring purpose

Part 6 : Configuring DB2 9.7 HADR between Primary and Standby

Part 8 : Performing some DB2 9.7 HADR failover and failback test

Complete step by step implementation is provided at following link to the topic

Enabling continuous access to read on standby databases using Virtual IP addresses

DB2 9.7 HADR with TSA - Part 08 - Performing some DB2 9.7 HADR failover and failback test

2011-04-10T22:28:00.000+01:00

In this artical i will provide failover and failback command for DB2 HADR with TSA and provide DB2 HADR test cases. I will also provide HADR step that i followed for testing.

Part 0 : DB2 9.7 HADR with TSA Part 00 - Introduction

Part 1 : Installing and configuring VMWare environment

Part 2 : Installing IBM tools for querying and monitoring purpose

Part 6 : Configuring DB2 9.7 HADR between Primary and Standby

Part 1 : Installing and configuring VMWare environment

A) Normal Operation

When DB2 HADR is configured under TSA clustering, under normal operation below is status of TSA resources and resource group

lssam -top

B) Controlled Failover

1. Current Primary Node => mumbai
2. Current Standby Node => london
3. Node on which failover command is executed => mumbai

Note:
For failover operation, the TSA command must be executed as "root" user on the existing primary node.

C) Controlled Failback

1. Current Primary Node => london
2. Current Standby Node => mumbai
3. Node on which failover command is executed => london

Note:
For failover operation, the TSA command must be executed as "root" user on the existing primary node.

D) Primary Instance Failure

In this case, TSA will try to start the Primary Instance Automatically.

E) Stanby Instance Failure

In this case, TSA will try to start the Standby Instance Automatically.

F) Primary Node failure

In this test scenario, you need a third node which behave as a Tie-breaker when the communication between each of the HADR node is lost. i.e. when the communication link between HADR node is lost, then the Network Tie-breaker node is used to decide which node will be the owning the cluster resource and will reboot the remaining node.

In order to test this scenario,
1) Create the TSA domain with network quoum which refer to the IP address of third non-HADR node.
2) Then bring down the eth0 card of primary node.
3) Bringing the eth0 card on primary node will force hard boot of Primary Node and TSA will perform a force takeover of HADR on Standby node
4) Following the Primary node restart the DB take the new role as STANDBY node.

G) Stopping TSA monitoring for Database

This will stop the TSA monitoring of the HADR Databases. But, the DB2 HADR is not terminated by this operation. DB2 HADR configuration continue to work as normal. Only, automatic failover of the DB2 HADR is disabled.

H) Starting TSA Monitoring for Database

This operation will start the TSA monitoring of the DB2 HADR database and provide operation of automatic failover following the primay node failure.

I) Standby instance TSA Resource Group failure

Primary Enters into disconnected state

Standby Instance Resource Group restored

At this stage, if the Standby Resource is stopped because of some error, then TSA will try to start it. If the Standby resource is manually stopped then TSA will not try to start it.

J) Primary Instance TSA Resource Group Failure

Standby Enters into DISCONNECTEDPEER State because HADR_PEER_WINDOW=300 (seconds)

Standby enters into REMOTECATCHPENDING state after HADR_PEER_WINDOW expires

Standby continue to stay in REMOTECATCHPENDING STATE

Restore the PRIMARY instance RESOURCE group

K) Failover using DB2 TAKEOVER command

L) Failback using DB2 TAKEOVER command

DB2 9.7 HADR with TSA - Part 07 : Configuring DB2 9.7 HADR to work with TSA

2011-04-04T00:44:00.000+01:00

In this artical i will discuss some of the pre-req needed to configure DB2 HADR with TSA and provide some useful references.
a) Pre-Configuration Details
b) Configuring DB2 HADR with TSA
c) Monitoring DB2 HADR with TSA configuration

Part 0 : DB2 9.7 HADR with TSA Part 00 - Introduction

Part 2 : Installing IBM tools for querying and monitoring purpose

Part 6 : Configuring DB2 9.7 HADR between Primary and Standby

A) Pre-Configuration Details

Item #	Description	Command

System On which command will be executed =>		Primary	Standby
1	Archive the Current db2diag.log file	db2diag -A	db2diag -A
2	Set the database HADR_PEER_WINDOW configuration parameter to non-zero value	db2 get db cfg for sample \| grep -i hadr	db2 get db cfg for sample \| grep -i hadr
3	Prepare the Environment for TSA configuration	a) Login as root su - b) Execute the TSA command preprpnode {node1} {node2} For Example, preprpnode mumbai london	a) Login as root su - b) Execute the TSA command preprpnode {node1} {node2} For Example, preprpnode mumbai london
4	Identify your Virutal IP(VIP)m DB2 Service Port and Network Quorum IP Address	a) Virtual IP => 192.168.5.55 b) DB2 Service Port DBM CFG parameter svcename = 60000 c) Network Quorum IP address In production Environment this should be the address of third machine which becomes the tie breaker. For testing purpose i am using one of the DB2 HADR node IP address.

B) Configuring DB2 HADR with TSA

Item #	Description	Command

System On which command will be executed =>		Primary	Standby
1	Login as Instance owner	No Operation	su - db2inst1
2	Configure the DB2 HADR with TSA	No Operation	a) Start the DB2 HA instance configuration Utility db2haicu b) Refere the below link for further step by step details ftp://ftp.software.ibm.com/software/data/pubs/papers/HADR_db2haicu.pdf
3	Problem resoultion for the errors when using Virutal Machine	"If during the creation of Cluster domain following error is reported. This happen when the HADR node is cloned using OS copy command. Error 2632-044 The domain cannot be created due to the following errors that were detected while harvesting information from the target nodes: london: 2632-068 This node has the same internal identifier as mumbai and cannot be included Action 1) Identify the node where it is failing in the db2diag.log file with the above message. For example, from the above message we see "london" is the node name" 2) Login as ""root"" user on that node 3) Execute the command /usr/sbin/rsct/install/bin/recfgct 4) Prepare the nodes again on both PRIMARY and SECONDARY preprpnode mumbai london If you get following errors on Standby 2011-10-06-17.53.36.820837-240 E11710207E627 LEVEL: Warning PID : 15534 TID : 47621153264496PROC : db2haicu INSTANCE: db2inst1 NODE : 000 FUNCTION: DB2 Common, SQLHA APIs for DB2 HA Infrastructure, sqlhaUICreateHADR, probe:1000 RETCODE : ECF=0x9000056F=-1879046801=ECF_SQLHA_HADR_VALIDATION_FAILED The HADR DB failed validation before being added to the cluster MESSAGE : Standby Node not configured for HADR. DATA #1 : String, 8 bytes db2inst1 DATA #2 : String, 8 bytes db2inst1 DATA #3 : String, 6 bytes sydney DATA #4 : String, 6 bytes london DATA #5 : String, 6 bytes SAMPLE 1) Refer link https://www-304.ibm.com/support/docview.wss?uid=swg21420060 https://www-304.ibm.com/support/docview.wss?uid=swg21443643 http://www.ibm.com/developerworks/data/tutorials/dm-1009db2hadr/section3.html

C) Monitoring DB2 HADR with TSA configuration

Item #	Description	Command

System On which command will be executed =>		Primary	Standby
1	Listing the TSA resources and resource groups as "Instance Owner" user	a) Point in time Snapshot lssam b) Continues Snapshot lssam -top
2	Check Network Equivalency	lsequ -Ab
3	Identify the Communication Group	lsrsrc -Ab IBM.NetworkInterface Name IPAddress CommGroup HeartbeatActive NodeNameList
4	Active TieBreaker	lsrsrc -c IBM.PeerNode OpQuorumTieBreaker
5	Service IP Resource	lsrsrc -Ab IBM.ServiceIP
6	Active TieBreaker	lsrsrc -c IBM.PeerNode OpQuorumTieBreaker

D) Some useful resolution

A) Error "2612-023" when attempting "resetrsrc" on "Failed Offline" standby resource

Action
1) Identify the Node on which the resource is shown "Failed Offline".

2) Login to node on which it is failing as "root" user

3) Execute "export CT_MANAGEMENT_SCOPE=2"

4) Execute the following command
resetrsrc -s "Name='db2_db2inst1_db2inst1_SAMPLE-rs' and NodeNameList={'sydney'}" IBM.Application

5) The above command will take the resource out of "Failed Offline" mode.

B) Sometime the "Resource Group" state is shown "Offline" even after all the resource under the resource group are Online.
Action
1) Change the Nominal state of all the "Resource Group" to "Offline"
chrg -o offline

2) Stop the HADR configuration on the database using below sequence
Primary => db2 stop hadr on database sample
Primary => db2 deactivate db sample
Primary => db2stop force
Standby => db2 deactivate db sample
Standby => db2 stop hadr on db sample
Standby => db2stop force

3) Login as "root" user on any one of the node in the cluster and stop the cluster domain
lsrpdomain
stoprpdomain

4) Start the cluster domain as "root" user on any one node.
startrpdomain
lsrpdomain
lsrpnode

Notes: The domain take some time to bring all the services online so please be patience

5) Change the Nominal state of instance "Resource Group" to "Online" using below sequence
chrg -o online
chrg -o online

Notes: After execution of the above command instance on both the server are started automatically

6) Start the HADR on the database using below sequence
Primary => db2 activate db sample
Standby => db2 start hadr on db sample as standby
Primary => db2 start hadr on db sample as primary
db2pd -db sample -hadr ........... This show state as "Peer"

7) Change the Nominal state of "HADR Resource group" to "Online" using below sequence
chrg -o online

8) Check to see if all the resources are showing state as "Online"
lssam

DB2 9.7 HADR with TSA - Part 06 - Configuring DB2 9.7 HADR between Primary and Standby

2011-03-20T23:15:00.000Z

In this blog i will present step to configure non-Cluster DB2 9.7 HADR configuration. Following topics are covered in this blog
a) Pre-Configuration Details
b) Setting up non-cluster DB2 9.7 HADR
c) Manual Starting/Stopping HADR steps
d) Manul Failover/Failback Steps
e) Force Failover/Failback Steps

Part 0 : DB2 9.7 HADR with TSA Part 00 - Introduction

Part 1 : Installing and configuring VMWare environment

Part 2 : Installing IBM tools for querying and monitoring purpose

DB2 9.7 HADR with TSA Part 2 - Installing IBM tools for querying and monitoring purpose

A) Pre-Configuation Details

Item #	Description	Command

System On which command will be executed =>		Primary	Standby
1	IP Address and Hostname	a) Public Network IP => 192.168.5.22 Hostname => Mumbai b) Private Network (Optional) IP => 192.168.5.23 Hostname => priv01	a) Public Network IP => 192.168.5.33 Hostname => London b) Private Network (Optional) IP => 192.168.5.34 Hostname => priv02
2	Virtual IP (Configured by TSA)	IP => 192.168.5.55 Virtual Hostname => newyork
3	Create New Mount point (Repet step a to Step i for each new hard disk added to the virtual machine)	a) Add two hard disk to virtual machine and restart the virtual machine b) Log on to the OS to see the hard disk is added ls /dev/sd* -- All the hard disk which show /dev/sd[alphabet] are newly added hard disk. -- All the hard disk which shows /dev/sd[albhabet][number] are the existing hard disk on the server. c) Partition the hard disk using FDISK. The sequence of command to FDISK are "n", "p", "1", "Return(Enter Button on Keyboard)", "Return(Enter Button on Keyboard)","p" and "w" d) New partition get added to the list ls /dev/sd* e) Create the physical Volumne pvcreate /dev/sd[alphabet][number] where : alphabet = Character of new added disk Number = Number of the newly formated disk For Example, pvcreate /dev/sdc1 f) Create the file system on the volumne mkfs -t ext3 -m 1 -v /dev/sd[alphabet][number] For example:- mkfs -t ext3 -m 1 -v /dev/sdc1 g) Check the new FS created tune2fs -l /dev/sd[alphabet][number] For example;- tune2fs -l /dev/sdc1 h) Create the mount directory mkdir /db2fs01 i) Modify the /etc/fstab file to include following line /dev/sdc1 /db2fs01 ext3 defaults 1 1
4	Check OS details	a) OS Version oslevel -a b) Patch details c) Kernal Level
5	Check the Network Details	a) Public IP Address b) Private IP Address c) Virtual IP Address
6	Check DB2 details	a) Check DB2 version db2level b) Mount point for DATA c) Mount point for Logs d) Mount point for archive logs

B) Setting up non-Cluster HADR



Network device : eth0Hostname : LondonIP => 192.168.5.33
Hostname => newyorkVirtual IP => 192.168.5.55
192.168.5.22   mumbai192.168.5.33   london192.168.5.55   newyork
ping mumbaiping london
groupadd -g 1003 db2iadm1groupadd -g 102 db2fadm1
useradd -u 21000 -g 1003 -d /home/db2inst1 -m -c "DB2 Instance Owner user" db2inst1useradd -u 108 -g 102 -d /home/db2fenc1 -m -c "DB2 Fence Owner user" db2fenc1

0 london 0
chmod 777 /db2fsmkdir /db2fs/db2bkpmkdir /db2fs/db2archmkdir /db2fs/db2log
a) DB2 TCPIP communication parametersdb2set DB2COMM=tcpipdb2 "update dbm cfg using svcename 60000"db2stop force;db2startb) Enable DB2 to use SSH password less communicationdb2set -i db2inst1 DB2RSHCMD=/usr/bin/sshdb2stop force;db2startc) Update DB2 HADR database parameters Note Parameter values are differenct on each node. This values are set after RESTORING the database on STANDYBYupdate db cfg for Sample using hadr_local_host    londonupdate db cfg for Sample using hadr_remote_host   mumbaiupdate db cfg for Sample using hadr_local_svc     DB2_HADR_LONupdate db cfg for Sample using hadr_remote_svc    DB2_HADR_MUMupdate db cfg for Sample using hadr_remote_inst   db2inst1update db cfg for Sample using hadr_timeout       120update db cfg for Sample using hadr_peer_window       120update db cfg for Sample using hadr_syncmode      nearsync
backup database sample online to /db2fs01/db2bkp
scp SAMPLE.0.db2inst1.NODE0000.CATN0000.20101021100242.001 db2inst1@hadrtest2:/home/db2inst1/bkp
restore database sample replace history fileThis will set the database in rollforward pedning state as we created the database from online full backup of primary database after enabling primary database for archival logging. It will be in ROLLFORWARD PENDING STATE. No changes needed.DO NOT TAKE THE DATABASE OUT OF ROLL FORWARD PENDING STATE 
start hadr on database sample as standby
start hadr on database sample as primary
db2pd -hadr -db sampleHADR State = Peer
1) Archive current logarchive log for db sample2) Check the first log sequenceget db cfg for sample | grep -i first3) Check if the archive log file is shifted to Standby. (May take some time)db2pd -hadr -db sample

Item #	Description	Command
System On which command will be executed =>	Primary	Standby
1	Public Network and hostname	Network device : eth0 Hostname : Mumbai IP => 192.168.5.22	Network device : eth0 Hostname : London IP => 192.168.5.33
2	Virtual IP	Hostname => newyork Virtual IP => 192.168.5.55
3	Add the public/Virtual IP details to /etc/hosts file	192.168.5.22 mumbai 192.168.5.33 london 192.168.5.55 newyork	192.168.5.22 mumbai 192.168.5.33 london 192.168.5.55 newyork
4	Check the connectivity between nodes	ping mumbai ping london	ping mumbai ping london
5	Create DB2 Instance Owner and DB2 Fence Owner Group GID and GROUP name must be same on both node	groupadd -g 1003 db2iadm1 groupadd -g 102 db2fadm1	groupadd -g 1003 db2iadm1 groupadd -g 102 db2fadm1
6	Create DB2 Instance Owner user and DB2 fence owner user	useradd -u 21000 -g 1003 -d /home/db2inst1 -m -c "DB2 Instance Owner user" db2inst1 useradd -u 108 -g 102 -d /home/db2fenc1 -m -c "DB2 Fence Owner user" db2fenc1	useradd -u 21000 -g 1003 -d /home/db2inst1 -m -c "DB2 Instance Owner user" db2inst1 useradd -u 108 -g 102 -d /home/db2fenc1 -m -c "DB2 Fence Owner user" db2fenc1
7	Check DB2 ports in /etc/services files.Must be same on each of the nodes	a)DB2 TCPIP Communication port 6000 b) DB2 HADR port DB2_HADR_MUM=50011/tcp DB2_HADR_LON=50012/tcp	a)DB2 TCPIP Communication port 6000 b) DB2 HADR port DB2_HADR_MUM=50011/tcp DB2_HADR_LON=50012/tcp
8	Check ~/sqllib/db2nodes.cfg under DB2 instance owner user	0 mumbai 0	0 london 0
9	Create directory a) Backup b) Archive log	chmod 777 /db2fs mkdir /db2fs/db2bkp mkdir /db2fs/db2arch mkdir /db2fs/db2log	chmod 777 /db2fs mkdir /db2fs/db2bkp mkdir /db2fs/db2arch mkdir /db2fs/db2log
10	Update DB2 parameters	a) DB2 TCPIP communication parameters db2set DB2COMM=tcpip db2 "update dbm cfg using svcename 60000" db2stop force;db2start b) Enable DB2 to use SSH password less communication db2set -i db2inst1 DB2RSHCMD=/usr/bin/ssh db2stop force;db2start c) Update DB2 HADR database parameters Note Parameter values are differenct on each node update db cfg for sample using logarchmeth1 'DISK:/db2fs/db2arch' update database configuration for sample using LOGINDEXBUILD ON update db cfg for Sample using hadr_local_host mumbai update db cfg for Sample using hadr_remote_host london update db cfg for Sample using hadr_local_svc DB2_HADR_MUM update db cfg for Sample using hadr_remote_svc DB2_HADR_LON update db cfg for Sample using hadr_remote_inst db2inst1 update db cfg for Sample using hadr_timeout 120 update db cfg for Sample using hadr_peer_window 120 update db cfg for Sample using hadr_syncmode nearsync	a) DB2 TCPIP communication parameters db2set DB2COMM=tcpip db2 "update dbm cfg using svcename 60000" db2stop force;db2start b) Enable DB2 to use SSH password less communication db2set -i db2inst1 DB2RSHCMD=/usr/bin/ssh db2stop force;db2start c) Update DB2 HADR database parameters Note Parameter values are differenct on each node. This values are set after RESTORING the database on STANDYBY update db cfg for Sample using hadr_local_host london update db cfg for Sample using hadr_remote_host mumbai update db cfg for Sample using hadr_local_svc DB2_HADR_LON update db cfg for Sample using hadr_remote_svc DB2_HADR_MUM update db cfg for Sample using hadr_remote_inst db2inst1 update db cfg for Sample using hadr_timeout 120 update db cfg for Sample using hadr_peer_window 120 update db cfg for Sample using hadr_syncmode nearsync
11	Backup the database	backup database sample online to /db2fs01/db2bkp	No Operation
12	Copy the backup image to Standby machine	scp SAMPLE.0.db2inst1.NODE0000.CATN0000.20101021100242.001 db2inst1@hadrtest2:/home/db2inst1/bkp	No Operation
13	Create new database on standby from the offline backup	No Operation	restore database sample replace history file This will set the database in rollforward pedning state as we created the database from online full backup of primary database after enabling primary database for archival logging. It will be in ROLLFORWARD PENDING STATE. No changes needed. DO NOT TAKE THE DATABASE OUT OF ROLL FORWARD PENDING STATE
14	Start HADR on Standby	No Operation	start hadr on database sample as standby
15	Start HADR on Primary	start hadr on database sample as primary	No Operation
16	Check the HADR Status (must be same on both nodes)	db2pd -hadr -db sample HADR State = Peer	db2pd -hadr -db sample HADR State = Peer
17	Check if HADR is working	1) Archive current log archive log for db sample 2) Check the first log sequence get db cfg for sample \| grep -i first 3) Check if the archive log file is shifted to Standby. (May take some time) db2pd -hadr -db sample	1) Archive current log archive log for db sample 2) Check the first log sequence get db cfg for sample \| grep -i first 3) Check if the archive log file is shifted to Standby. (May take some time) db2pd -hadr -db sample

C) Manual Starting/Stopping HADR Steps

Item #	Description	Command

Starting HADR steps		Primary	Standby
1	Start Instance on Primary/Standby	db2start	db2start
2	Start HADR on Standby machine	No Operation	start hadr on database sample as standby
3	Start HADR on Primary Machine	start hadr on database sample as primary	No Operation
4	Check if DB2 HADR is running	db2pd -hadr -db sample	db2pd -hadr -db sample

Stopping HADR steps		Primary	Standby
1	Stop HADR on database	stop hadr on database sample	No Operation
2	Deactivate Database	deactivate database sample	No Operation
3	Stop instance	db2stop force	No Operation
4	Deactivate the standby database	No Operation	deactivate database sample
5	Stop HADR on database	No Operation	stop hadr on database sample
6	Stop Instance	No Operation	db2stop force

D) Manul Failover/Failback Steps

Item #	Description	Command

Failover Operation		Primary	Standby
1	Identify the Standby node	db2pd -db sample -hadr	db2pd -db sample -hadr
2	Execute takeover command on standby	No Operation	takeover hadr on database sample

Failback Operation		Primary	Standby
1	Identify the new Standby node	db2pd -db sample -hadr	db2pd -db sample -hadr
2	Execute takeover command on old Primary which is now new Standby after failover	takeover hadr on database sample	No Operation

E) Force Failover

Item #	Description	Command
Force Failover (Test Scenario)		Primary	Standby
1	Kill the DB2 instance procee to simulate Test Scenario	ps -eaf \| grep -i db2sysc kill -9 where PID = Second Column	No Operation
2	Check the status of the standby	No Operation	db2pd -hadr -db sample HADR State will be in Disconnected state
3	Execute the force failover on Standby	No Operation	takeover hadr on database sample by force
4	Bring the primary Server online	db2start start hadr on database sample as standby	No Operation
5	Failback to Primary machine	takeover hadr on database sample	No Operation
6	Check HADR status	db2pd -hadr -db sample	db2pd -hadr -db sample

DB2 9.7 HADR with TSA - Part 05 - Creating Instance and Database

2011-03-13T16:03:00.000Z

In this post i will give steps to create DB2 Instance and DB2 database.

DB2 9.7 HADR with TSA Part 00 - Introduction

DB2 9.7 HADR with TSA Part 1 : Installing and configuring VMWare environment

DB2 9.7 HADR with TSA Part 2 : Installing IBM tools for querying and monitoring purpose

DB2 9.7 HADR with TSA Part 3 : Installing IBM DB2 9.7 database

DB2 9.7 HADR with TSA Part 4: Installing DB2 9.7 Fixpack

A) Steps to Create DB2 Instance

Item#	Description	Command
1	Create new DB2 OS group	1) Instance Owner Group groupadd -g 110 db2iadm1 2) Fence User Group groupadd -g 112 db2grp1
2	Create DB2 instance ownere user	1) DB2 Instance Owner user useradd -u 550 -g 110 -d /home/db2inst1 -m -c "DB2 Instance Owner" db2inst1 2) Set the instance owner password passwd db2inst1
3	Create new DB2 fence user	1) DB2 Fence user useradd -u 551 -g 112 -d /home/db2fenc1 -m -c "DB2 Fence User" db2fenc1 2) Set Fence user password passwd db2fenc1
4	Create DB2 Instance	1) Login as "root" user 2) Change directory to db2 install path cd /opt/ibm/db2/V9.7/instance 3) Create DB2 instance ./db2icrt -u db2fenc1 db2inst1
5	Start the DB2 instance	1) Login as DB2 Instance Owner 2) Check the $DB2INSTANCE environment to reflect the instance name echo $DB2INSTANCE 3) Start the DB2 database manager (DBM) db2start 4) Check if DB2 database manager is started db2pd -edus
6	Check DB2 Database Manager Parameters	db2 get dbm cfg

B) Steps to Create DB2 database

Item#	Description	Command
1	Create DB2 Database	1) Login as DB2 instance owner 2) Create SAMPLE database db2sampl 3) Activate the SAMPLE database db2 activate db sample 4) Check if the database is activate successfully db2pd -edus 5) Check the database parameters db2 get db cfg for sample

C) Connecting to remote database using JDBC

Item#	Description	Command
1	Check the port number associate to instance	more /etc/services \| grep -i db2inst1
2	Check if the required port is set in SVCENAME DBM parameter	get dbm cfg \| grep i svcename
3	Check if the DB2COMM variable is set	db2set -all
4	Set DBM and registry variable variable and restart the instance	1)Set DBM parameter update dbm cfg using svcename 60008 2) Set registry variable db2set DB2COMM=tcpip 3) Restart the DB2 instance db2stop force;db2start
5	JDBC Connection Details	1) Hostname 2) Database Name 3) Instance Port number 4) Username 5) Password

D) Dropping the instance and database

Item#	Description	Command
1	Drop the database	1) Login as instance owner user under which DB2 database is created 2) Check if it is the correct instance db2 get instance 3) Check the database that needs to be dropped db2 list db directory 4) Deactivate the database a) db2 force application all b) db2 terminate c) db2 deactivate db sample 5) Drop the appropriate database db2 DROP DATABASE sample
2	Drop the Instance	1) Login as instance owner 2) Stop the instance db2stop force 3) Check if instance is stopped db2pd -edus 4) Login as "root" user 5) Change to DB2 Install directory /opt/ibm/db2/V9.7/instance 6) Drop the Instance ./db2idrop db2inst1
3	Delete the instance user, fence user and DB user	1) Delete the instance owner user userdel -f db2inst1 2) Delete the fence user userdel -f db2inst1

DB2 9.7 HADR with TSA - Part 04 - Installing DB2 9.7 Fix-pack

2011-03-13T14:37:00.000Z

In this post i will give steps to upgrade DB2 9.7 GA to DB2 9.7 Fixpack 3a.

DB2 9.7 HADR with TSA Part 00 - Introduction

DB2 9.7 HADR with TSA Part 1 : Installing and configuring VMWare environment

DB2 9.7 HADR with TSA Part 2 : Installing IBM tools for querying and monitoring purpose

DB2 9.7 HADR with TSA Part 3 : Installing IBM DB2 9.7 database

A) Introduction

DB2 fixpack are of two type. They are
1) DB2 Universal Fixpack

In this, you need to have the GA of the product installed on the machine. In this setup, it include all the APAR until this fixpack level. It does not include the base code of the product. This is ideal in the PROD environment.

2) DB2 Server Fixpack

In this, the fixpack include the base code + APAR until this fixpack level. This is just one single installation that include the Fixpack at the desired level. This is ideal for new installation.

B) Download Details

Item#	Description
Download Source	http://www-01.ibm.com/support/docview.wss?uid=swg27007053

C) Step to install fixpack

Item#	Description	Command
1	Downlaod the DB2 fixpack	NA
2	Stop DAS instance	db2admin stop
3	Stop all DB2 instance	db2stop force
4	Get the current DB2 9.7 product install path	db2ls
5 (a)	Execute the install Fixpack script to upgrade the already installed DB2 binary files	./installFixpack -b (DB2 product install path taken from db2ls command)
==OR== 5 (b)	Execute the install Fixpack script to install DB2 server fixpack to different location	./installFixpack -b (New location for DB2 installation)
6	Check the DB2 installed location	db2ls
7	Validate the installation	db2val

C) Steps to upgrade the instance to new Fixpack level

Item#	Description	Command
1	Check the DB2 installed location	db2ls
2	Get the instance names	db2ilist
3	Get the db2fence user name	cat sqllib/ctrl/.fencedID
4	Upgrade the instance	1) Login as root 2) cd (New DB2 install location taken from output of db2ls command) 3) cd (db2_install_path)/instance 4) ./db2iupdt -u (fence_user_name) (Instance_name)
5	Upgrade DAS instance(DAS is Deprecated in Db2 9.7)	1) Login as root 2) cd (new_db2_install_path)/instance 3) ./dasupdt

DB2 9.7 HADR with TSA - Part 03 - Installing IBM DB2 9.7 database

2011-03-12T18:13:00.000Z

In this blog i will provide links to the website contributed by the DB2 community which will demostrate the DB2 9.7 installation.

DB2 9.7 HADR with TSA Part 00 - Introduction

DB2 9.7 HADR with TSA Part 1 - Installing and configuring VMWare environment

A) Links to website demostrating DB2 installation.

1.a) DB2 9.7.2 Installation on Windows Steps

1.b) Installation methods for DB2 LUW V9.1, V9.5, and V9.7

2) DB2 Installation and different DB2 Edition

3) What new in since DB2 9.7 GA

Some helpful websites for DB2 begineers

0) DB2 Architecture

1) The DB2 Environment - Part 01   & Part 02

2) Tools and Scripting - Part 01   & Part 02

3) Client Connectivity - Part 01   & Part 02

4) Working with database objects

5) Data Movement

6) Security

7) Backup and recovery

8) Maintenance

9) Concurrency and Locking - Part 01   & Part 02

10) SQL PL Stored Procedures

11) User defined functions

12) Triggers

13) Troubleshooting

DB2 9.7 HADR with TSA - Part 02 - Installing IBM tools for querying and monitoring purpose

2011-03-12T17:37:00.000Z

IBM provide excellent tools to work with DB2. Starting from DB2 9.7 DB2 control Center is deprecated and will be removed in future versions of DB2. The replacement tool for DB2 Control Center is IBM Data Studio.

DB2 9.7 HADR with TSA - Part00

DB2 9.7 HADR with TSA - Part 01 Installing IBM tools for querying and monitoring purpose

A) Introduction

IBM Data Studio is the replacement tool for DB2 Control Center. It provide most of the feature that were part of DB2 CC. It include additional functionality of PL/SQL development, Java programming, etc. other important extension. It is eclipse based and its shell is shared with other IBM Optim tools such as Optim Database Administrator.

B) Download Details

Item#	Description
Download Source	https://www-304.ibm.com/support/docview.wss?uid=swg24025984

C) Installation

C.1) Data Studio Installation

C.2) Data Studio Health Monitor

DB2 9.7 HADR with TSA - Part 01 - Installing and configuring VMWare environment

2010-11-15T00:55:00.000Z

In this blog i will explain the steps needed to install VMWare software.
DB2 9.7 HADR with TSA Part 00 - Introduction

A) Introduction

VMWare is the industry standard virtualization software company. VMWare Server is one of the virtualization software that allows to run multiple Guest OS on top of single Host OS. For example, a laptop having Microsoft Windows XP Operating System is termed as Host OS. VMWare Server software is then installed on this Host OS. VMWare Server then provides services that allows running multiple OS called as Guest OS under its runtime environment.

B) Download Details
The VMware software can be downloaded from VMWare website

Item#	Description
Download Source	http://www.vmware.com/products/server/
Documentation	https://www.vmware.com/support/pubs/server_pubs.html

C) Installation

D) Configuration

Start the VMWare Server from Windows XP Start Menu

Start -> VMware -> VMware Server -> VMware Server Home Page

Step 1: Login to VMware server using your Host OS Login crediantial

Step 2:

Create Virtual Machine using the menu Virtual Machine -> Create Virtual Machine

Provide name for your new virtual machine. In our case the name of the Virtual Machine is mumbai

Select the Guest OS that will be installed on Virtual Machine. In our case the guest OS is Red Hat Enterprise Linux 5.

Select the Physical Memory for the Guest OS. This memory will the pulled from Host OS. In our case I specified 1024MB. Kindly select appropriate memory based on available memory on you machine. Giving more memory than what is available on laptop may result into paging.

Create hard disk for virtual machine that will be used to install Guest OS. Select Create a New Virtual Disk

Provide value for Hard disk properties

Capacity :- 10GB
Location :- [standard] chicago/chicago.vmdk
File Option :- Make sure “Allocate all disk space now” is unchecked.
Disk Mode :- Independent -> Persistent
Virtual Device Node :- Adapter => SCSI 0 Device => 0
Policies :- Optimize for safety

Add network adapter for the virtual machine. Select Add a Network Adapter

Select Bridged Network Connection

Create Media drive for your Guest OS. Select “Use a Physical Drive”

Give drive name for the media drive

Ignore creation of Floppy dirve for Guest OS

Add USB controller for Guest OS

Summary of the new Virtual machine that will be created. Click Finish to create virtual machine

New virtual machine is created in is listed on Left Panel in VMware Home Page

Step 3 : Installing the Guest Operating System on Virtual Machine

After creating the Virtual machine, next task is to install the Guest OS on the Virtual Machine. For this sample example we use CentOS 5.2 Linux Operating System. This is the same package that will is used to develop the RED HAT Enterprise Linux Operating System.

Donwload Instruction

Item#	Description
Download Source	http://mirror.ukhost4u.com/centos/5.4/isos/i386/ CentOS-5.4-i386-bin-1of6.iso CentOS-5.4-i386-bin-2of6.iso CentOS-5.4-i386-bin-3of6.iso CentOS-5.4-i386-bin-4of6.iso CentOS-5.4-i386-bin-5of6.iso CentOS-5.4-i386-bin-6of6.iso
Documentation	http://www.centos.org/docs/5/

Start the Virtual Machine by clicking the Green Play button on top menu. Before starting the Virtual Machine make sure the CentOS CD is inserted into your CD Drive. VMware pick the detail and start the Linux Installation setup.

Click the Console tab which will let you see the insall screen. Click the Window which will open a new VMware client window

This will show the Linux OS install steps. Follow the Linux step to install Linux OS on Virtual Machine

Install all the package to avoid problem of missing library files. Also provide following details

Hostname : chicago.localdomain
IP Address eth0 : 192.168.9.111
Default Gateway eth0: 192.168.9.1
IP Address eth1 : 192.168.0.105
Default Gateway eth1 : None

Step 4: Starting the Viirtual Machine

Once the OS is installed on the virtual machine, start the virtual machine to login to Guest OS

DB2 9.7 HADR with TSA - Part 00

2010-11-14T23:26:00.000Z

In coming weeks before I start my Christmas vacation, i would be presenting series of blogs explaining the DB2 9.7 HADR configuration using TSA. All work which i would be presenting in the upcoming blogs was carried out on VMWare test environment.

The DB2 9.7 HADR with TSA series of blogs is organised in following sections

Part 1 : Installing and configuring VMWare environment

Part 2 : Installing IBM tools for querying and monitoring purpose

Part 6 : Configuring DB2 9.7 HADR between Primary and Standby