With the reads on standby feature, the standby database of an HADR pair can be used to support read only applications. The standby can be enabled in a read mode by setting the value of registry variable DB2_HADR_ROS. The registry variable is set to ON to enable reads on standby. The setting of the registry variable is not dynamic. In other words, the standby server must be stopped and restarted for the change to the registry variable to take effect. If, in the event that the standby becomes a primary due to a takeover operation, the registry variable will not have any effect on the new primary. The read capability is supported for the HADR sync modes: ASYNC, NEARSYNC, SYNC and SUPERASYNC. Read capability is not supported when the standby is in local catch-up state.
Perform the following steps to set up DB2_HADR_ROS.
- After setting up the HADR pair, check to see if DB2_HADR_ROS is set.
db2 => !db2set - Connecting to the standby database should yield SQL1776 rc=1, as shown in Listing 1.
Listing 1. Connect to standby database
db2 => connect to hadrdb SQL1776N The command is not supported on an HADR standby database or on an HADR standby database with the current configuration or state. Reason code = "1".
- Set the DB2_HADR_ROS variable, as shown in Listing 2.
Listing 2. Set variable
db2 => !db2set DB2_HADR_ROS=ON db2 => !db2set DB2_HADR_ROS=ON
- Bounce the server for the registry variable to take effect, as shown in Listing 3.
Listing 3. Bounce the server
db2 => deactivate db hadrdb DB20000I The DEACTIVATE DATABASE command completed successfully. db2 => !db2stop SQL1064N DB2STOP processing was successful. db2 => !db2start SQL1063N DB2START processing was successful.
- Activate and connect to the standby database, as shown in Listing 4.
Listing 4. Activate and connect
db2 => activate db hadrdb DB20000I The ACTIVATE DATABASE command completed successfully. db2 => connect to hadrdb Database Connection Information Database server = DB2/LINUXX8664 9.7.5 SQL authorization ID = KATTI Local database alias = HADRDB
Verify HADR setup using RoS
Users generally want to verify that the HADR setup is working as expected and that there has been no data loss after the HADR pair is configured. Before DB2 V9.7 FP1, you had to use TAKEOVER to make the standby server become the new primary server in order to verify an HADR setup. No connections were allowed on the standby server, since it is offline to users. Thus it was difficult to check data on standby. The only way to bring standby online was TAKEOVER.
The following process was required to verify HADR before RoS was available.
- On primary side, make some changes, such as creating a table and inserting values, as shown in Listing 5.
Listing 5. Changes to primary server
[1049] [xjcd@db2eng63] /home/xjcd => db2 connect to hadrdb Database Connection Information Database server = DB2/LINUXX8664 9.7.5 SQL authorization ID = XJCD Local database alias = HADRDB [1050] [xjcd@db2eng63] /home/xjcd => db2 "create table t1(c1 int)" DB20000I The SQL command completed successfully. [1051] [xjcd@db2eng63] /home/xjcd => db2 "insert into t1 values(123)" DB20000I The SQL command completed successfully. [1052] [xjcd@db2eng63] /home/xjcd => db2 connect reset DB20000I The SQL command completed successfully.
- On the standby side, run TAKEOVER HADR and check the changes made on the primary server, as shown in Listing 6.
Listing 6. Checking the changes on the standby server
[898] [xjcd@db2eng64] /home/xjcd => db2 takeover hadr on db hadrdb DB20000I The TAKEOVER HADR ON DATABASE command completed successfully. [899] [xjcd@db2eng64] /home/xjcd => db2 connect to hadrdb Database Connection Information Database server = DB2/LINUXX8664 9.7.5 SQL authorization ID = XJCD Local database alias = HADRDB [900] [xjcd@db2eng64] /home/xjcd => db2 "select * from t1" C1 ----------- 123 1 record(s) selected.
With read on standby enabled after DB2 V97 FP1, verifying the HADR setup becomes much easier. Connections can be established directly to the standby, so no takeover is required.
Perform the following steps to verify HADR using RoS.
- On the primary side, make some changes to the database, as shown in Listing 7.
Listing 7. Making changes to the primary database
[1049] [xjcd@db2eng63] /home/xjcd => db2 connect to hadrdb Database Connection Information Database server = DB2/LINUXX8664 9.7.5 SQL authorization ID = XJCD Local database alias = HADRDB [1050] [xjcd@db2eng63] /home/xjcd => db2 "create table t1(c1 int)" DB20000I The SQL command completed successfully. [1051] [xjcd@db2eng63] /home/xjcd => db2 "insert into t1 values(123)" DB20000I The SQL command completed successfully. [1052] [xjcd@db2eng63] /home/xjcd => db2 connect reset DB20000I The SQL command completed successfully.
- Connect to standby directly after RoS is enabled, and check for the changes made on the primary database, as shown in Listing 8.
Listing 8. Checking for changes on the standby database
[910] [xjcd@db2eng64] /home/xjcd => db2 connect to hadrdb Database Connection Information Database server = DB2/LINUXX8664 9.7.5 SQL authorization ID = XJCD Local database alias = HADRDB [911] [xjcd@db2eng64] /home/xjcd => db2 "select * from t1" C1 ----------- 123 1 record(s) selected.
Replay only window on the standby database
When an HADR active standby database is replaying DDL log records or maintenance operations, the standby enters the replay-only window. When the standby is in the replay-only window, existing connections to the standby are terminated and new connections to the standby are blocked (SQL1776N Reason Code 4). New connections are allowed on the standby after all of the transactions that issued DDL or maintenance operations have completed. Existing applications are forced off at the outset of the replay-only window, and an error is returned (SQL1224N). The replay only window status can be obtained by executing the
db2pd -db db_name -hadr command on the standby database.
Perform the following steps.
- After setting up the HADR pair and establishing a connection to the standby database, issue the following command, shown in Listing 9, to obtain the current active applications on standby.
Listing 9. Obtaining the status of applications on the standby database
db2 => list applications Auth Id Application Appl. Application Id DB # of Name Handle Name Agents -------- -------------- ---------- -------------- -------- ----- KATTI db2bp 13 *LOCAL.katti.120305194800 HADRDB 1 - Check to see if the replay only window is active or inactive on the standby database, as shown in Listing 10.
Listing 10. Checking replay only window
db2 => !db2pd -db hadrdb -hadr Database Partition 0 -- Database HADRDB -- Active Standby -- Up 0 days 00:05:37 – Date 03/05/2012 11:50:58 HADR Information: Role State SyncMode HeartBeatsMissed LogGapRunAvg (bytes) Standby Peer Sync 0 3298 ConnectStatus ConnectTime Timeout Connected Mon Mar 5 11:45:26 2012 (1330976726) 120 ReplayOnlyWindowStatus ReplayOnlyWindowStartTime MaintenanceTxCount Inactive N/A 0 LocalHost LocalService grebe DB2_katti RemoteHost RemoteService RemoteInstance petrel xkatti katti PrimaryFile PrimaryPg PrimaryLSN S0000001.LOG 13 0x000000000235DFB6 StandByFile StandByPg StandByLSN StandByRcvBufUsed S0000001.LOG 13 0x000000000235DFB6 0%
- Issue an uncommitted transaction containing DDL on the primary database, as shown in Listing 11.
Listing 11. Issuing an uncommitted transaction
db2 => update command options using c off DB20000I The UPDATE COMMAND OPTIONS command completed successfully. db2 => create table t1(c1 int, c2 int) DB20000I The SQL command completed successfully.
- Check to see if the replay only window is active, as shown in Listing 12.
Listing 12. Checking the replay only window
db2 => !db2pd -db hadrdb -hadr Database Partition 0 -- Database HADRDB -- Active Standby -- Up 0 days 00:10:26 -- Date 03/05/2012 11:55:47 HADR Information: Role State SyncMode HeartBeatsMissed LogGapRunAvg (bytes) Standby Peer Sync 0 1441 ConnectStatus ConnectTime Timeout Connected Mon Mar 5 11:45:26 2012 (1330976726) 120 ReplayOnlyWindowStatus ReplayOnlyWindowStartTime MaintenanceTxCount Active Mon Mar 5 11:54:09 2012 (1330977249) 1 LocalHost LocalService grebe DB2_katti RemoteHost RemoteService RemoteInstance petrel xkatti katti PrimaryFile PrimaryPg PrimaryLSN S0000001.LOG 15 0x000000000235FCFC StandByFile StandByPg StandByLSN StandByRcvBufUsed S0000001.LOG 14 0x000000000235EE8A 0% - See if existing applications have been kicked off, as shown in Listing 13.
Listing 13. Existing applications
db2 => list applications SQL1611W No data was returned by Database System Monitor.
- Check to see if new connections are blocked, as shown in Listing 14.
Listing 14. New connections
db2 => connect to hadrdb SQL1776N The command is not supported on an HADR standby database or on an HADR standby database with the current configuration or state. Reason code = "4".
So the replay windows obviously have an impact on user business on the standby database. If RoS is enabled on standby, ensure that you do the following.
- Plan to have all the operations that will generate replay only windows occur
- together in a short period of time on the primary server.
- Enable automatic commit on the primary server so that
- the replay only window can be as short as possible.
- Turn off the automatic maintenance features on both the primary and standby servers, as shown in Listing 15.
Listing 15. Turning off the automatic maintenance features on primary and standby databases
=> db2 UPDATE DATABASE CFG FOR hadrdb USING AUTO_MAINT OFF AUTO_RUNSTATS OFF AUTO_REORG OFF DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
- For the full list of DDL statements and maintenance operations, please refer to DB2 Information Center link in the Resources section for more information on this article.
Thanks a lot, you saved our group assignment!
ReplyDelete