7. Import a Table From Some Source Database

This is a step by step guide to load data from some database into the TSA.

For testing purposes a table is prvided containing information about people. The schema in this example is called “testDB”, in the database “TESTING”. For a SQL-Server as a source DB, you may use the following command:

 1create table testDB.Person
 2(
 3    CustomerId bigint,
 4    FirstName  varchar(100),
 5    LastName   varchar(100),
 6    Salary     decimal(8,2),
 7    Birthday   date,
 8    CreatedOn  datetime not null,
 9    UpdatedOn  datetime,
10    primary key (CustomerId)
11);

Now insert some test data:

1insert into testDB.Person (CustomerId, FirstName, LastName, Salary, Birthday, CreatedOn, UpdatedOn)
2values
3('5971250571','Jane', 'Doe', 3500.64, '19710525', getdate()-5, null),
4('6755130370','Max', 'Mustermann', 3500.65, '19700313', getdate()-3, getdate()-1);

Now switch to the RED environment. Here the following steps are necessary to be able to import the database table:

  • Add a BDomain

  • Add BDomain Settings

  • Add CDC Group

  • Add CDC watcher

  • Add BDomain to CDC watcher

  • Add Loader

  • Add BDomain metadata to Loader

  • Create TSA table

But before these steps can be taken the framework shoud be suspended. This can be done by issuing one of the following commands on the RED server:

redctl suspend

or

redctl restart -S

The second option should be the preferred one. More information about the RED server and possible commands can be found in the RED-PSS Guide.

Hint

This guide can be followed no matter if the data should be historised as a SCD Type-2 or Type-3 table in the end as there is no difference until the TSA table is created.

7.1. Add a BDomain

Make sure the BDomain exists or add it first: Add a BDomain

Check for the existence of the BDomain ‘TESTING’ in the table REDADM.RED_BDOMAINS.

7.2. Add BDomain Settings

Add specific BDomain setting for DBI-loads: Add BDomain Settings.

Check the settings in the table DBIADM.DBI_BDOMAIN_SETTINGS.

7.3. Add a CDC Group

Add CDC a Group ‘tst_cdc_grp’.

Check the CDC group ‘tst_cdc_grp’ in the table DBIADM.DBI_CDC_GROUPS.

7.4. Add CDCG watcher

Now add a CDCG watcher base on time intervals.

Check the settings in the table DBIADM.DBI_CDCG_WATCH.

7.5. Add BDomain specific metadata to CDC watcher

Add BDomain specifc metadata to the CDCG watcher

Check the settings in the table DBIADM.DBI_CDCG_WATCH_BD.

7.6. Add a Loader

Add a Loader named ‘TST_PERS_LDR’.

7.7. Add BDomain Specifc Metadata to the Loader

Add BDomain specifc metadata to the loader.

Check the settings in the table DBIADM.DBI_LOADER_BD for the loader ‘TST_PERS_LDR’ with the BDomain ‘TESTING’.

7.8. Create TSA tables

A TSA table can now be created by providing the name of the loader and bdomain to the script API_DBIADM.CREATE_TSA_TABLES. Differently to a CFI loader in this step the columns do not have to be specified as the loader fetches all necessary information from the source DB.

Example

1EXECUTE SCRIPT API_DBIADM.CREATE_TSA_TABLES(
2   'TST_PERS_LDR'      -- p_ldr_name
3   ,'TESTING'          -- p_domain
4);

Afterwards a refresh is necessary for the new table to be accessible, therefore the following command has to be issued:

1EXECUTE SCRIPT API_DBIADM.REFRESH()

The TSA table has now been create into DBITSA.TEST_PERSON.

Tip

You can fill in metadata for all loaders first and then create all missing TSA-tables at once by just calling API_DBIADM.CREATE_TSA_TABLES without any arguments (i.e., provide NULL twice).

The load will start as soon as the framework has been resumed again:

redctl resume