2. Load DBI Data into the PSA¶
This guide continues the DBI import: it defines the PSA table for the TSA table DBITSA.TESTING_TST_PERS_LDR of the loader ‘TST_PERS_LDR’.
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.
2.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 Table from a Source Database.
2.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 and change tracking (CT) 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('DBITSA,TESTING_TST_PERS_LDR,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 |
2.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.
If the source table has a primary key, it is used as IDC and BSK by default. Define or change the keys 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 CustomerId.
Example: Define IDC, BSK, and BK
1EXECUTE SCRIPT API_REPPSA.MODIFY_TSA_KEY_COLUMNS(
2 'DBITSA' -- p_obj_schema
3 ,'TESTING_TST_PERS_LDR' -- p_obj_table
4 ,'IDC' -- p_type
5 ,ARRAY('1,CustomerId') -- p_key_cols
6 ,true -- p_reset_flags
7);
8
9EXECUTE SCRIPT API_REPPSA.MODIFY_TSA_KEY_COLUMNS(
10 'DBITSA' -- p_obj_schema
11 ,'TESTING_TST_PERS_LDR' -- p_obj_table
12 ,'BSK' -- p_type
13 ,ARRAY('1,CustomerId') -- p_key_cols
14 ,true -- p_reset_flags
15);
16
17EXECUTE SCRIPT API_REPPSA.MODIFY_TSA_KEY_COLUMNS(
18 'DBITSA' -- p_obj_schema
19 ,'TESTING_TST_PERS_LDR' -- p_obj_table
20 ,'BK' -- p_type
21 ,ARRAY('1,CustomerId') -- 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 |
|---|---|---|---|
CustomerId |
1 |
1 |
1 |
2.4. Define the BDate Expression¶
The Effective-BDate of each row is defined with API_REPPSA.MODIFY_TSA_BDATE_EXPR:
- p_bdatecols
The columns used by the expression, with their placeholder number, in the format
ARRAY('1,column1', '2,column2', ...).- p_bdate_expr
SQL expression for the business date, with the placeholders
@1,@2, … for the columns of p_bdatecols.
In the following example the business date is the date of the last update or, if a record has never been updated, its creation date.
Example: Use the Update or Creation Date as BDate
1EXECUTE SCRIPT API_REPPSA.MODIFY_TSA_BDATE_EXPR(
2 'DBITSA' -- p_obj_schema
3 ,'TESTING_TST_PERS_LDR' -- p_obj_table
4 ,'coalesce(to_date(@2), to_date(@1))' -- p_bdate_expr
5 ,ARRAY('1,CreatedOn','2,UpdatedOn') -- p_bdatecols
6);
The expression is stored in REPPSA.REP_TSA_OBJECTS:
OBJECT_SCHEMA |
OBJECT_TABLE |
BDATE_EXPRESSION |
|---|---|---|
DBITSA |
TESTING_TST_PERS_LDR |
coalesce(to_date(@2), to_date(@1)) |
REPPSA.REP_TSA_COLUMNS shows the placeholder number of each column in BDATE_POS:
TSA_COLUMN_NAME |
DATA_TYPE |
IDC_POS |
BSK_POS |
BK_POS |
BDATE_POS |
|---|---|---|---|---|---|
CustomerId |
DECIMAL(19,0) |
1 |
1 |
1 |
(null) |
FirstName |
VARCHAR(100) UTF8 |
(null) |
(null) |
(null) |
(null) |
LastName |
VARCHAR(100) UTF8 |
(null) |
(null) |
(null) |
(null) |
Salary |
DECIMAL(8,2) |
(null) |
(null) |
(null) |
(null) |
Birthday |
DATE |
(null) |
(null) |
(null) |
(null) |
CreatedOn |
TIMESTAMP(3) |
(null) |
(null) |
(null) |
1 |
UpdatedOn |
TIMESTAMP(3) |
(null) |
(null) |
(null) |
2 |
2.5. 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, the BSK must be calculated from CustomerId, and the business date from the expression defined above.
Then refresh the dependent metadata:
2.6. 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 'DBITSA' -- p_tsa_schema
3 ,'TESTING_TST_PERS_LDR' -- 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 |
|---|---|---|---|---|---|
DBITSA |
TESTING_TST_PERS_LDR |
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('DBITSA.TESTING_TST_PERS_LDR') -- p_tsa_objects
4);
Tip
With an empty array, the calendar information of all TSA tables is refreshed.
2.7. 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.
2.8. Resume the DB Jobs¶
Resume the DB jobs on the RED server (redctl resume). The data is loaded into the TSA table as soon as the watcher detects a change, and then historised into the PSA table.