TV 3.7.0
If data from a database should be loaded into a TSA table the following guide should be followed step by step.
The import of database tables can be tested by creating a test table, in this case a table containing information about people, in a schema, in this example called "testDB", in the database "TESTING", from which tables should be imported from.
This can be done, for example if the source database is a SQL server, by using the following command:
create table testDB.Person
(
CustomerId bigint,
FirstName varchar(100),
LastName varchar(100),
Salary decimal (8,2),
Birthday date,
CreatedOn datetime not null,
UpdatedOn datetime,
primary key (CustomerId)
);
After the table is created, some entries have to be added, to be able to use this table for testing. This can be achieved by issuing the following command:
insert into testDB.Person (CustomerId, FirstName, LastName, Salary, Birthday, CreatedOn, UpdatedOn)
values
('5971250571','Jane', 'Doe', 3500.64, '19710525', getdate()-5, null),
('6755130370','Max', 'Mustermann', 3500.65, '19700313', getdate()-3, getdate()-1);
Afterwards one has to 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 to Loader
- Create TSA table
But before these steps can be taken the framework has to 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 PSS Guide.
This guide can be followed no matter if the data should be historized as a SCD Type-2 or Type-3 table in the end as there is no difference until the TSA table is created.
¶ 1. Add BDomain
First a bdomain has to be added. This can be done by following the first chapter of this HowTo Guide:
After following this guide, a bdomain with the name 'TESTING' has been created. If the creation was successful can be checked by going to the table REDADM.RED_BDOMAINS and looking for an entry with this name in the field 'BDOMAIN'.
¶ 2. Add BDomain Settings
After a bdomain has been added, DBI bdomain settings have to be added. This can be achieved by following the first chapter of the HowTo Guide Add BDomain Settings.
The settings can be checked in the table DBIADM.DBI_BDOMAIN_SETTINGS.
¶ 3. Add CDC Group
The next step is to add a Change Data Capture (CDC) group. Such a group can be added by following the first chapter of this HowTo Guide:
One can check if a cdc group was successfully created, by searching for an entry 'tst_cdc_grp' in the table DBIADM.DBI_CDC_GROUPS.
¶ 4. Add CDC watcher
After a cdc group has been added, a watcher has to be created for that group. This can be achieved by following the first chapter of the following HowTo Guide:
By following the guide above a scheduled watcher is created, however, if one wants to add a watcher based on a trigger, this is also explained in the guide above.
To make sure that the cdc watcher was correctly added, one can look at the entry for the cdc group 'tst_cdc_grp' in the table DBIADM.DBI_CDCG_WATCH.
¶ 5. Add BDomain specific metadata to CDC watcher
The next step, after having added a cdc watcher, is to add bdomain specific metadata to that watcher. This can be done by following the HowTo Guide Add BDomain specifc metadata to CDC watcher
Following this guide results in an entry for the cdc group 'tst_cdc_grp' and the bdomain 'TESTING' in the table DBIADM.DBI_CDCG_WATCH_BD.
¶ 6. Add Loader
After a cdc watcher has been added and a bdomain has been added to it, a loader has to be created. A loader can be added by following the first chapter of the HowTo Guide below.
To check if the loader was added, one can check the table DBIADM.DBI_LOADER for an entry with the name 'TST_PERS_LDR'.
¶ 7. Add BDomain specifc metadata to Loader
The next step is to connect the already created bdomain and loader. This can be done by following the first chapter of the following HowTo Guide:
The result of following the guide before is an entry in the table DBIADM.DBI_LOADER_BD for the loader 'TST_PERS_LDR' with the bdomain 'TESTING'.
¶ 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 is aware of the table and can use these formats for the target table.
¶ Example
EXECUTE SCRIPT API_DBIADM.CREATE_TSA_TABLES(
'TST_PERS_LDR' -- p_ldr_name
,'TESTING' -- p_domain
);
Afterwards a refresh is necessary for the new table to be accessible, therefore the following command has to be issued:
EXECUTE SCRIPT API_DBIADM.REFRESH()
The source table has now been loaded into DBITSA.TEST_PERSON.
TIPP: If one has created various loaders and wants to create a TSA table for each of them, one can provide two null values to the API_DBIADM.CREATE_TSA_TABLES function and the function will automatically create a TSA table for each new loader.