2. Manage a CDC Group Watcher

Make sure that the CDC group already exists: Add a CDC Group.

2.1. Add a CDC Watcher

The CDC group watcher defines the SQL expression that is executed regularly to detect a change in the source (the watch ID). The expression must return an increasing value of type integer, date, or timestamp; whenever the value changes, all loaders of the CDC group are queued. If source schema and table are NULL, the expression is evaluated in Exasol (select from DUAL). The connection to the source may differ per BDomain and is therefore maintained separately in DBIADM.DBI_CDCG_BD (BDomain metadata of a watcher).

Use API_DBIADM.ADD_CDCG to add the watcher.

The following example adds a watcher that returns a new value every day, with the day change at 3:00. The expression is evaluated in Exasol, since no source schema or table is defined.

Example: Add a Daily Watcher

1EXECUTE SCRIPT API_DBIADM.ADD_CDCG(
2    'tst_cdc_grp'                             -- p_cdc_group
3   ,null                                      -- p_src_schema
4   ,null                                      -- p_src_table
5   ,'to_date(add_hours(systimestamp, -3))'    -- p_src_watch_id_expr
6);

This command creates a new entry in the table DBIADM.DBI_CDCG.

CDC_GROUP

SRC_SCHEMA

SRC_TABLE

SRC_WATCH_ID_EXPR

tst_cdc_grp

(null)

(null)

to_date(add_hours(systimestamp, -3))

2.2. Modify a CDC Watcher

To modify a watcher, use API_DBIADM.MODIFY_CDCG. NULL leaves a parameter unchanged, while a blank (’ ‘) restores its default value. In the following example the day change of the watch ID is moved to 6:00.

Example: Move the Day Change to 6:00

1EXECUTE SCRIPT API_DBIADM.MODIFY_CDCG(
2    'tst_cdc_grp'                             -- p_cdc_group
3   ,null                                      -- p_src_schema
4   ,null                                      -- p_src_table
5   ,'to_date(add_hours(systimestamp, -6))'    -- p_src_watch_id_expr
6);

The entry for the CDC group ‘tst_cdc_grp’ in DBIADM.DBI_CDCG has changed:

CDC_GROUP

SRC_SCHEMA

SRC_TABLE

SRC_WATCH_ID_EXPR

tst_cdc_grp

(null)

(null)

to_date(add_hours(systimestamp, -6))

2.3. Remove a CDC Watcher

Remove a CDC watcher using API_DBIADM.REMOVE_CDCG.

Example: Remove the Watcher

1EXECUTE SCRIPT API_DBIADM.REMOVE_CDCG(
2    'tst_cdc_grp'                             -- p_cdc_group
3);

The watcher of the CDC group ‘tst_cdc_grp’ is removed from DBIADM.DBI_CDCG.