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.

Oracle GoldenGate for Oracle database simplifies the administration of the heartbeat table by eliminating the need for GGSCHEMA or HEARTBEATTABLE parameter. To implement this, Extracts and Replicat look in the schema of the ER processes connected user for the heartbeat tables, except for Oracle CDB root Extract. In case of CDB root Extract, GGSCHEMA is used. In case of Autonomous Database (ADB), the user must be GGADMIN.

This command is not valid for PostgreSQL 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 often the heartbeat seed table and heartbeat table are updated. For example, how frequently heartbeat records are generated. The default is 60 seconds.

Consider the following limits in GGSCI, when using DB/400, DB2 z/OS, and DB2 LUW:

The number_in_seconds must be between 0 and 7999. The special value of zero to pause heartbeat will be valid.
The frequency for DB2 /zOS 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.
Minimum frequency is 0, which pauses the heartbeat.

For MySQL, the maximum frequency value is 7999. Minimum value is 0 and default value is 60 seconds.

For SQL Server, the maximum frequency value is 3600. Minimum value is 0 seconds and default value is 60 seconds.

For PostgreSQL, the maximum frequency value is 7999. Minimum and default value is 60 seconds.

RETENTION_TIME

Specifies when heartbeat entries older than the retention time in the history table are purged. The default is 30 days. For DB2/400, DB2 z/OS and DB2 LUW, the maximum retentition time is 31 days and values must be between 1 and 2147483646.

For MySQL, SQL Server, PostgreSQL, the maximum retenion time value is 2147483646 days. Minimum value is 1 day and the default value is 30 days.

PURGE_FREQUENCY

Changes the repeat interval, in days, of the purge heartbeat table.

For GGSCI, this parameter must specify that the number_in_days must be in the interval 1 to 31 for DB2 LUW and DB2 for z/OS.

For MySQL, SQL Server, and PostgreSQL, the maximum purge frequency value is 199 days. Minimum and default value is 1 day.

TARGETONLY | NOTARGETONLY

Valid for Oracle Database, 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.

Valid for Oracle Database, PostgreSQL, and SQL Server. 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.

(PostgreSQL) These options can be used to delete the job and procedure (TARGETONLY) or can be used to add job and procedure (NOTARGETONLY).

Examples

ALTER HEARTBEATTABLE FREQUENCY 60

ALTER HEARTBEATTABLE RETENTION_TIME 30

ALTER HEARTBEATTABLE PURGE_FREQUENCY 1