3. Manage a CFI BDomain Loader

Streams from different sources (BDomains) that are loaded into the same PSA table may have a similar but not identical structure: columns may be missing in some BDomains, or data types may differ. Therefore, the loader specification is defined per BDomain in the table CFIADM.CFI_LOADER_BD. A complete overview, including the resulting TSA schema and table, is shown in CFIADM.CFI_LOADER_DETAILS.

Before you continue, make sure that the BDomain, its CFI settings, and the loader exist:

3.1. Add BDomain-Specific Metadata to a Loader

To add BDomain-specific metadata to a CFI loader, use API_CFIADM.ADD_LOADER_BD. Loader name and BDomain are the key. The most important parameters are:

p_imp_type

File format: CSV or FBV (fixed-width). JSON and Parquet files are converted to CSV by RED-PSS before the import (see the note below), so their loaders use CSV as well.

p_file_name_pattern

Regular expression for the names of the files to be loaded. File names are relative to the input directory; a leading directory part selects a sub-directory of any depth, e.g. ^east/hr/TEST_person_[\d]{8}_[\d]{14}\.csv$ for files in data/input/east/hr/.

p_bdate_pattern and p_bdate_fmt

If the file name contains the business date: regular expression for the BDate part of the file name and its format.

p_tsa_schema_suffix

Mandatory suffix of the target schema: the TSA table is created as CFITSA_<SUFFIX>.<BDOMAIN>_<LDR_NAME>.

p_active

Activates or deactivates the loader for this BDomain.

p_file_encoding, p_file_row_separator, p_file_skip_rows

Character encoding (default UTF-8), row separator (LF, CR, CRLF; default LF), and the number of header rows to skip (default 0).

p_file_csv_column_separator, p_file_csv_column_delimiter, p_file_csv_null, p_file_csv_trim

CSV only: column separator (default ‘,’), column delimiter (default ‘”’), the text to be interpreted as NULL, and an optional trim (LTRIM, RTRIM, TRIM).

p_file_reject_limit

CSV only: number of rows with format errors that are tolerated before the import fails (default 0). Rejected rows are written to the error table <TSA_TABLE>_ERR. For FBV files the reject limit is always 0.

p_part_number_pattern

Regular expression for a part number in the file name, if a delivery is split into several files.

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_numeric_characters

Decimal and thousands separator (two characters), e.g. ‘,.’ for “1.000,00”.

This step does not load any data yet; it only defines how the files are loaded.

Example: Add a CSV Loader for BDomain ‘TESTING’

 1EXECUTE SCRIPT API_CFIADM.ADD_LOADER_BD(
 2     'PERSON'                                          -- p_ldr_name
 3    ,'TESTING'                                         -- p_bdomain
 4    ,'CSV'                                             -- p_imp_type
 5    ,'^TEST_person_[\d]{8}_[\d]{14}\.csv$'             -- p_file_name_pattern
 6    ,'([1-2][0-9][0-9][0-9])([0-1][0-9][0-3][0-9])'    -- p_bdate_pattern
 7    ,'YYYYMMDD'                                        -- p_bdate_fmt
 8    ,'TEST'                                            -- p_tsa_schema_suffix
 9    ,false                                             -- p_active
10    ,null                                              -- p_bdate_epoch
11    ,0                                                 -- p_status_finished_offset
12    ,'WINDOWS-1252'                                    -- p_file_encoding
13    ,null                                              -- p_file_row_separator
14    ,null                                              -- p_file_csv_null
15    ,1                                                 -- p_file_skip_rows
16    ,null                                              -- p_file_csv_trim
17    ,';'                                               -- p_file_csv_column_separator
18    ,'"'                                               -- p_file_csv_column_delimiter
19    ,null                                              -- p_file_fbv_row_size
20    ,0                                                 -- p_file_reject_limit
21    ,null                                              -- p_part_number_pattern
22    ,false                                             -- p_bdate_ffdl_prev_day
23    ,',.'                                              -- p_numeric_characters
24);

The file name pattern matches files like TEST_person_20240101_20240102061500.csv: the business date followed by the creation timestamp. The BDate pattern extracts the first eight digits (20240101).

The command creates a new entry in CFIADM.CFI_LOADER_BD:

LDR_NAME

BDOMAIN

IMP_TYPE

FILE_NAME_PATTERN

BDATE_FMT

TSA_SCHEMA_SUFFIX

ACTIVE

FILE_ENCODING

FILE_ROW_SEPARATOR

FILE_CSV_COLUMN_SEPARATOR

FILE_REJECT_LIMIT

NUMERIC_CHARACTERS

PERSON

TESTING

CSV

^TEST_person_[\d]{8}_[\d]{14}.csv$

YYYYMMDD

TEST

false

WINDOWS-1252

LF

;

0

,.

Note

JSON and Parquet files are converted to CSV by RED-PSS (see the RED-PSS Guide), and the original file is moved to the archive directory:

  • A Parquet file becomes a CSV file without header. Its loader must use the column separator ‘,’ and the column delimiter ‘*’.

  • A JSON array becomes a file with one JSON document per row, i.e. a single column.

Define the file name pattern for the resulting .csv file.

3.2. Modify a CFI BDomain Loader

Use API_CFIADM.MODIFY_LOADER_BD to modify a BDomain loader. Loader name and BDomain identify the entry and cannot be changed; all other parameters can. NULL leaves a parameter unchanged, while a blank (’ ‘) restores its default value. In the following example the loader is activated and the row separator is changed to ‘CRLF’.

Example: Activate the Loader and Change the Row Separator

 1EXECUTE SCRIPT API_CFIADM.MODIFY_LOADER_BD(
 2     'PERSON'                                          -- p_ldr_name
 3    ,'TESTING'                                         -- p_bdomain
 4    ,null                                              -- p_imp_type
 5    ,null                                              -- p_file_name_pattern
 6    ,null                                              -- p_bdate_pattern
 7    ,null                                              -- p_bdate_fmt
 8    ,null                                              -- p_tsa_schema_suffix
 9    ,true                                              -- p_active
10    ,null                                              -- p_bdate_epoch
11    ,null                                              -- p_status_finished_offset
12    ,null                                              -- p_file_encoding
13    ,'CRLF'                                            -- p_file_row_separator
14    ,null                                              -- p_file_csv_null
15    ,null                                              -- p_file_skip_rows
16    ,null                                              -- p_file_csv_trim
17    ,null                                              -- p_file_csv_column_separator
18    ,null                                              -- p_file_csv_column_delimiter
19    ,null                                              -- p_file_fbv_row_size
20    ,null                                              -- p_file_reject_limit
21    ,null                                              -- p_part_number_pattern
22    ,null                                              -- p_bdate_ffdl_prev_day
23    ,null                                              -- p_numeric_characters
24);

The entry for loader ‘PERSON’ and BDomain ‘TESTING’ in CFIADM.CFI_LOADER_BD has changed:

LDR_NAME

BDOMAIN

IMP_TYPE

FILE_NAME_PATTERN

BDATE_FMT

TSA_SCHEMA_SUFFIX

ACTIVE

FILE_ENCODING

FILE_ROW_SEPARATOR

FILE_CSV_COLUMN_SEPARATOR

FILE_REJECT_LIMIT

NUMERIC_CHARACTERS

PERSON

TESTING

CSV

^TEST_person_[\d]{8}_[\d]{14}.csv$

YYYYMMDD

TEST

true

WINDOWS-1252

CRLF

;

0

,.

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

3.3. Remove BDomain-Specific Metadata from a Loader

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

Example: Remove the Loader for BDomain ‘TESTING’

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

The entry for loader ‘PERSON’ and BDomain ‘TESTING’ is removed from CFIADM.CFI_LOADER_BD.