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.