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_OBJECTSandREPPSA.REP_TSA_COLUMNSTSA tables and their columns, including key positions and BDate expressions.
REPPSA.REP_PSA_OBJECTSandREPPSA.REP_PSA_COLUMNSPSA tables and their columns.
REPPSA.REP_PSA_TSA_OBJECTSandREPPSA.REP_PSA_TSA_COLUMNSThe 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
2for master data (Type-2) or3for 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.