Sunday, March 20, 2011

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

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







A) Pre-Configuation Details

Item # DescriptionCommand
System On which command will be executed => PrimaryStandby
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

Item # DescriptionCommand
System On which command will be executed => PrimaryStandby
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 # DescriptionCommand
Starting HADR stepsPrimaryStandby
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 stepsPrimaryStandby
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 # DescriptionCommand
Failover OperationPrimaryStandby
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 OperationPrimaryStandby
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 # DescriptionCommand
Force Failover (Test Scenario) PrimaryStandby
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


Sunday, March 13, 2011

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

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

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







Saturday, March 12, 2011

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

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

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.








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