How to Add Prefix to Replicated Table Name(s) in Target

Question

How can I add a prefix to a replicated table name in the target?

Environment

HVR 5

Answer

This can be done by updating the hvr catalog table called hvr_action, selecting all the tables from the catalog table hvr_table.

Below is an example of how to add the prefix, src_ to the tables that you are replicating to your target.

There are a couple of tips and tricks you can experiment with, such as:

1. Export channel.

2. Generate SQL into the HVR 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 tbl_base_name="src_<table_name>.

   

3. Run 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 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 if:

   • You have other table properties that exist, which would then require some fancier querying, and

   • You have long database table names (greater than 26 columns),

   • You have the same table name going to two target tables,

   • You have the same table name in multiple source schemas, for which HVR will modify the internal HVR 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 HVR but reflects the actual table name in the source. But as noted above, HVR normally truncates HVR internal column name called tbl_name and populates down to 26 characters.

5. Then run Refresh to populate the tables on the target with the new name (ie. src_<table_name>) and view the log once completed.