3. Configure the Load From the CSV File¶
All activities must be executed as application manager.
3.1. Configure BDomain Settings¶
This step is optional since we assume that a new business day starts at midnight.
1execute script API_CFIADM.ADD_BDOMAIN_SETTING(
2 'DIV_EAST' -- p_bdomain
3 ,0 -- p_bdate_start_offset
4);
The settings can be checked in CFIADM.CFI_BDOMAIN_SETTINGS.
3.2. Add a CFI loader¶
The TSA data should be kept for 3 days.
1execute script API_CFIADM.ADD_LOADER(
2 'EMP_PERSON' -- p_ldr_name
3 ,3 -- p_tsa_retention
4 ,99 -- p_priority
5 ,false -- p_use_ultimo_shift
6);
Check the loader in CFIADM.CFI_LOADER.
3.3. Add BDomain specific metadata to the Loader¶
The BDomain DIV_EAST delivers a UTF-8 file with LF as row separator and - as you can see in the sample file - a semicolon as column separator.
Decimals use German notation (‘,’ as decimal separator).
The TSA schema suffix is ‘EMP’, so the TSA table is created as
CFITSA_EMP.DIV_EAST_EMP_PERSON(CFITSA_<suffix>.<BDOMAIN>_<LDR_NAME>).The BDate is taken from the file name in the format ‘YYYYMMDD’.
Leading and trailing spaces are trimmed.
1execute script API_CFIADM.ADD_LOADER_BD(
2 'EMP_PERSON' -- p_ldr_name
3 ,'DIV_EAST' -- p_bdomain
4 ,'CSV' -- p_imp_type
5 ,'^EMP_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 ,'EMP' -- p_tsa_schema_suffix
9 ,true -- p_active
10 ,null -- p_bdate_epoch
11 ,0 -- p_status_finished_offset
12 ,'UTF-8' -- p_file_encoding
13 ,'LF' -- p_file_row_separator
14 ,null -- p_file_csv_null
15 ,1 -- p_file_skip_rows
16 ,'TRIM' -- 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);
Check the result in CFIADM.CFI_LOADER_BD or - even better - in CFIADM.CFI_LOADER_DETAILS, which also shows the TSA schema and table.
3.4. Add Column Definitions¶
1execute script API_CFIADM.ADD_LOADER_BD_COLUMN(
2 'EMP_PERSON' -- p_ldr_name
3 ,'DIV_EAST' -- p_bdomain
4 ,'EmpId' -- p_col_name
5 ,'int' -- p_col_data_type
6 ,null -- p_col_size
7 ,null -- p_col_scale
8 ,null -- p_col_format
9 ,1 -- p_col_pos
10 ,null -- p_col_fbv_size
11 ,null -- p_col_fbv_start
12 ,null -- p_col_fbv_align
13 ,null -- p_col_fbv_padding
14);
15
16execute script API_CFIADM.ADD_LOADER_BD_COLUMN(
17 'EMP_PERSON' -- p_ldr_name
18 ,'DIV_EAST' -- p_bdomain
19 ,'FirstName' -- p_col_name
20 ,'varchar' -- p_col_data_type
21 ,100 -- p_col_size
22 ,null -- p_col_scale
23 ,null -- p_col_format
24 ,2 -- p_col_pos
25 ,null -- p_col_fbv_size
26 ,null -- p_col_fbv_start
27 ,null -- p_col_fbv_align
28 ,null -- p_col_fbv_padding
29);
30
31execute script API_CFIADM.ADD_LOADER_BD_COLUMN(
32 'EMP_PERSON' -- p_ldr_name
33 ,'DIV_EAST' -- p_bdomain
34 ,'LastName' -- p_col_name
35 ,'varchar' -- p_col_data_type
36 ,100 -- p_col_size
37 ,null -- p_col_scale
38 ,null -- p_col_format
39 ,3 -- p_col_pos
40 ,null -- p_col_fbv_size
41 ,null -- p_col_fbv_start
42 ,null -- p_col_fbv_align
43 ,null -- p_col_fbv_padding
44);
45
46execute script API_CFIADM.ADD_LOADER_BD_COLUMN(
47 'EMP_PERSON' -- p_ldr_name
48 ,'DIV_EAST' -- p_bdomain
49 ,'Salary' -- p_col_name
50 ,'decimal' -- p_col_data_type
51 ,8 -- p_col_size
52 ,2 -- p_col_scale
53 ,'999G999D99' -- p_col_format
54 ,4 -- p_col_pos
55 ,null -- p_col_fbv_size
56 ,null -- p_col_fbv_start
57 ,null -- p_col_fbv_align
58 ,null -- p_col_fbv_padding
59);
60
61execute script API_CFIADM.ADD_LOADER_BD_COLUMN(
62 'EMP_PERSON' -- p_ldr_name
63 ,'DIV_EAST' -- p_bdomain
64 ,'Birthday' -- p_col_name
65 ,'date' -- p_col_data_type
66 ,null -- p_col_size
67 ,null -- p_col_scale
68 ,'YYYYMMDD' -- p_col_format
69 ,5 -- p_col_pos
70 ,null -- p_col_fbv_size
71 ,null -- p_col_fbv_start
72 ,null -- p_col_fbv_align
73 ,null -- p_col_fbv_padding
74);
75
76execute script API_CFIADM.ADD_LOADER_BD_COLUMN(
77 'EMP_PERSON' -- p_ldr_name
78 ,'DIV_EAST' -- p_bdomain
79 ,'Department' -- p_col_name
80 ,'varchar' -- p_col_data_type
81 ,10 -- p_col_size
82 ,null -- p_col_scale
83 ,null -- p_col_format
84 ,6 -- p_col_pos
85 ,null -- p_col_fbv_size
86 ,null -- p_col_fbv_start
87 ,null -- p_col_fbv_align
88 ,null -- p_col_fbv_padding
89);
Six entries, one for each column, are added to CFIADM.CFI_LOADER_BD_COLUMNS.
CFIADM.CFI_TSA_TABLE_DDL shows the CREATE TABLE statement based on these definitions.
Important
The parameter p_col_pos must match the exact column position in the file.
3.5. Create the CFITSA Table¶
1execute script API_CFIADM.CREATE_TSA_TABLES(
2 'EMP_PERSON' -- p_ldr_name
3 ,'DIV_EAST' -- p_bdomain
4);
The table is created as CFITSA_EMP.DIV_EAST_EMP_PERSON.