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:

• Schema and table name (or view name) of the source object

  • Mind the case sensitivity of the source system!

• CDC group

• CDC type:

  • FULL (full load)

  • INCR (incremental load)

  • CT (change tracking)

  • INFU (incremental-full, incremental load but consider the data as full load dataset for later PSA delta logic)

In case of an incremental load p_scr_incr_expr, p_scr_incr_datatype and p_inc_offset also have to be set as well.

• incremental expression: must return a monotonically increaing value of type integer, date or timestamp

• incremental data type: I (integer), D (date) or T (timestamp)

• incremental offset: offset back in time in order to load overlapping time frames (sometimes it might not be clear if already extracted data have been changed again)

Other important parameters (see API_DBIADM.ADD_LOADER):

• TSA retention: retention in days before TSA data are cleaned out

• Ultimo shift

• autofill missing days

• incremental offset

In the example below 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’. TSA retention period is set to 5 days and ultimo shift should be used.

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 DBI_LOADER.

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	truw	(null)

4.2. Modify a DBI Loader

Modify a DBI Loader using API_DBIADM.MODIFY_LOADER. Providing NULL leaves the parameters as is, while a blank (’ ‘) restores the default value. In the example below the values for using ultimo shift and autofilling missing days is changed to false, while the rest of the parameters 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 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)

4.3. Remove DBI Loader

To remove a DBI loader use API_DBIADM.REMOVE_LOADER.

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 DBI_LOADER .