hadr_spool_limit - HADR log spool limit configuration parameter

The hadr_spool_limit parameter determines the maximum amount of log data that is allowed to be spooled to disk on HADR standby.

Configuration type
Database
Applies to
  • Database server with local and remote clients
  • Database server with local clients
Parameter type
Configurable1
Default [range]
AUTOMATIC [-1 - 2 147 483 647]
Unit of measure
Pages (4 KB)
Upgrade Note
  • If you are upgrading from a Db2® Version 9.8 Fix Pack 4 pureScale environment or earlier, the value of hadr_spol_limit is set to the value on member 0.

The hadr_spool_limit configuration parameter enables log spooling on the HADR standby database. Log spooling allows transactions on the HADR primary to make progress without having to wait for the log replay on HADR standby. Log data that is sent by the primary is then written, or spooled, to disk on the standby if it falls behind in log replay. The standby can later on read the log data from disk. This allows the system to better tolerate either a spike in transaction volume on the primary, or a slow down of log replay (due to the replay of particular type of log records) on the standby.

To disable spooling, set hadr_spool_limit to 0. When spooling is disabled, the standby can be behind the primary up to the size of the log receive buffer. When the buffer is full, it is possible that new transactions on the primary will be blocked because the primary cannot send any more log data to the standby system.

When hadr_spool_limit is set to AUTOMATIC, the effective value is computed as (log_primary + logsecond) * logfilsiz.

A value of -1 means unlimited spooling (as much as supported by the disk space available). If you are using a high value for hadr_spool_limit, you should consider that if there is a large gap between the log position of the primary and log replay on the standby, which might lead to a longer takeover time because the standby cannot assume the role of the new standby until the replay of the spooled logs finishes.

When making use of log spooling, ensure that adequate disk space is provided to the active log path of the standby database. There must be enough disk space to hold the active logs, which is determined by the logprimary, logsecond, logfilsiz, and hadr_spool_limit configuration parameters. If Advanced Log Space Management is enabled, then in addition to active log files there must be enough disk space for extraction log files as well. To determine the current computed spool size (in pages), check the STANDBY_SPOOL_LIMIT field for the MON_GET_HADR table function or the db2pd command with the -hadr option.

Note that making use of log spooling does not compromise the HADR protection provided by the HADR feature. Data from the primary is still replicated in log form to the standby using the specified sync mode; it just takes time to apply (through log replay) the data to the table spaces.

1 This configuration is only used by HADR standby database. The current standby database must be deactivated and activated again, in order for a change to take effect. This configuration can be changed online on the current primary database. The new value on the current primary database takes effect when the primary database changes its role to standby as a result of issuing the TAKEOVER HADR command.