How to Add a Prefix to Replicated Table Name(s) on Target

Viewing 1 post (of 1 total)
  • Author
    Posts
  • #17738
    Donna Zehl
    Keymaster

     

    Description:

    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 form the catalog table hvr_table.

    Reference:

    hvr_table:  https://www.hvr-software.com/docs/Catalog_Tables#HVR_TABLE

    hvr_action:  https://www.hvr-software.com/docs/Catalog_Tables#HVR_ACTION

     

    Steps:

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

    There are a couple 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 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 large number of tables, you can do it with a script

    1.  Next create the dynamic SQL for 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, ie 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 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 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.

     

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

     

Viewing 1 post (of 1 total)
  • You must be logged in to reply to this topic.

© 2020 HVR

Test drive Contact us