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 DBIADM.DBI_BDOMAIN_SETTINGS.
4.2. Add a CDC Group and Watcher¶
A load should be triggered once a day at 3:00: the watch expression returns a new value every day at this time.
1execute script API_DBIADM.ADD_CDC_GROUP(
2 'EMP_CDC' -- p_cdc_group
3 ,'CDC for employee DB' -- p_description
4);
5
6execute script API_DBIADM.ADD_CDCG(
7 'EMP_CDC' -- p_cdc_group
8 ,null -- p_src_schema
9 ,null -- p_src_table
10 ,'to_date(add_hours(systimestamp, -3))' -- p_src_watch_id_expr
11);
12
13execute script API_DBIADM.ADD_CDCG_BD(
14 'EMP_CDC' -- p_cdc_group
15 ,'DIV_WEST' -- p_bdomain
16 ,null -- p_src_db_name
17 ,null -- p_src_connection
18 ,null -- p_bdate_change_offset
19 ,null -- p_src_where_clause
20 ,true -- p_active
21 ,null -- p_cdcg_min_intv
22);
Check the results in DBIADM.DBI_CDCG_DETAILS.
4.3. Add a DBI Loader¶
The load should be incremental: changed rows are detected by the column UpdatedOn, or CreatedOn for rows that have never been updated.
The TSA data should be deleted after 5 days.
Without a TSA schema suffix, the TSA table is created as
DBITSA.DIV_WEST_EMP_PERSON(DBITSA.<BDOMAIN>_<LDR_NAME>).The source is read via the connection RED_DEMO_MSSQL created in the prerequisites.
For the BDate evaluation, the first load after midnight counts for the previous day, all following loads 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 ,null -- p_incr_offset
13 ,null -- p_incr_char_list_sql
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 ,'RED_DEMO_MSSQL' -- p_src_connection
21 ,null -- p_tsa_schema_suffix
22 ,true -- p_active
23 ,null -- p_bdate_epoch
24 ,0 -- p_status_finished_offset
25 ,null -- p_ext_bdate_sql_expr
26 ,true -- p_bdate_ffdl_prev_day
27 ,null -- p_src_view_gen
28 ,null -- p_src_where_clause
29 ,false -- p_ct_enabled
30 ,null -- p_ct_group
31 ,null -- p_split_max_degree
32 ,false -- p_split_on_load_abort
33 ,99 -- p_priority
34 ,1 -- p_ct_min_intv
35);
Check the results in DBIADM.DBI_LOADER_DETAILS.
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_bdomain
4);
The table is created as DBITSA.DIV_WEST_EMP_PERSON.
Finally refresh the DBI metadata, which provides the column lists for the import statements:
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.