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.