TV 3.7.0
Before one can add bdomain specific to a loader, a bdomain has to be created and bdomain settings have to be added. This can be achieved by following the first chapters of the HowTo Guides below:
Furthermore, a CFI loader also already has to exist. It can be added by following the first chapter of this HowTo Guide:
¶ 1. Add BDomain specifc metadata to a Loader
To add bdomain specific metadata to a CFI loader the script API_CFIADM.ADD_LOADER_BD is used. Several values have to be provided to this script. The name of the loader and bdomain are the first values. Furthermore, the import format, csv, parquet or json, has to be chosen. The file name and bdate pattern and format of the source file have to be defined as well as the name of the target schema and table. In this example a regular expression is used to define the file name pattern starting with 'TEST_person_' followed by the bdate and the time the file has been created. Another important value one has to set, is if the loader should be active or not. One also has to provide the file-encoding format as well as the file row separator used by the source file. Other values that can be set are the column separator and delimiter as well as if a trim function was used if the input file is of the format csv. The variable 'p_file_reject_limit' can be used to set a limit of how many errors are tolerated before a file cannot be loaded while the variable 'p_part_number_pattern' can be used to set the amount of parts a file can be split into if it is too big. 'P_numeric_characters' is used to specify how numeric numbers should be interpreted, for example if a thousand should be depicted as "1.000,00" or "1,000.00". This step does not yet load any data, the script is only used to define how the data should then be loaded and the necessary metadata is created.
¶ Example
EXECUTE SCRIPT API_CFIADM.ADD_LOADER_BD(
'PERSON' -- p_ldr_name
,'TESTING' -- p_bdomain
,'CSV' -- p_imp_type
,'^TEST_person_[\d]{8}_[\d]{14}.csv' -- p_file_name_pattern
,'([1-2][0-9][0-9][0-9])([0-1][0-9][0-3][0-9])' -- p_bdate_pattern
,'YYYYMMDD' -- p_bdate_fmt
,'CFITSA' -- p_tsa_schema
,'TEST_PERSON' -- p_tsa_table
,false -- p_active
,null -- p_bdate_epoch
,0 -- p_status_finished_offset
,'WINDOWS-1252' -- p_file_encoding
,null -- p_file_row_separator
,null -- p_file_csv_null
,1 -- p_file_skip_rows
,null -- p_file_csv_trim
,';' -- p_file_csv_column_separator
,'"' -- p_file_csv_column_delimiter
,null -- p_file_fbv_row_size
,0 -- p_file_reject_limit
,null -- p_part_number_pattern
,false -- p_bdate_ffdl_prev_day
,',.' -- p_numeric_characters
);
The running of the script above will result in a new entry in the table CFIADM.CFI_LOADER_BD.
| ldr _name | bdomain | imp _type | file_name _pattern | bdate_pattern | bdate _fmt | tsa _schema | tsa _table | 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 | ([1-2][0-9][0-9][0-9])([0-1][0-9][0-3][0-9]) | YYYYMMDD | CFITSA | TEST_PERSON | false | WINDOWS-1252 | LF | ; | 0 | ,. |
¶ 2. Modify a CFI BD Loader
If one wants to modify the CFI BD loader parameters, the script API_CFIADM.MODIFY_LOADER_BD can be used. While the name of the loader which should be modified as well as the name of the corresponding bdomain have to be provided and therefore cannot be changed, one can change the rest of the parameters. By providing a null for any of these parameters, the current value will be retained.
In the following example the loader will be set to active and the file row separator will be changed to 'CRLF'. The rest of the parameters should stay the same and therefore a null value is used for all of them.
¶ Example
EXECUTE SCRIPT API_CFIADM.MODIFY_LOADER_BD(
'PERSON' -- p_ldr_name
,'TESTING' -- p_bdomain
,null -- p_imp_type
,null -- p_file_name_pattern
,null -- p_bdate_pattern
,null -- p_bdate_fmt
,null -- p_tsa_schema
,null -- p_tsa_table
,true -- p_active
,null -- p_bdate_epoch
,null -- p_status_finished_offset
,null -- p_file_encoding
,'CRLF' -- p_file_row_separator
,null -- p_file_csv_null
,null -- p_file_skip_rows
,null -- p_file_csv_trim
,null -- p_file_csv_column_separator
,null -- p_file_csv_column_delimiter
,null -- p_file_fbv_row_size
,null -- p_file_reject_limit
,null -- p_part_number_pattern
,null -- p_bdate_ffdl_prev_day
,null -- p_numeric_characters
);
After running the command above the entry for the loader 'PERSON' with the BDomain 'TESTING' in the table CFIADM.CFI_LOADER_BD will have changed as can be seen below.
| ldr _name | bdomain | imp _type | file_name _pattern | bdate_pattern | bdate _fmt | tsa _schema | tsa _table | 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 | ([1-2][0-9][0-9][0-9])([0-1][0-9][0-3][0-9]) | YYYYMMDD | CFITSA | TEST_PERSON | true | WINDOWS-1252 | CRLF | ; | 0 | ,. |
¶ 3. Remove BDomain specific metadata from a Loader
To remove bdomain specific metadata from a loader one only has to provide the name of the loader that should be removed as well as the bdomain that should be removed from the loader to the script API_CFIADM.REMOVE_LOADER_BD as can be seen in the example below.
¶ Example
EXECUTE SCRIPT API_CFIADM.REMOVE_LOADER_BD(
'PERSON' -- p_ldr_name
,'TESTING' -- p_bdomain
);
After issuing the command above the entry belonging to the loader 'PERSON' with the bdomain 'TESTING' will have been removed from the table CFIADM.CFI_LOADER_BD.