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 the table CFIADM.CFI_BDOMAIN_SETTINGS.
3.2. Add a CFI loader¶
TSA loads 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);
To check if the loader was added inspect table CFIADM.CFI_LOADER.
3.3. Add BDomain specific metadata to the Loader¶
The BDomain DIV_EAST delivers a UTF-8 file delimited with LF and - as you can see in the sample file - a semicolon is used as column delimiter.
Decimals use German notation.
The target table should be EMP_PERSON in the schema CFITSA.
The BDate will be taken as a substring from the filename, the format must be ‘YYYYMMDD’.
Leading and trailing spaces will be trimmed automatically.
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 ,'CFITSA' -- p_tsa_schema
9 ,'EMP_PERSON_EAST' -- p_tsa_table
10 ,true -- p_active
11 ,null -- p_bdate_epoch
12 ,0 -- p_status_finished_offset
13 ,'UTF-8' -- p_file_encoding
14 ,'LF' -- p_file_row_separator
15 ,null -- p_file_csv_null
16 ,1 -- p_file_skip_rows
17 ,'TRIM' -- p_file_csv_trim
18 ,';' -- p_file_csv_column_separator
19 ,'"' -- p_file_csv_column_delimiter
20 ,null -- p_file_fbv_row_size
21 ,0 -- p_file_reject_limit
22 ,null -- p_part_number_pattern
23 ,false -- p_bdate_ffdl_prev_day
24 ,',.' -- p_numeric_characters
25);
Check the result in the table CFIADM.CFI_LOADER_BD or - even better - in CFIADM.CFI_LOADER_DETAILS.
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, will have been added to the table CFIADM.CFI_LOADER_BD_COLUMNS. In addition, in CFIADM.CFI_TSA_TABLE_DDL you can find the create table statement based on the above definitions.
Important
The parameter p_col_pos has to be match the exact column position in the file.
3.5. Create the CFITSA Table and Refresh Metadata¶
1execute script API_CFIADM.CREATE_TSA_TABLES(
2 'EMP_PERSON' -- p_ldr_name
3 ,'DIV_EAST' -- p_bdomain
4);
The thus created table can be found as CFITSA.EMP_PERSON.
Finally refresh DBI metadata:
1execute script API_CFIADM.REFRESH;
Important
Do not skip this last step. Otherwise the subsequent creation of the PSA structures as well as other RED features will not work as expected.