Problem Summary
A request has been made to prefix the replicated name. This can be done by updating the hvr catalog table called hvr_action, selecting all the tables from the catalog table hvr_table.
Answer
Below is an example of how to add the prefix, ie. src_to your tables that you are replicating to your target.
You may also wish to reference, https://www.hvr-software.com/docs/internal-hvr-objects/catalog-tables
There are a couple of tips and tricks you can experiment with, such as:
1. Export channel
2. Generate SQL into the Local Data Processing repository
For the Export Channel suggestion, you can try to first build your complete channel definition, then export it, then edit the resulting export.xml file by doing a string substitution. The string substitution would involve changing tbl_base_name="value to be tbl_base_name="src_<table_name>.
3. run re-INITIALIZE to update the enrollment file for this to be successful.
Now if you don't want to do this one by one for each table or have a large number of tables, you can do it with a script
4. Next, create the dynamic SQL for the replication set of the table(s) you want to prefix. In this example, I will prefix with 'src_'
After initializing the channel connecting as the HUB owner, i.e. hvrhub create & run the SQL you create with the below examples to populate the target for all the tables you have included that you want to be renamed on target.
NOTE: Remember to switch out your appropriate channel and group names ('TARGET') as when you create this dynamic SQL as yours may not be named the same.
Also, be careful
a) if you happen to have other table properties that exist, which would then require some fancier querying, and
b) if you have long database table names (greater than 26 columns),
c) if you have the same table name going to two target tables,
d) have the same table name in multiple source schemas, for which Local Data Processing will modify the internal Local Data Processing tbl_name by possibly truncating the table name and then adding numbers to the end.
Example directly creating the SQL for all tables in a channel, for a group
select 'insert into hvr_action_tmp (chn_name,grp_name,tbl_name,act_name,act_parameters) values ("'||chn_name||"', "TARGET", "'||tbl_name||"', "TableProperties", "/BaseName=src_'||tbl_name||"');' from hvr_table where chn_name='&chn_name'
Or an example of creating a dynamic SQL to create the insert statements separately to save/spool to a file to review then run separately in the hub owner schema.
select 'insert into hvr_action (chn_name,grp_name,tbl_name,act_name,act_parameters) values ("'||chn_name||"', "TARGET", "'||tbl_name||"', "TableProperties", "/BaseName=src_'||tbl_name||"');' from hvr_table where chn_name= '&chn_name';
Example output:
insert into hvr_action (chn_name,grp_name,tbl_name,act_name,act_parameters) values ('one2many', 'TARGET', 'order_line', 'TableProperties', '/BaseName=src_order_line');
insert into hvr_action (chn_name,grp_name,tbl_name,act_name,act_parameters) values ('one2many', 'TARGET', 'orders', 'TableProperties', '/BaseName=src_orders');
insert into hvr_action (chn_name,grp_name,tbl_name,act_name,act_parameters) values ('one2many', 'TARGET', 'stock', 'TableProperties', '/BaseName=src_stock');
NOTE :The table base name (tbl_name) is not truncated by Local Data Processing but reflects the actual table name in the source. But as noted above, Local Data Processing normally truncates Local Data Processing internal column name called tbl_name and populates down to 26 characters.
- Then run REFRESH to populate the tables on the target with the new name (ie. src_<table_name>) and view the log once completed.
