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, and
structured type columns. Attempts to query these data types will receive
an error (SQL1773N Reason Code 3).
- Explain tools (db2exfmt, db2expln,
and visual explain) 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.