6. Manage BDomain Metadata of a DBI Loader

Make sure that the DBI loader exists: Add a DBI Loader.

6.1. Add BDomain-Specific Metadata to a Loader

To add BDomain-specific metadata to a loader, use API_DBIADM.ADD_LOADER_BD. The most important parameters are:

p_src_db_name and p_src_connection

Source database and the Exasol connection to it (see EXA_ALL_CONNECTIONS).

p_tsa_schema_suffix

Optional suffix of the target schema: the TSA table is created as DBITSA.<BDOMAIN>_<LDR_NAME> or, with a suffix, as DBITSA_<SUFFIX>.<BDOMAIN>_<LDR_NAME>.

p_active

Activates or deactivates the loader for this BDomain.

p_ext_bdate_sql_expr

Optional SQL expression that determines the business date of a delivery (see Control-BDate).

p_bdate_ffdl_prev_day

If true, the first full load of a day (after midnight) uses the previous day as BDate (default true).

p_src_where_clause

Optional WHERE condition on the source object, e.g. to separate BDomains.

p_ct_enabled, p_ct_group, p_ct_min_intv

Change tracking: activation, CT group, and minimum interval in minutes between two checks (default 1).

p_split_max_degree and p_split_on_load_abort

Maximum degree of parallelism of the extraction, and whether to split the extraction if it fails due to a connection abort (default false).

p_priority

Priority of the loader; lower values are loaded first (default 99).

Example: Add the Loader for BDomain ‘TESTING’

 1EXECUTE SCRIPT API_DBIADM.ADD_LOADER_BD(
 2    'TST_PERS_LDR'   -- p_ldr_name
 3   ,'TESTING'        -- p_bdomain
 4   ,'TESTING'        -- p_src_db_name
 5   ,'SQL_SERVER'     -- p_src_connection
 6   ,null             -- p_tsa_schema_suffix
 7   ,false            -- p_active
 8   ,null             -- p_bdate_epoch
 9   ,0                -- p_status_finished_offset
10   ,null             -- p_ext_bdate_sql_expr
11   ,false            -- p_bdate_ffdl_prev_day
12   ,null             -- p_src_view_gen
13   ,null             -- p_src_where_clause
14   ,false            -- p_ct_enabled
15   ,null             -- p_ct_group
16   ,null             -- p_split_max_degree
17   ,false            -- p_split_on_load_abort
18   ,99               -- p_priority
19   ,2                -- p_ct_min_intv
20);

This command creates a new entry in DBIADM.DBI_LOADER_BD. Some of its columns:

LDR_NAME

BDOMAIN

SRC_DB_NAME

SRC_CONNECTION

TSA_SCHEMA_SUFFIX

ACTIVE

BDATE_EPOCH

SPLIT_ON_LOAD_ABORT

PRIORITY

CT_MIN_INTV

TST_PERS_LDR

TESTING

TESTING

SQL_SERVER

(null)

false

(null)

false

99

2

The resulting TSA schema and table (DBITSA.TESTING_TST_PERS_LDR) are shown in DBIADM.DBI_LOADER_DETAILS.

6.2. Modify BDomain-Specific Metadata of a Loader

To modify the BDomain-specific metadata of a loader, use API_DBIADM.MODIFY_LOADER_BD. NULL leaves a parameter unchanged, while a blank (’ ‘) restores its default value. In the following example only the loader is activated.

Example: Activate the Loader

 1EXECUTE SCRIPT API_DBIADM.MODIFY_LOADER_BD(
 2    'TST_PERS_LDR'   -- p_ldr_name
 3   ,'TESTING'        -- p_bdomain
 4   ,null             -- p_src_db_name
 5   ,null             -- p_src_connection
 6   ,null             -- p_tsa_schema_suffix
 7   ,true             -- p_active
 8   ,null             -- p_bdate_epoch
 9   ,null             -- p_status_finished_offset
10   ,null             -- p_ext_bdate_sql_expr
11   ,null             -- p_bdate_ffdl_prev_day
12   ,null             -- p_src_view_gen
13   ,null             -- p_src_where_clause
14   ,null             -- p_ct_enabled
15   ,null             -- p_ct_group
16   ,null             -- p_split_max_degree
17   ,null             -- p_split_on_load_abort
18   ,null             -- p_priority
19   ,null             -- p_ct_min_intv
20);

The entry for the loader ‘TST_PERS_LDR’ and the BDomain ‘TESTING’ in DBIADM.DBI_LOADER_BD has changed:

LDR_NAME

BDOMAIN

SRC_DB_NAME

SRC_CONNECTION

TSA_SCHEMA_SUFFIX

ACTIVE

BDATE_EPOCH

SPLIT_ON_LOAD_ABORT

PRIORITY

CT_MIN_INTV

TST_PERS_LDR

TESTING

TESTING

SQL_SERVER

(null)

true

(null)

false

99

2

To switch a BDomain loader on or off without changing other settings, use API_DBIADM.TOGGLE_LOADER_BD.

6.3. Remove BDomain-Specific Metadata from a Loader

To remove the BDomain-specific metadata of a loader, use API_DBIADM.REMOVE_LOADER_BD.

Example: Remove the Loader for BDomain ‘TESTING’

1EXECUTE SCRIPT API_DBIADM.REMOVE_LOADER_BD(
2    'TST_PERS_LDR'   -- p_ldr_name
3   ,'TESTING'        -- p_bdomain
4);

The entry for the loader ‘TST_PERS_LDR’ and the BDomain ‘TESTING’ is removed from DBIADM.DBI_LOADER_BD.