Cloning from Multiple Data Sources

If you are cloning multiple data sources to the same relational database and there are duplicate data set names, DATABridge modifies the table name for those duplicates to avoid creating multiple tables with the same name.

For example, if you have two data sources (DSA and DSB), both of which include a data set named PRODUCTS, DATABridge clones the data set from DSA into a table named "products". When DSB is cloned, its data set PRODUCTS is cloned into a table named "products1".

Important: To avoid potential errors, rename any tables that have duplicate names. For example, you can rename the "products" table to "products_a" for data source DSA and to "products_b" for data source DSB. You can rename tables during the relational database customization phase of the define command using the script.user_define.primary_tablename. For a sample script, see Renaming a Table.

The DATABridge Client renames duplicate table names across data sources as a precaution against accidentally removing a table that contains good data. If you do not drop either of the data sources, rerunning the define command for either data source does not cause any problems.

For example, if you execute another define command for DSA because DMSII database A was reorganized, the define command looks for the table name "products" in the DATATABLES client control table that belongs to data sources other than DSA. Because the name "products" belongs to DSA only, the define command does not find "products" as a table name under any other data source. Thus the table corresponding to the data set PRODUCTS will be named "products", as was the case earlier.

Similarly, if you execute a define command for DSB, the define command looks for the name "products" in the DATATABLES client control table that belongs to data sources other than DSB. Because the name "products" belongs to DSA, the define command will find "products" as a table name used by another data source and it will resolve the conflict by renaming the table. Thus the table corresponding to the data set PRODUCTS will be named "products1" as was the case before the define command was run.

If you drop either of the data sources, however, the results may be different because the table name is no longer a duplicate. For example, if you drop DSA and then execute a define command for data source DSB, the table will be named "products", not "products1", because it is no longer a duplicate.

Similarly, if you do a dropall command and then execute a define command for data source DSB first, the tables will be named "products" for data source DSB and "products1" for data source DSA.

Add a Prefix to Duplicate Data Set Names

If you replicate two or more databases, which have many data set names in common, you can make the program add a prefix to all the table names for a data source. The prefixes, which can be 1–8 characters long, must be defined before the relational database layout is created. To do this, assign a value, such as X1, to the tab_name_prefix column of the corresponding entry in the DATASOURCES client control table using the script script.user_datasets.datasource. Using different prefixes for each data source makes the table names unique and eliminates the need to rename tables.

If you are using multiple data sources that have data sets or indexes that have the same name, we strongly recommend that you write user scripts to resolve this issue by forcing such a table to use a different name for one (or more if the name occurs in more than two data sources).

This will ensure that you have a consistent naming convention. Otherwise, you could run into problems if you reorganize these data sets.

Example script

script.user_define.customer:
update DATATABLES set table_name='customer_demodb'
where data_source='DEMODB' and dataset_name='CUSTOMER'
/***/
update DATAITEMS set table_name='customer_demodb'
where data_source='DEMODB' and table_name='customer'

This example script forces the DEMODB table customer to always be renamed. The other one will then be able to always use the name 'customer'. It also makes sure that all the items in the renamed table point to the renamed table. The line /***/, which separates the two SQL statements in the script tells the Client to execute the first SQL statement before moving on to the second one.