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), or INFU (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), or L (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 for I.

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.