DB2 HADR - stanislawbartkowski/wikis GitHub Wiki
DB2 HADR
HADR, High Availability and Disaster Recovery is an outstanding feature of DB2. How it works is described below.
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.
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
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/ [email protected]:/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.
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 [email protected]:/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.