Reads on standby restrictions

You can use the reads on standby feature of high availability disaster recovery (HADR) to run read-only workloads on an HADR active standby database. In addition to the read-only restriction, this feature has other limitations that you should be aware of.

  • If the replay-only window is enabled (DB2_HADR_ROS_AVOID_REPLAY_ONLY_WINDOW is set to OFF), then the standby database is inaccessible to all application connections during the replay of DDL log records or maintenance operations (during the replay-only window). If the replay-only window is disabled (DB2_HADR_ROS_AVOID_REPLAY_ONLY_WINDOW is set to ON), then only the tables, indexes or other objects that conflict with the DDL or maintenance operation are inaccessible to application connections. For more details, see Replay-only window (and replay-only window avoidance) on the active standby database.
  • The standby is inaccessible to user connections when it is in the local catchup state.
  • The reads on standby feature is not supported in Db2® pureScale® environments.
  • Only the uncommitted read (UR) isolation level is supported on the standby. Applications, statements, or sub-statements that request a higher isolation level receive an error.
  • The instance-level audit configuration is not replicated to the standby. You must ensure that the instance-level auditing settings are the same on the primary and the standby by using the db2audit tool.
  • You cannot configure the standby as a federation server.
  • Text Search feature is not supported on HADR standby with reads on standby enabled.
  • The standby is inaccessible to user connections while it is actively replaying upgrade log records and is considered in an upgrade in progress state.
Data and table types
  • Declared global temporary tables (DGTTs) are not supported on the standby.
  • Created global temporary tables (CGTTs) can be created only on the primary database. Their definitions are replicated to the standby. However, access to CGTTs is not supported on the standby.
  • The creation of CGTTs on the primary triggers the replay-only window on the standby.
  • Tables with the NOT LOGGED INITIALLY (NLI) attribute cannot be accessed on the standby.
  • Column-organized tables cannot be accessed on the standby.
  • XML and large object (LOB) data must be inline to be successfully queried.
  • You cannot query the following data: long field (LF), a distinct type based on LF data types, structured type columns, and varying-length string data (that is, data that resides in extended rows).
Operations
  • Write operations, namely operations that modify permanent database objects such as catalogs, tables, and indexes, are not permitted on the standby. In particular, you cannot perform any operation that generates log records on the standby.
  • The db2exfmt command and the db2batch command are not supported on the standby database. If you want to analyze and optimize the performance of the read-only workload, you will need to run these tools on the primary database to perform the necessary optimizations, and then move the optimized workload to the standby database. While the db2expln command is supported on the standby database, you need to run the command on the primary database at least once to bind the required packages.
  • Explicit binding of packages, explicit rebinding of packages, and implicit rebinding of packages are not supported on the standby. Attempts to run static packages that refer to invalidated objectsresult in an error. Instead, bind the package on the primary and run the package on the standby after replicating the change to the standby.
  • The self-tuning memory manager (STMM) is not supported on the standby. If you want to tune the standby, either to suit running the read-only workload or to help the standby to perform well after takeover, you must do so manually.
  • Workload manager (WLM) DDL statements on the primary are replayed on the standby, but they are not effective on the standby. However, any definitions in the database backup that you used to set up the standby are active on a read-enabled standby.
  • Creation and alteration of sequences is not supported on the standby. As well, you cannot use the NEXT VALUE expression to generate the next value in a sequence.
  • Runtime revalidation of invalid objects is not supported on the standby.
  • Backup and archive operations are not supported on the standby.
  • Quiesce operations are not supported on the standby.
  • The db2ReadLog API cannot be called on the standby.
  • You cannot use the automatic client reroute (ACR) if you enable the reads on standby feature.