5. Configure the PSA Load Process

All activities must be executed as application manager. The REPPSA APIs require an open business version.

5.1. Configure the PSA Target Table

  • Employee data is master data and is therefore configured as Type-2.

  • The PSA table is created as PSA_EMP.PERSON.

  • Deleted rows are terminated by BDate. Division WEST marks deleted rows with the column ‘Deleted’ (see the active expression below); the incremental CSV loads of division EAST contain no deletion information.

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,DIV_EAST_EMP_PERSON,DIV_EAST', 'DBITSA,DIV_WEST_EMP_PERSON,DIV_WEST')  -- p_tsa_tables
8);

5.2. Configure Key Columns

Since no HT lookup tables are used in this example, it is sufficient to define the BSK (it is also used as IDC if no IDC is defined). The column ‘EmpId’ identifies an employee within a division in both sources and is therefore used as business key.

 1execute script API_REPPSA.MODIFY_TSA_KEY_COLUMNS(
 2     'CFITSA_EMP'            -- p_obj_schema
 3    ,'DIV_EAST_EMP_PERSON'   -- p_obj_table
 4    ,'BSK'                   -- p_type
 5    ,ARRAY('1,EmpId')        -- p_key_cols
 6    ,true                    -- p_reset_flags
 7);
 8
 9execute script API_REPPSA.MODIFY_TSA_KEY_COLUMNS(
10     'DBITSA'                -- p_obj_schema
11    ,'DIV_WEST_EMP_PERSON'   -- p_obj_table
12    ,'BSK'                   -- p_type
13    ,ARRAY('1,EmpId')        -- 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 part of the file name; 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    ,'DIV_WEST_EMP_PERSON'                  -- p_obj_table
4    ,'coalesce(to_date(@2), to_date(@1))'   -- p_bdate_expr
5    ,ARRAY('1,CreatedOn','2,UpdatedOn')     -- p_bdatecols
6);

5.4. Define an Active Expression

The table of division WEST contains the flag “Deleted”, which marks logically deleted records. The active expression defines when a row is active; an inactive row is terminated with a proper BDATE_TO. The expression works whether the flag is loaded as BOOLEAN or as number. An active expression must be defined for all TSA tables of a PSA table, so division EAST gets the constant expression true.

 1execute script API_REPPSA.MODIFY_TSA_ACTIVE_EXPR(
 2     'DBITSA'                               -- p_obj_schema
 3    ,'DIV_WEST_EMP_PERSON'                  -- p_obj_table
 4    ,'nvl(cast(@1 as decimal(1,0)), 0) = 0' -- p_active_expr
 5    ,ARRAY('1,Deleted')                     -- p_act_cols
 6);
 7
 8execute script API_REPPSA.MODIFY_TSA_ACTIVE_EXPR(
 9     'CFITSA_EMP'                           -- p_obj_schema
10    ,'DIV_EAST_EMP_PERSON'                  -- p_obj_table
11    ,'true'                                 -- p_active_expr
12    ,ARRAY()                                -- p_act_cols
13);

5.5. Compile And Deploy All PSA Structures

Optional: to check for misconfigurations beforehand, refresh the metadata, check the validation view, and 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_V where object_schema = 'PSA_EMP' and object_table = 'PERSON';

The compile step checks the validity anyway and raises an error if something is wrong.

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 may be delayed by up to one day. From the database of division WEST we expect a valid dataset for every calendar day.

RED delivers the calendar keys CD (calendar day) and BD (business day) for each calendar, see REDADM.RED_CALENDAR_KEYS. The resulting days are listed in REDADM.RED_CALENDAR_DAYS.

 1execute script API_REDADM.ADD_TSA_LOAD_CYCLE(
 2     'CFITSA_EMP'            -- p_tsa_schema
 3    ,'DIV_EAST_EMP_PERSON'   -- 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    ,'DIV_WEST_EMP_PERSON'   -- p_tsa_table
16    ,'AT'                -- p_load_cycle_cal_name
17    ,'CD'                -- 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 load cycles are stored in REDADM.RED_TSA_LOAD_CYCLES. REDADM.RED_TSA_LOAD_CYCLE_STATUS shows load cycle mismatches, e.g. overlaps, and, once the system has been running for a while, actual loads without a matching 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.DIV_EAST_EMP_PERSON','DBITSA.DIV_WEST_EMP_PERSON')   -- p_tsa_objects
4);

Tip

With an empty array, the calendar information of all TSA tables is refreshed.

5.7. Refresh the PSV Security Layer

Instead of waiting for the next regular security refresh (every 30 minutes), which creates the new PSV views, trigger it manually:

1execute script API_SCURTY.REP_REFRESH;

Now PSA_EMP.PERSON is accessible via the schemas PSV_EMP_DR, PSV_EMP_TA, and PSV_EMP_BA. However, a “normal” user (i.e. not the application manager) does not see these objects yet; access is granted in the next chapter.

5.8. Resume the Framework And Trigger the Loads

Resume the DB jobs with redctl resume. The DB load starts as soon as the watcher detects a change (at the latest at the next day change at 3:00); to load immediately, use API_DBIADM.TRIGGER_CDC_GROUP.

For the file load, copy the prepared file from /tmp into the input directory of the environment on the RED server.

Important

Copy the file with the suffix ‘.tra’ and remove the suffix only when the copy is complete.

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 is configured correctly, the file is loaded within the next minute and then moved to the archive directory (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