2. Manage a CDC Group Watcher¶
Make sure that the CDC group itself already exists:
2.1. Add a CDC Watcher¶
The CDC group watcher specifies the SQL expression to be executed on a table in the source in order to evaluate some change. The expression must return an increasing value of type integer, date or timestamp. If source table and schema are NULL a select from DUAL is implied. The connection data of the source may be specific per BDomain an is, therefore, separately maintained in DBI_CDCG_WATCH_BD. Use API_DBIADM.ADD_CDCG_WATCH to add the definition.
The example below adds a watcher which returns a new id every 3 hours. The query is executed in Exasol since no table or schema of a source is defined.
Example
1EXECUTE SCRIPT API_DBIADM.ADD_CDCG_WATCH(
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 will add an entry in the table DBI_CDCG_WATCH.
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 use API_DBIADM.MODIFY_CDCG_WATCH. Providing NULL leaves the parameters as is, while a blank (’ ‘) restores the default value. In the example below the SQL expression the watch-id is changed in order to get a new id every 6 hours.
Example
1EXECUTE SCRIPT API_DBIADM.MODIFY_CDCG_WATCH(
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 change can be seen in the entry for the cdc group ‘tst_cdc_grp’ in the table DBI_CDCG_WATCH.
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¶
Remoce a CDC watcher using API_DBIADM.REMOVE_CDCG_WATCH.
Example
1EXECUTE SCRIPT API_DBIADM.REMOVE_CDCG_WATCH(
2 'tst_cdc_grp' -- p_cdc_group
3);
The above command removes the entry for the CDC watcher of the CDC group ‘tst_cdc_grp’ from the table DBI_CDCG_WATCH.