Reads on standby restrictions

The High Availability and Disaster Recovery (HADR) reads on standby feature allows you to run read-only workloads on an HADR active standby database. In addition to the read-only restriction, this feature has the following limitations that you should be aware of.

• Write operations are not permitted on the standby . In this context, a write operation is an operation that modifies permanent database objects like catalogs, tables, and indexes. Write operations on the standby return an error (SQL1773N Reason Code 5). In particular, you cannot perform any operation that results in the generation of log records on the standby.
• The standby is inaccessible to user connections during the replay of DDL log records or maintenance operations (the replay-only window). For more information, see Replay-only window on the active standby database.
• The standby is inaccessible to user connections when it is in the local catchup state. Clients attempting to connect this state will receive an error (SQL1776N Reason Code 1).
• Only the Uncommitted Read (UR) isolation level is supported on the standby. Applications, statements, or sub-statements requesting an higher isolation level will receive an error (SQL1773N Reason Code 1). For more information, see Isolation level on the active standby database.
• 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 using the db2audit tool.
• Declared temporary tables (DGTTs) are not supported on the standby. Attempts to create or access them on the standby will receive an error (SQL1773N Reason Code 4).
• Created temporary tables (CGTTs) can only be created on the primary database and their definition is replicated to the standby. However, access to CGTTs is not supported on the standby, attempts to create or access them will receive an error (SQL1773N Reason Code 4).
• The creation of created temporary tables (CGTTs) on the primary will trigger the replay-only window on the standby.
• Not Logged Initially (NLI) tables cannot be accessed on the standby. Applications attempting to read an NLI table on the standby will receive an error (SQL1477N).
• Queries on the standby database can only make use of SMS system temporary table spaces. A query executed on the standby database that uses DMS system temporary table spaces may result in an error (SQL1773N Reason Code 5).
• XML and large object (LOB) data must be inline to be successfully queried, otherwise an error is returned (SQL1773N Reason Code 3).
• The following data cannot be queried: long field (LF), a distinct type based on one of these data types, structured type columns, and varying-length string data (that is, data that resides in extended rows). Attempts to query these data types will receive an error (SQL1773N Reason Code 3).
• Explain tools (db2exfmt and db2expln) and db2batch tools are not supported on the standby (SQL1773N Reason Code 5). If you want to analyze performance of the read-only workload, you should first run these tools on the primary, make the necessary optimizations to the workload on the primary, and then move the optimized workload to the standby.
• Explicit binding and rebinding and implicit rebinding of packages is not supported on the standby. Attempts to run static packages that refer to invalidated objects, as well as implicit rebinds of those packages, will result in an error (SQL1773N Reason Codes 5 and 6, respectively). Instead, you should bind packages on the primary and run the package on the standby after the change has been replicated 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 perform well after takeover), you must do so manually.
• Workload manager (WLM) DDL statements on the primary are replayed on the standby, but they will not be effective on the standby; however, any definitions that exist in the database backup that was used to set up the standby will be active on the read enabled standby.
• The 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.
• The standby cannot be configured as a Federation Server.
• 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.