1. Load CFI Data into the PSA

This guide continues the CFI import: it defines the PSA table for the TSA table CFITSA_TEST.TESTING_PERSON of the loader ‘PERSON’.

Attention

Suspend the DB jobs on the RED server first (redctl suspend), and only resume them at the end of this guide.

Important

The REPPSA APIs require an open business version. Check REDSYS.ADM_CURRENT_BVERSION; if its status is CLOSED, open a new version with API_REDADM.OPEN_BV.

1.1. Important Tables

The PSA metadata is stored in the schema REPPSA:

REPPSA.REP_TSA_OBJECTS and REPPSA.REP_TSA_COLUMNS

TSA tables and their columns, including key positions and BDate expressions.

REPPSA.REP_PSA_OBJECTS and REPPSA.REP_PSA_COLUMNS

PSA tables and their columns.

REPPSA.REP_PSA_TSA_OBJECTS and REPPSA.REP_PSA_TSA_COLUMNS

The mapping of TSA tables and columns to PSA tables and columns.

Make sure that the TSA table exists: Import a File.

1.2. Add the PSA Object

To add a PSA object, use API_REPPSA.ADD_OBJECT:

p_psa_obj_schema and p_psa_obj_table

Schema and name of the PSA table.

p_scd_type

2 for master data (Type-2) or 3 for event and transaction data (Type-3), see Historisation.

p_del_method

How deleted rows are terminated: by business date ('B') or by technical date ('T'). Type-3 tables only allow 'T'.

p_tsa_tables

All TSA tables that are loaded into this PSA table, one per BDomain, in the format ARRAY('TSA_SCHEMA,TSA_TABLE,BDOMAIN', ...).

In the following example the PSA table is a Type-2 table with termination by technical date.

Example: Add the PSA Table PSA_PERS.PERSON

1EXECUTE SCRIPT API_REPPSA.ADD_OBJECT(
2    'PSA_PERS'                                    -- p_psa_obj_schema
3   ,'PERSON'                                      -- p_psa_obj_table
4   ,2                                             -- p_scd_type
5   ,null                                          -- p_comment
6   ,'T'                                           -- p_del_method
7   ,ARRAY('CFITSA_TEST,TESTING_PERSON,TESTING')   -- p_tsa_tables
8);

This command creates a new entry in REPPSA.REP_PSA_OBJECTS:

OBJECT_SCHEMA

OBJECT_TABLE

COMMENT

SCD_TYPE

DEL_METHOD

PSA_PERS

PERSON

(null)

2

T

1.3. Define the Key Columns

Each TSA table needs the following key definitions:

IDC (identity code)

Identifies a row in the source uniquely.

BSK (business surrogate key)

Identifies a record and all its historical versions; basis of the historisation.

BK (business key)

Alternate key for hub (_HT) lookup tables.

Define them with API_REPPSA.MODIFY_TSA_KEY_COLUMNS. p_key_cols lists the key columns with their position, in the format ARRAY('1,column1', '2,column2', ...). With p_reset_flags true, the flags of the columns are reset first; if in doubt, use true and define IDC, BSK, and BK one after the other.

In this example all three keys consist of the column ID.

Example: Define IDC, BSK, and BK

 1EXECUTE SCRIPT API_REPPSA.MODIFY_TSA_KEY_COLUMNS(
 2     'CFITSA_TEST'       -- p_obj_schema
 3    ,'TESTING_PERSON'    -- p_obj_table
 4    ,'IDC'               -- p_type
 5    ,ARRAY('1,ID')       -- p_key_cols
 6    ,true                -- p_reset_flags
 7);
 8
 9EXECUTE SCRIPT API_REPPSA.MODIFY_TSA_KEY_COLUMNS(
10     'CFITSA_TEST'       -- p_obj_schema
11    ,'TESTING_PERSON'    -- p_obj_table
12    ,'BSK'               -- p_type
13    ,ARRAY('1,ID')       -- p_key_cols
14    ,true                -- p_reset_flags
15);
16
17EXECUTE SCRIPT API_REPPSA.MODIFY_TSA_KEY_COLUMNS(
18     'CFITSA_TEST'       -- p_obj_schema
19    ,'TESTING_PERSON'    -- p_obj_table
20    ,'BK'                -- p_type
21    ,ARRAY('1,ID')       -- p_key_cols
22    ,true                -- p_reset_flags
23);

The key positions are stored in REPPSA.REP_TSA_COLUMNS:

TSA_COLUMN_NAME

IDC_POS

BSK_POS

BK_POS

ID

1

1

1

The business date of a CFI delivery is taken from the file name (see the loader definition). If the file contains a column with the business date of each row, define it as BDate expression with API_REPPSA.MODIFY_TSA_BDATE_EXPR (see Load DBI Data into the PSA for an example).

1.4. Compile and Refresh

Compile the PSA metadata. This creates or changes all necessary tables and views in the PSA layer.

Check the view PSA_PERS.PERSON_DV: it must contain all columns of the TSA table, and the BSK must be calculated from the key column ID.

Then refresh the dependent metadata:

1.5. Define a Load Cycle

Define when a delivery is expected with API_REDADM.ADD_TSA_LOAD_CYCLE (see Calendar Logic).

Example: Expect a Load on All Business Days of the Austrian Calendar

 1EXECUTE SCRIPT API_REDADM.ADD_TSA_LOAD_CYCLE(
 2     'CFITSA_TEST'       -- p_tsa_schema
 3    ,'TESTING_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    ,null                -- p_delivery_offset
 9    ,null                -- p_alert_1_offset
10    ,null                -- p_alert_2_offset
11);

This command creates a new entry in REDADM.RED_TSA_LOAD_CYCLES:

TSA_SCHEMA

TSA_TABLE

LOAD_CYCLE_CAL_NAME

LOAD_CYCLE_CAL_KEY

LOAD_CYCLE_FROM

LOAD_CYCLE_TO

CFITSA_TEST

TESTING_PERSON

AT

BD

2024-08-01

9999-12-31

Refresh the calendar information of the TSA table:

Example: Refresh the Calendar Information

1EXECUTE SCRIPT API_REDADM.REFRESH_TIME(
2     true                                  -- p_full_refresh
3    ,ARRAY('CFITSA_TEST.TESTING_PERSON')   -- p_tsa_objects
4);

Tip

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

1.6. Refresh the PSV Security Layer

Create the PSV views of the new PSA table immediately, instead of waiting for the next security refresh:

Example: Refresh the Security Layer

1EXECUTE SCRIPT API_SCURTY.REP_REFRESH();

The PSA table PSA_PERS.PERSON is now created and accessible via the corresponding PSV views.

1.7. Resume and Deliver the File

Resume the DB jobs on the RED server (redctl resume) and deliver the file. The data is loaded into the TSA table and then historised into the PSA table.