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_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 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

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 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.