TV 3.7.0
To be able to add a DBI loader a CDC watcher connected to a bdomain has to exist. This can be achieved by following the HowTo Guide below and all the linked Prerequisites:
¶ 1. Add a DBI Loader
Adding a DBI loader is done by using the script API_DBIADM.ADD_LOADER. First one has to provide a name for the loader as well as the name of the cdc group that just has been created. Furthermore, the schema name and object name which should be loaded have to be provided. It is important that the names of the source system are used for these variables. Additionally one can specifiy if the object's data should be loaded incremental (INCR) or full (FULL), meaning all records, for each load cycle. If an incremental load is chosen the values for the variables p_scr_incr_expr, p_scr_incr_datatype and p_inc_offset also have to be set. While the first is used to specify which column should be used to detect changed records of a column which has to be either of type integer (I), date (D) or timestamp (T), the second clarifies of which type the column is I, D or T. The third is used to specify an offset for incremental loads if necessary.
In this example the intention is to first check if any entries have been updated and if so trigger the loader, but also trigger the loader if nothing has been updated, but a new entry has been created. This can be done by using the function coalasce(x1,x2). This leads to the loader first checking if there are any changes in column x1 and only check if there are changes in column x2 if the first column returned 'null'. The last values that have to be provided are the TSA retention period, meaning after how many days the data should be removed from the TSA tables, if ultimo shift should be used and if missing days should automatically be filled with fake data. If the last value is set to false and a dataset should be added for each day, however, due to some reason no dataset has been added, no more data can be added until the error has been solved. If it is set to true on the other hand, no error is generated from a missing dataset, instead fake data is added and if data is being sent the next day, that data can be added to the table as if nothing has happened the day before.
¶ Example
EXECUTE SCRIPT API_DBIADM.ADD_LOADER(
'tst_pers_ldr' -- p_ldr_name
,'testDB' -- p_src_schema
,'Person' -- p_src_table
,'tst_cdc_grp' -- p_cdc_group
,'INCR' -- p_cdc_type
,null -- p_src_split_expr
,5 -- p_tsa_retention
,'coalesce(UpdatedOn,CreatedOn)' -- p_src_incr_expr
,true -- p_use_ultimo_shift
,'T' -- p_src_incr_datatype
,true -- p_autofill_missing_days
,null -- p_incr_offset
);
The script above adds an entry to the table DBIADM.DBI_LOADER as can be seen below.
| ldr_name | src_schema | src_table | cdc_group_type | cdc_group | cdc_type | src_split_expr | tsa_retention | src_incr_expr | use_ultimo_shift | src_incr_datatype | autofill_missing_days | incr_offset |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| TST_PERS_LDR | testDB | Person | WATCH | tst_cdc_grp | INCR | (null) | 5 | coalesce(UpdatedOn,CreatedOn) | true | T | true | (null) |
¶ 2. Modify a DBI Loader
To modify any of the DBI loader variables, one only has to provide the name of the loader that should be modified as well as the new values for the variables that should be changed to the script API_DBIADM.MODIFY_LOADER. By providing a null value for all the other variables, the current saved value will be kept.
In the example below the values for using ultimo shift and autofilling missing days is changed to false, while the rest of the variables stay the same.
¶ Example
EXECUTE SCRIPT API_DBIADM.MODIFY_LOADER(
'TST_PERS_LDR' -- p_ldr_name
,null -- p_src_schema
,null -- p_src_table
,null -- p_cdc_group
,null -- p_cdc_type
,null -- p_src_split_expr
,null -- p_tsa_retention
,null -- p_src_incr_expr
,false -- p_use_ultimo_shift
,null -- p_src_incr_datatype
,false -- p_autofill_missing_days
,null -- p_incr_offset
);
By issuing the command above the entry for the loader 'TST_PERS_LDR' in table DBIADM.DBI_LOADER is going to be changed as follows:
| ldr_name | src_schema | src_table | cdc_group_type | cdc_group | cdc_type | src_split_expr | tsa_retention | src_incr_expr | use_ultimo_shift | src_incr_datatype | autofill_missing_days | incr_offset |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| TST_PERS_LDR | testDB | Person | WATCH | tst_cdc_grp | INCR | (null) | 5 | coalesce(UpdatedOn,CreatedOn) | false | T | false | (null) |
¶ 3. Remove DBI Loader
To remove a DBI loader one only has to provide the name of the loader that should be removed to the script API_DBIADM.REMOVE_LOADER as can be seen in the example below.
¶ Example
EXECUTE SCRIPT API_DBIADM.REMOVE_LOADER(
'TST_PERS_LDR' -- p_ldr_name
);
After the command above is run, there will no longer be an entry for the loader with the name 'TST_PERS_LDR' in table DBIADM.DBI_LOADER .