2.27 ALTER HEARTBEATTABLE

Use ALTER HEARTBEATTABLE to alter existing seed, heartbeat, and history table options that you set with ADD HEARTBEATTABLE.

This command requires a DBLOGIN. On a CDB database, a PDB login is required.

Valid for Oracle, Db2 z/OS, Db2 LUW, Db2 for i, MySQL, PostgreSQL, SQL Server. This command is not valid for TimesTen and Teradata.

Syntax

ALTER HEARTBEATTABLE
[, FREQUENCY number_in_seconds]
[, RETENTION_TIME number_in_days] |
[, PURGE_FREQUENCY number_in_days]
[, TARGETONLY | NOTARGETONLY]
FREQUENCY number_in_seconds

Specifies how frequently heartbeat records are generated. The default is 60 seconds.

Consider the following limits:

  • For Oracle Database, the minimum value is 0 and the maximum is 7999.

  • For DB2 for i Series, the minimum value is 0 and the maximum is 7999.

  • For DB2 LUW and DB2 z/OS, the minimum value is 60 and the maximum is 7999.

  • The frequency for DB2 z/OS and DB2 LUW must be a multiple of 60 for values less than 3600 and multiples for 3600 for values greater or equal to 3600.

  • For MySQL, the minimum value is 0 and the maximum is 7999.

  • For SQL Server, the minimum value is 10 and the maximum is 7999. It supports 0, which disables the SQL Server Agent Heartbeat table UPDATE job, but can only be set with ALTER HEARTBEATTABLE.

  • For PostgreSQL, the minimum value is 60 and the maximum is 7999.

  • Databases that support setting FREQUENCY to 0 will pause the heartbeat record scheduler.

RETENTION_TIME

Specifies that heartbeat entries older than the retention time in the heartbeat history table are purged. The default is 30 days.

The minimum value for all databases is 1 and the maximum is 2147483646.

PURGE_FREQUENCY

Specifies how often the purge scheduler is run to delete table entries that are older than the retention time from the heartbeat history table. The default is 1 day.

For DB2 LUW and DB2 z/OS, the minimum value is 1 and the maximum is 31.

For all other supported databases, the minimum value is 1 and the maximum value is 199.

TARGETONLY | NOTARGETONLY

Valid for Oracle Database, DB2 for i Series, DB2 LUW, DB2 z/OS, MySQL, PostgreSQL, and SQL Server.

TARGETONLY modifies existing heartbeat seed and heartbeat tables by disabling supplemental logging on both tables. It drops the existing scheduler job for updating the heartbeat table.

NOTARGETONLY modifies existing heartbeat seed and heartbeat tables by enabling supplemental logging on both tables. It creates a new scheduler job for updating the heartbeat table.

Examples

ALTER HEARTBEATTABLE FREQUENCY 60
ALTER HEARTBEATTABLE RETENTION_TIME 30
ALTER HEARTBEATTABLE PURGE_FREQUENCY 1
ALTER HEARTBEATTABLE NOTARGETONLY