5. Configure the PSA Load Process

All activities must be executed as application manager.

5.1. Configure the PSA Target Table

  • Clearly, the employees data are master data and, therefore, must be configured as type-2.

  • The PSA table should be created in the schema PSA_EMP and the table should be named PERSON.

  • Deleted rows in the source system should be terminated by BDate.

    • For the CSV load we always expect a full dataset. Therefore, a missing row means a deleted row.

    • For DB loads we find deleted rows by the column ‘Deleted’ (being set to treu).

1execute script API_REPPSA.ADD_OBJECT(
2    'PSA_EMP'                            -- p_psa_obj_schema
3   ,'PERSON'                             -- p_psa_obj_table
4   ,2                                    -- p_scd_type
5   ,null                                 -- p_comment
6   ,'B'                                  -- p_del_method
7   ,ARRAY('CFITSA,EMP_PERSON_EAST,DIV_EAST', 'DBITSA,EMP_PERSON_WEST,DIV_WEST')  -- ARRAY p_tsa_tables
8);

5.2. Configure Key Columns

Since we do not use HT lookup tables in this example it is sufficient (and mandatory, of course) to define the BSK. The column ‘EmpId’ in the DB as well in the CSV file obvoiusly is the primary key and can, therefore, be used as the business key.

 1execute script API_REPPSA.MODIFY_TSA_KEY_COLUMNS(
 2     'CFITSA'                -- p_obj_schema
 3    ,'EMP_PERSON_EAST'       -- p_obj_table
 4    ,'BSK'                   -- p_type
 5    ,ARRAY('1,EmpId')        -- ARRAY p_key_cols
 6    ,true                    -- p_reset_flags
 7);
 8
 9execute script API_REPPSA.MODIFY_TSA_KEY_COLUMNS(
10     'DBITSA'                -- p_obj_schema
11    ,'EMP_PERSON_WEST'       -- p_obj_table
12    ,'BSK'                   -- p_type
13    ,ARRAY('1,EmpId')        -- ARRAY p_key_cols
14    ,true                    -- p_reset_flags
15);

5.3. Configure the BDate Expression

For the file load from division EAST the BDate is set a part of the filename and, therefore, Control-BDate and Effective-BDate are the same. For the DB loads from WEST the BDate must be evaluated from the columns CreatedOn and UpdatedOn.

1execute script API_REPPSA.MODIFY_TSA_BDATE_EXPR(
2     'DBITSA'                               -- p_obj_schema
3    ,'EMP_PERSON_WEST'                      -- p_obj_table
4    ,'coalesce(to_date(@2), to_date(@1))'   -- p_bdate_expr
5    ,ARRAY('1,CreatedOn','2,UpdatedOn')     -- ARRAY p_bdatecols
6);

5.4. Define an Active Expression

The table from division WEST contains the “Deleted” flag which tells us that the record was logically deleted or not. Therefore, the row should be terminated with a proper BDATE_TO when the flag is false. To achieve this we define an active expresseion based on this flag.

1execute script API_REPPSA.MODIFY_TSA_ACTIVE_EXPR(
2	 'DBITSA'                               -- p_obj_schema
3	, 'EMP_PERSON_WEST'                     -- p_obj_table
4	, 'nvl(@1, true)'                       -- p_active_expr
5	, ARRAY('1,Deleted')                    -- ARRAY p_act_cols
6);

Note that the CSV load from division EAST is always an incremental load and does not contain any delete-logic.

5.5. Compile And Deploy All PSA Structures

Optional:
In order to check for any misconfiguration beforehand - just refresh the metadata and check the validation view and have a look at the create table statement:

1execute script API_REPPSA.REFRESH;
2
3select * from REPPSA.REP_VALIDATE_PSA_OBJECTS where not chk_result;
4select * from REPPSA.REP_TABLE_DEFINITION where object_schema = 'PSA_EMP' and object_table = 'PERSON';

The compile step checks the validity anyways and raises an error if something is not ok.

1execute script API_REPPSA.COMPILE;

5.6. Define Load Cycles

We expect a file delivery from division EAST for each business day (validity date!). The delivery of the file may be delayed max. 1 calendar day. From the database we expect a valid dataset for every calandar day.

The RED framework delivers always 2 calendar keys from scratch: CD (calendar day) and BD (business day). Check REDADM.RED_CALENDAR_KEYS for this. A per resolution on daily basis can be found in REDADM.RED_CALENDAR_DAYS.

 1execute script API_REDADM.ADD_TSA_LOAD_CYCLE(
 2     'CFITSA'            -- p_tsa_schema
 3    ,'EMP_PERSON_EAST'   -- p_tsa_table
 4    ,'AT'                -- p_load_cycle_cal_name
 5    ,'BD'                -- p_load_cycle_cal_key
 6    ,'2024-08-01'        -- p_load_cycle_from
 7    ,'9999-12-31'        -- p_load_cycle_to
 8    ,1                   -- p_delivery_offset
 9    ,null                -- p_alert_1_offset
10    ,null                -- p_alert_2_offset
11);
12
13execute script API_REDADM.ADD_TSA_LOAD_CYCLE(
14     'DBITSA'            -- p_tsa_schema
15    ,'EMP_PERSON_WEST'   -- p_tsa_table
16    ,'AT'                -- p_load_cycle_cal_name
17    ,'BD'                -- p_load_cycle_cal_key
18    ,'2024-08-01'        -- p_load_cycle_from
19    ,'9999-12-31'        -- p_load_cycle_to
20    ,null                -- p_delivery_offset
21    ,null                -- p_alert_1_offset
22    ,null                -- p_alert_2_offset
23);

The thus created load cycles can be found in REDADM.RED_TSA_LOAD_CYCLES. The view REDADM.RED_TSA_LOAD_CYCLE_STATUS shows load cycle mismatches, e.g.cycle overlaps. Later, when your system has been running for a while, it also shows real loads without a matchin cycle.

Finally, refresh the calendar information to make the load cycles effective:

1EXECUTE SCRIPT API_REDADM.REFRESH_TIME(
2     true                                                       -- p_full_refresh
3    ,ARRAY('CFITSA.EMP_PERSON_EAST','DBITSA.EMP_PERSON_WEST')   -- ARRAY p_tsa_objects
4);

Tip

If an empty array is provided, all calendars for all tables will be refreshed.

5.7. Refresh the PSV Security Layer

We don’t want to wait for the next regular SCURTY refresh which would automatically add the new PSV-view. Therefore, we could trigger the refresh manually:

1execute script API_SCURTY.REP_REFRESH();

Now PSA_EMP.PERSON is accessible via PSV_EMP_DR, PSV_EMP_TA and PSV_EMP_BA schemas. However, an “normal” user (i.e. not the application manager) will not see these objects. This will be managed in one of the next chapters.

5.8. Resume the Framework And Trigger the Loads

Revert to normal operation of the framework by redctl resume. With a maximum delay of 1 minute the DB loads should start automatically.

For the file load simply copy the prepared file from /tmp into the input directory on the RED-server.

Important

Don’t forget to copy the file with a suffix ‘.tra’ and remove this suffix only if the copy was successfull.

DEV1 [redsys@red01 dev1]$ cd data/input
DEV1 [redsys@red01 input]$ cp /tmp/EMP_person_20250901_20250902144500.csv EMP_person_20250901_20250902144500.csv.tra
DEV1 [redsys@red01 input]$ mv EMP_person_20250901_20250902144500.csv.tra EMP_person_20250901_20250902144500.csv
DEV1 [redsys@red01 input]$

If everything was configured correctly the file should be loaded within the next minute and then be moved to the archive directory with the import run-id appended (check CFIADM.CFI_IMP_LOG_DETAILS).

For a detailed status and runtime overview check the following views:

  • DBIADM

    • DBI_CDC_STATUS

    • DBI_CDC_LOG_DETAILS

  • CFIADM

    • CFI_IMP_STATUS

    • CFI_IMP_LOG_DETAILS

  • REDADM

    • STA_TSA_ACTIVE_LOADER

    • STA_PSA_ACTIVE_LOADER

    • STA_OBJECT_STATUS