The New Table-to-Table Synchronization Rule Form
Published 19 March 2018
The Data Masker Create/Edit Table-To-Table Synchronization Rule Form
This form is used to create and edit Data Masker Table-To-Table Synchronization rules. Table-To-Table Synchronization rules are used in a situation where columns in one table must be updated to contain values identical to those in another table. The title text and button label on the form will change as is appropriate to the create or edit mode. In the example screen shot above, the form is editing an existing Table-To-Table Synchronization rule.
A Table-To-Table Synchronization rule must be configured to operate on a source and target table. Table-To-Table Synchronization rules also require information on the rows in each table which must be synchronized and a method of identifying how the rows in the source and target tables are related (called the Join condition). The About Table-To-Table Synchronization rules help file contains a detailed discussion of these topics.
Configuring the source and target tables is a straightforward process. Select the source table from the Source Table panel on the left of the form and the target table from the Target Table panel on the right. Use the mouse to select each table name from the appropriate panel. The choice of available source tables is entirely determined by the Rule Controller with which the Table-To-Table Synchronization rule is associated. Only tables from within the database for which the Rule Controller is configured will be visible. If you do not see the tables you require, the database structure can be refreshed using the Refresh Tables and Indexes button on Options tab of the edit Rule Controller form. The choice of the target tables is determined by all tables known to all Rule Controllers in the masking set. By default only the tables belonging to the current Rule Controller are displayed - however activating the All Databases button located at the bottom of the Target Tables panel will display the tables for other databases.
If a target table is located in a different database than the source table, the Table-To-Table Synchronization rule will perform cross database synchronization. In such cases, the source database must have permissions to update the table in the target database.
It is not possible to configure the Table-To-Table Synchronization rule to use an identical source and target table. If you need this functionality investigate Table-Internal Synchronization rules.
The columns to be synchronized are entered in the Synchronization Columns panel located at the top center of the form. Once the source and target tables have been selected, the items on the appropriate side of the Synchronization Columns panel will contain the column information for that table. Select the matching pair of columns in the source and target tables that require the same values. If there is more than one pair to synchronize, press the Add button to generate a new placeholder and then select the column names from it.
The rows which receive the same values in the synchronization columns are determined by the Join condition. This panel is located in the lower central area of the form and it operates in a similar way to the Synchronization Columns panel. Select the columns by which the rows should be joined - if there is more than one, press the Add button to generate another placeholder. Note the I icon located in the header of columns in the Join Panel. A green icon here indicates that the specified columns are indexed. If the target table is large it is very important that the columns on the target side of the join condition are indexed. Otherwise, the rule will be extremely slow.
When the rule is executed, the rows in the source and target table with identical join conditions will be selected and the target table will be updated with the values from the specified synchronization columns in the source table. The About Table-To-Table Synchronization rules contains a useful example illustrating how Table-To-Table Synchronization rules operate.
Important Note: Please read the section of the Table-To-Table Synchronization rule help page which discusses the pre-masking of the columns in the target table prior to performing the Table-To-Table synchronization. This can avoid accidentally leaving some columns un-masked if the join values in the target do not appear in the source table.
The Table-To-Table Rule Options Tab
The Options Tab of the Table-To-Table rule (see above) offers two options.
Single Pass Join
Provides a way to choose an alternate synchronization method called a Single Pass Join. The Single Pass Join synchronization mode is usually faster than the standard synchronization method. However, the rule will not issue any commits until the entire synchronization operation is complete. This also means it will not be possible to monitor the rules progress in the Data Masker statistics screens. Also, it is not possible to apply a Where Clause to the Table-To-Table rule when using this mode.
Force ISNULLs Off
This options builds an SQL Statement which does not use ISNULLS on the Join condition. Builds an SQL Statement which does not use ISNULLS on the join condition. In SQL Server a NULL value does not equal NULL and ISNULLs are used to enforce the comparison so that join conditions containing NULL values will be correctly synchronized. If you are sure the Join condition in the Table-To-Table rule will never contain a NULL value you can check this option and ISNULLs will not be used. This option can speed up some rule processing. Please contact the Data Masker support team for further advice and assistance before activating this option.
The Table-To-Table Rule Where Clause Tab
Where Clause options (see above) are used when the choice of rows affected by the synchronization operation is to be based on a specific criteria. Where Clause and sampling options are configured on the Where Clause tab. Note that the Where Clause cannot be used if the Single Pass Join option is set on the Rule Options Tab.
Existing Table-To-Table Synchronization rules can be edited by double clicking on them with the mouse. Table-To-Table Synchronization rules are created by launching the New Table-To-Table Synchronization rule form using the New Rule button located on the bottom of the Rules in Set tab.
How to Create a New Table-To-Table Synchronization Rule