Create and update a heartbeat table

This topic describes how to create and update a heartbeat table when you migrate data from a MySQL database, DB2 LUW database, or PostgreSQL database.

Background

When you create a MySQL, PostgreSQL, or DB2 LUW database, if you select Allow OMS to automatically write heartbeat data into this instance during incremental synchronization. This resolves the problem of high latency when no business data is written in the source database. and the entered database user has the privileges to create and write tables, OMS will create a heartbeat table in the corresponding database and update it every 5 seconds.

If you do not want the heartbeat table to be automatically created and updated by OMS, you can create a database table and configure a crontab task to periodically perform the UPDATE operation on the source database.

Prerequisites

The source database account is granted the privileges to create databases or schemas and the privileges to read and write heartbeat tables.

Note

The DB2 LUW database account has all required privileges by default.

MySQL database

• Grant a database user the privilege to create the drc.heartbeat table:

  GRANT CREATE ON drc.heartbeat TO '<user_name>';
  

• Grant a database user the privilege to write the drc.heartbeat table:

  GRANT INSERT, UPDATE, DELETE ON drc.heartbeat TO '<user_name>';
  

PostgreSQL database

• Grant a database user the privilege to create the drc.heartbeat table:

  GRANT CREATE ON SCHEMA public TO '<user_name>';
  

• Grant a database user the privilege to write the drc.heartbeat table:

  GRANT INSERT, UPDATE, DELETE ON oms_postgres_heartbeat TO '<user_name>';
  

Create and update a heartbeat table in a MySQL database

• Create a heartbeat table

  CREATE DATABASE IF NOT EXISTS drc;
  CREATE TABLE IF NOT EXISTS drc.heartbeat (`id` smallint(6) NOT NULL DEFAULT 1,`ts` int(11) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
  

• Insert data

  INSERT INTO drc.heartbeat VALUES (1,0) ON DUPLICATE KEY UPDATE ts=0;
  

• Update data

  UPDATE drc.heartbeat SET ts=%d WHERE id=1;
  

Create and update a heartbeat table in a DB2 LUW database

• Create a heartbeat table

  CREATE TABLE IF NOT EXISTS drc.heartbeat (id SMALLINT NOT NULL DEFAULT 1, ts INTEGER DEFAULT NULL, PRIMARY KEY (id));
  

• Insert data

  MERGE INTO drc.heartbeat AS ht USING(
    SELECT * FROM TABLE (
      VALUES(1,0)
    )
  ) AS vt(id,ts) ON (ht.id=vt.id)
  WHEN MATCHED THEN 
    UPDATE SET ts=vt.ts
  WHEN NOT MATCHED THEN 
  INSERT (id, ts) VALUES(vt.id,vt.ts); 
  

• Update data

  UPDATE drc.heartbeat SET ts=%d WHERE id=1
  

Create and update a heartbeat table in a PostgreSQL database

• Create a heartbeat table

  CREATE TABLE IF NOT EXISTS public.oms_postgres_heartbeat (id int NOT NULL DEFAULT 1, ts int DEFAULT NULL, PRIMARY KEY (id));
  

• Insert data

  INSERT INTO public.oms_postgres_heartbeat VALUES (1,0) ON CONFLICT (id) DO UPDATE SET ts=0;
  

• Update data

  UPDATE public.oms_postgres_heartbeat SET ts=%d WHERE id=1;