Skip to content

DB2 HADR

Jump to bottom
stanislawbartkowski edited this page Feb 9, 2021 · 47 revisions

DB2 HADR

HADR, High Availability and Disaster Recovery is an outstanding feature of DB2. How it works is described below.

https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5.0/com.ibm.db2.luw.admin.ha.doc/doc/c0011267.html

Explained in plain text: https://developer.ibm.com/technologies/analytics/articles/enabling-db2-encryption-using-ssl-in-hadr-environment/

HADR is available in all genres of DB2 including free Community Edition.

https://www.ibm.com/cloud/blog/announcements/ibm-db2-developer-community-edition

Setting up and exploiting the full power of DB2 HADR can be scary and seems to be complex, there is even a free Redbook analyzing all aspects of DB2 High Availability across all platforms supported. http://www.redbooks.ibm.com/abstracts/sg247363.html

There is also good news, the basic configuration is easy and straightforward and can be completed in several clear steps.

https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5.0/com.ibm.db2.luw.admin.ha.doc/doc/t0011725.html

Below I'm describing how to build a minimal but powerful HADR cluster starting from scratch. Having grasped the basics, it is pretty easy to jump into more complex customer use cases.

Prerequsities

  • DB2: DB 11.5 Community Edition
  • Two Linux hosts, free Centos 7/8 and VM machines.
  • Assume hostnames db1.sb.com and db2.sb.com
  • DB2 is installed on both hosts and db2inst1 instance is created.

Run db2sampl on db1.sb.com command to create SAMPLE database.

Important: HADR is configured at the database, not instance, level. In a single instance, we can have a database(s) in HADR mode and a database(s) in non-HADR mode.

Configure SAMPLE on db1.sb.com as a primary database

Enable archive logging on SAMPLE

More: https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5.0/com.ibm.db2.luw.admin.ha.doc/doc/c0051344.html

As a default, circular logging is enabled. HADR requires archive logging.

db2 "UPDATE DB CFG FOR sample USING LOGARCHMETH1 LOGRETAIN"

Configure local and remote instance

HADR requires a separate port to synchronize between hosts. It can be any not occupied port, assume 60000 port.

db2 "UPDATE DB CFG FOR sample USING HADR_LOCAL_HOST db1.sb.com HADR_LOCAL_SVC 60000"

db2 "UPDATE DB CFG FOR sample USING HADR_REMOTE_HOST db2.sb.com HADR_REMOTE_SVC 60000 HADR_REMOTE_INST db2inst1"

Important: at this point, HADR is not activated. Database SAMPLE is still in a standard mode.

Make an off-line full backup of SAMPLE

Important: database backup should be done after enabling archive logging.

mkdir /tmp/db2backup
db2 BACKUP DATABASE sample TO /tmp/db2backup

Backup successful. The timestamp for this backup image is : 20201216005042

Remark: To make an offline backup, the database should be deactivated.
Close all connection or break them forcibly.

db2 force application all
db2 deactivate db sample
db2 backup database ....
db2 activate db sample

Reconnect client applications.


Copy backup to db2.sb.com host

scp -r /tmp/db2backup/ db2inst1@db2.sb.com:/tmp

Configure SAMPLE on db2.sb.com as a secondary database

Restore SAMPLE from backup

db2 RESTORE DB sample FROM /tmp/db2backup taken at 20201216005042

Database SAMPLE is now in ROLL-FORWARD PENDING state, keep it unchanged.

Configure local and remote

The same command as before but with local and remote role swapped.

db2 "UPDATE DB CFG FOR sample USING HADR_LOCAL_HOST db2.sb.com HADR_LOCAL_SVC 60000"

db2 "UPDATE DB CFG FOR sample USING HADR_REMOTE_HOST db1.sb.com HADR_REMOTE_SVC 60000 HADR_REMOTE_INST db2inst1"

Enable db2.sb.com as Standby

db2 "START HADR ON DB sample AS STANDBY"

SQL1766W  The command completed successfully. However, LOGINDEXBUILD was not 
enabled before HADR was started.

Make sure that Standby database is activated.

db2 ACTIVATE DB sample

Enable db1.sb.com as Primary

db2 "START HADR ON DB sample AS PRIMARY"

SQL1766W  The command completed successfully. However, LOGINDEXBUILD was not 
enabled before HADR was started.

Final

That is all. We have DB2 HADR cluster, db1.sb.com is standing as primary and db2.sb.com as secondary. Every committed transaction on db1.sb.com is replayed on db2.sb.com.

Test

Verify that HADR cluster is working.

On db1.sb.com create a table and insert several rows.

db2 CONNECT TO SAMPLE
db2 "CREATE TABLE test (x int, name varchar(100))"
db2 "INSERT INTO test values(1,'John')"
db2 "INSERT INTO test values(2,'Mary')"

Change the roles and verify db2.sb.com

On db2.sb.com force takeover.

db2 "TAKEOVER HADR ON DB sample"

DB20000I  The TAKEOVER HADR ON DATABASE command completed successfully.

Now db2.sb.com is Primary and db1.sb.com is Standby.

db2 CONNECT TO SAMPLE
db2 "SELECT * FROM test"

           NAME                                                                                                
----------- ----------------------------------------------------------------------------------------------------
          1 John                                                                                                
          2 Mary                                                                                                

  2 record(s) selected.

Additional remarks

Useful commands

db2 START HADR ON DB sample AS PRIMARY
db2 START HADR ON DB sample AS STANDBY

Force failover, on Secondary node

db2 TAKEOVER HADR ON DB sample

Remove HADR and put a database to standard mode

db2 stop hadr on db sample

Check HADR configuration

db2 GET DB CFG FOR sample | grep -i HADR

HADR healthcheck

db2 "SELECT HADR_STATE FROM TABLE (mon_get_hadr(NULL))"

HADR_STATE             
-----------------------
PEER                   

  1 record(s) selected.

Another method

db2pd -db sample -hadr

Recovery after shutdown

Firstly start Secondary instance

db2start

Important: activate Standby database!

db2 activate db sample

Safely start Primary instance

db2start

Connect to database

db2 connect to sample

Verify HADR state, should be reported as PEER

db2 "SELECT HADR_STATE FROM TABLE (mon_get_hadr(NULL))"

HADR on TLS

DB2 on TLS

Before enabling TLS for HADR communication, enable TLS for both DB2 instances participating in HADR cluster.

https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5.0/com.ibm.db2.luw.admin.sec.doc/doc/t0025241.html

Practical steps are described (for BigSQL): https://github.com/stanislawbartkowski/IBMBigSQLSSL/blob/master/README.md

Below in a nutshell.

Collect necessary information

Information Value
Directory for server certificates /home/db2inst1/security
Fully qualified path of keystore /home/db2inst1/security/db2.kdb
Fully qualified path of stash file (encrypted password) /home/db2inst1/security/db2.sth
Key and certificate label db2
DB2 secure port 50010
Keystore password secret, use more secure password in production environment

Create keystore

mkdir -p /home/db2inst1/security
chmod 700 /home/db2inst1/security
cd /home/db2inst1/security
gsk8capicmd_64 -keydb -create -db db2.kdb -pw "secret" -stash

Self-signed certificate

gsk8capicmd_64 -cert -create -db db2.kdb -pw secret -label db2 -dn "CN=db1.sb.com,O=myDB1,OU=thinkde,L=H,ST=MZ,C=WAW" -size 2048 -sigalg SHA256_WITH_RSA

Create CSR file to be signed by CA, recommended in a production environment. You can stay with self-signed certificate in less secure environment.

gsk8capicmd_64 -certreq -extract -db db2.kdb -label db2 -file db2.csr -stashed

Send db2.csr to CA. Assume that ca-chain.cert.pem contains certificate chain and db1.sb.com.cert.pem signed certificate. Import certificates back to DB2 keystore.

gsk8capicmd_64 -cert -add -db db2.kdb -file /tmp/ca-chain.cert.pem -stashed
gsk8capicmd_64 -cert -receive -db db2.kdb -file /tmp/db1.sb.com.cert.pem -stashed

Configure DB2 instance to listen on secure port

db2 update dbm cfg using SSL_SVR_KEYDB /home/db2inst1/security/db2.kdb
db2 update dbm cfg using SSL_SVR_STASH /home/db2inst1/security/db2.sth
db2 update dbm cfg using SSL_SVR_LABEL db2
db2 update dbm cfg using SSL_SVCENAME 50010
db2set DB2COMM=SSL,TCPIP

In a production environment, it is recommended to stay with SSL only.

Restart DB2 instance

db2stop
db2start

Check that DB2 is listening on the secure port, it is possible also that server certificate is not presented here.

openssl s_client -connect localhost:50010

CONNECTED(00000003)
140369608922944:error:1425F102:SSL routines:ssl_choose_client_version:unsupported protocol:ssl/statem/statem_lib.c:1942:
---
no peer certificate available
---
No client certificate CA names sent
---
SSL handshake has read 4326 bytes and written 296 bytes
Verification: OK
---
New, (NONE), Cipher is (NONE)
Secure Renegotiation IS NOT supported
Compression: NONE
Expansion: NONE
No ALPN negotiated
Early data was not sent
Verify return code: 0 (ok)
---

DB2 client on SSL

Connect a client using a secure port.

On server side.

cd /home/db2inst1/security

Export server certificate

gsk8capicmd_64 -cert -extract -db db2.kdb -label db2 -target /tmp/db2.arm -format ascii -fips -stashed

Create client truststore and import certificates

mkdir /home/db2inst1/client
cd /home/db2inst1/client
gsk8capicmd_64 -keydb -create -db db2.kdb -pw "secret" -stash

For CA-signed certificate, import certificate chain.

gsk8capicmd_64 -cert -add -db db2.kdb -file /tmp/ca-chain.cert.pem -format ascii -stashed

Import DB2 server certificate

gsk8capicmd_64 -cert -add -db db2.kdb -label db2 -file /tmp/db2.arm -format ascii -stashed

ls

db2.crl
db2.kdb
db2.rdb
db2.sth

On the client side, copy /home/db2inst1/client from server directory.
Assume that certificate directory on client desktop is also /home/db2inst1/client

db2 update dbm cfg using SSL_CLNT_KEYDB /home/db2inst1/client/db2.kdb
db2 update dbm cfg using SSL_CLNT_STASH /home/db2inst1/client/db2.sth

Configure SSL DB2 instance node

db2 catalog tcpip node db1ssl remote db1.sb.com server 50010 security SSL
db2 catalog database sample as sampless at node db1ssl
db2 connect to sampless user db2inst1

Enter current password for db2inst1: 

 Database Connection Information

 Database server        = DB2/LINUXX8664 11.5.0.0
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLESS

If the connection fails with the error message:

SQL10013N  The specified library "GSKit Error: 408" could not be loaded.  
SQLSTATE=42724

Verify that certificate and stash files are accessible by the client.

ll /home/db2inst1/client/

-rw-------. 1 db2inst1 db2iadm1    88 12-26 13:53 db2.crl
-rw-------. 1 db2inst1 db2iadm1 15088 12-26 13:53 db2.kdb
-rw-------. 1 db2inst1 db2iadm1    88 12-26 13:53 db2.rdb
-rw-------. 1 db2inst1 db2iadm1   193 12-26 13:53 db2.sth

Give the client read access to sth and kbd files.

chmod 644 db2.sth
chmod 644 db2.kdb

Enable SSL HADR

Export and exchange server certificates for both DB2 instances in HADR. Use the following command to export instance certificate:

gsk8capicmd_64 -cert -extract -db db2.kdb -label db2 -target /tmp/db2.arm -format ascii -fips -stashed

Then copy standby server certificate to primary and primary certificate to standby

Standby server, primary is db1.sb.com

scp db2.arm db2inst1@db1.sb.com:/tmp/db2standby.arm

Primary server, standby is db2.sb.com

scp db2.arm db2inst1@db2:/tmp/db2primary.arm

Import certificates into appropriate keystore and use different label then db2

cd /home/db2inst1/security

Primary server, import standby certificate and use standby label

gsk8capicmd_64 -cert -add -db db2.kdb -label standby -file /tmp/db2standby.arm -format ascii -stashed

Reconfigure HADR. The HADR_SSL_LABEL specifies the certificate used for HADR encryption, can be the same as for client encryption.

db2 update db cfg for sample using HADR_SSL_LABEL db2

The same for standby server

gsk8capicmd_64 -cert -add -db db2.kdb -label primary -file /tmp/db2primary.arm -format ascii -stashed
db2 update db cfg for sample using HADR_SSL_LABEL db2

List the content of keystore

gsk8capicmd_64 -cert -list -db db2.kdb -stashed

!	CN=thinkde.sb.com,OU=IntermediateRoom,O=MyHome,ST=Mazovia,C=PL
!	CN=thinkde.sb.com,OU=MyRoom,O=MyHome,L=Warsaw,ST=Mazovia,C=PL
!	primary
-	db2

Restart both servers.

Clone this wiki locally