4. Manage a DBI Loader¶
Make sure that a suitable CDC group exists: Add a CDC Group.
4.1. Add a DBI Loader¶
Add a DBI loader using API_DBIADM.ADD_LOADER.
Besides the loader name, the following parameters must be specified:
- p_src_schema and p_src_table
Schema and table (or view) of the source object. Mind the case sensitivity of the source system.
- p_cdc_group
The CDC group whose watcher triggers the loader.
- p_cdc_type
FULL(full load),INCR(incremental load),CT(change tracking), orINFU(incremental load whose result is treated as a full delivery by the PSA delta logic).
For incremental loads the following parameters are needed as well:
- p_src_incr_expr
Expression on the source object that returns a monotonically increasing value, e.g. a modification timestamp.
- p_src_incr_datatype
Data type of the expression:
I(integer),D(date),T(timestamp), orL(character list, see p_incr_char_list_sql).- p_incr_offset
Overlap with the previous load, so that records changed again in the meantime are loaded as well: in days for
D/T, as an integer forI.
Further parameters:
- p_tsa_retention
Number of days the data remains in the TSA table (default 10).
- p_use_ultimo_shift
If true, the BDate of a business ultimo is shifted to the calendar ultimo (default false).
- p_src_split_expr
Optional degree of parallelism for the extraction.
In the following example the loader reads all records that have been created or updated since the last load. The expression coalesce(UpdatedOn, CreatedOn) returns the update timestamp of a record or, if the record has never been updated, its creation timestamp. One day of overlap is loaded again each time. The TSA retention is 5 days and the ultimo shift is used.
Example: Add an Incremental Loader
1EXECUTE SCRIPT API_DBIADM.ADD_LOADER(
2 'tst_pers_ldr' -- p_ldr_name
3 ,'testDB' -- p_src_schema
4 ,'Person' -- p_src_table
5 ,'tst_cdc_grp' -- 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 ,true -- p_use_ultimo_shift
11 ,'T' -- p_src_incr_datatype
12 ,1 -- p_incr_offset
13 ,null -- p_incr_char_list_sql
14);
This command creates a new entry in the table DBIADM.DBI_LOADER (the loader name is converted to upper case):
LDR_NAME |
SRC_SCHEMA |
SRC_TABLE |
CDC_GROUP |
CDC_TYPE |
SRC_SPLIT_EXPR |
TSA_RETENTION |
SRC_INCR_EXPR |
USE_ULTIMO_SHIFT |
SRC_INCR_DATATYPE |
INCR_OFFSET |
|---|---|---|---|---|---|---|---|---|---|---|
TST_PERS_LDR |
testDB |
Person |
tst_cdc_grp |
INCR |
(null) |
5 |
coalesce(UpdatedOn,CreatedOn) |
true |
T |
1 |
4.2. Modify a DBI Loader¶
Modify a DBI loader using API_DBIADM.MODIFY_LOADER. NULL leaves a parameter unchanged, while a blank (’ ‘) restores its default value. In the following example the ultimo shift is deactivated; all other parameters stay the same.
Example: Deactivate the Ultimo Shift
1EXECUTE SCRIPT API_DBIADM.MODIFY_LOADER(
2 'TST_PERS_LDR' -- p_ldr_name
3 ,null -- p_src_schema
4 ,null -- p_src_table
5 ,null -- p_cdc_group
6 ,null -- p_cdc_type
7 ,null -- p_src_split_expr
8 ,null -- p_tsa_retention
9 ,null -- p_src_incr_expr
10 ,false -- p_use_ultimo_shift
11 ,null -- p_src_incr_datatype
12 ,null -- p_incr_offset
13 ,null -- p_incr_char_list_sql
14);
The entry for the loader ‘TST_PERS_LDR’ in DBIADM.DBI_LOADER has changed:
LDR_NAME |
SRC_SCHEMA |
SRC_TABLE |
CDC_GROUP |
CDC_TYPE |
SRC_SPLIT_EXPR |
TSA_RETENTION |
SRC_INCR_EXPR |
USE_ULTIMO_SHIFT |
SRC_INCR_DATATYPE |
INCR_OFFSET |
|---|---|---|---|---|---|---|---|---|---|---|
TST_PERS_LDR |
testDB |
Person |
tst_cdc_grp |
INCR |
(null) |
5 |
coalesce(UpdatedOn,CreatedOn) |
false |
T |
1 |
4.3. Remove a DBI Loader¶
To remove a DBI loader, use API_DBIADM.REMOVE_LOADER.
Example: Remove the Loader
1EXECUTE SCRIPT API_DBIADM.REMOVE_LOADER(
2 'TST_PERS_LDR' -- p_ldr_name
3);
The entry of the loader ‘TST_PERS_LDR’ is removed from DBIADM.DBI_LOADER.