4. Configure the Load from the DB¶
All activities must be executed as application manager.
4.1. Configure BDomain Settings¶
This step is optional since we assume that a new business day starts at midnight.
1execute script API_DBIADM.ADD_BDOMAIN_SETTING(
2 'DIV_WEST' -- p_bdomain
3 ,0 -- p_bdate_start_offset
4);
The settings can be checked in the table DBIADM.DBI_BDOMAIN_SETTINGS.
4.2. Add a CDC Group and Watcher¶
A load should be triggered every 3 hours.
1execute script API_DBIADM.ADD_CDC_GROUP(
2 'EMP_CDC' -- p_cdc_group
3 ,'CDC for employee DB ' -- p_description
4 ,false -- p_bdate_needed
5);
6
7execute script API_DBIADM.ADD_CDCG_WATCH(
8 'EMP_CDC' -- p_cdc_group
9 ,null -- p_src_schema
10 ,null -- p_src_table
11 ,'to_date(add_hours(systimestamp, -3))' -- p_src_watch_id_expr
12);
13
14execute script API_DBIADM.ADD_CDCG_WATCH_BD(
15 'EMP_CDC' -- p_cdc_group
16 ,'DIV_WEST' -- p_bdomain
17 ,null -- p_src_db_name
18 ,null -- p_src_connection
19 ,null -- p_bdate_change_offset
20 ,null -- p_scr_where_clause
21 ,true -- p_active
22 ,null -- p_cdc_min_intv
23);
Check the view DBIADM.DBI_CDCG_WATCH_DETAILS for the results.
4.3. Add a DBI Loader¶
The load should be incremental which means that the UpdatedOn column should be checked if a row has been changed. If UpdatedOn is NULL check CreatedOn instead.
The TSA data should be deleted after 5 days.
The target table should be EMP_PERSON in the schema DBITSA.
For the BDate evaluation, the first load after midnight should count for the previous day while all foloowing loads should count for the current day.
1execute script API_DBIADM.ADD_LOADER(
2 'EMP_PERSON' -- p_ldr_name
3 ,'emp' -- p_src_schema
4 ,'Person' -- p_src_table
5 ,'EMP_CDC' -- p_cdc_group
6 ,'INCR' -- p_cdc_type
7 ,null -- p_src_split_expr
8 ,5 -- p_tsa_retention
9 ,'coalesce(UpdatedOn,CreatedOn)' -- p_src_incr_expr
10 ,false -- p_use_ultimo_shift
11 ,'T' -- p_src_incr_datatype
12 ,false -- p_autofill_missing_days
13 ,null -- p_incr_offset
14);
15
16execute script API_DBIADM.ADD_LOADER_BD(
17 'EMP_PERSON' -- p_ldr_name
18 ,'DIV_WEST' -- p_bdomain
19 ,'red_demo' -- p_src_db_name
20 ,'SQL_SERVER' -- p_src_connection
21 ,'DBITSA' -- p_tsa_schema
22 ,'EMP_PERSON_WEST' -- p_tsa_table
23 ,true -- p_active
24 ,null -- p_bdate_epoch
25 ,0 -- p_status_finished_offset
26 ,null -- p_ext_bdate_sql_expr
27 ,true -- p_bdate_ffdl_prev_day
28 ,null -- p_src_view_gen
29 ,null -- p_src_where_clause
30 ,false -- p_ct_enabled
31 ,null -- p_ct_group
32 ,null -- p_split_max_degree
33 ,false -- p_split_on_load_abort
34 ,99 -- p_priority
35);
Check DBIADBI_LOADER_DETAILS for the results.
4.4. Create DBITSA Table and Refresh Metadata¶
1execute script API_DBIADM.CREATE_TSA_TABLES(
2 'EMP_PERSON' -- p_ldr_name
3 ,'DIV_WEST' -- p_domain
4);
The thus created table can be found as DBITSA.EMP_PERSON.
Finally refresh DBI metadata:
1execute script API_DBIADM.REFRESH;
Important
Do not skip this last step. Otherwise the subsequent creation of the PSA structures as well as other RED features will not work as expected.