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 schema for which the Rule Controller is configured will be visible. If you do not see the tables you require, the schema structure can be refreshed using the Refresh Schema 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 Schemas button located at the bottom of the Target Tables panel.

If a target table is located in a different schema than the source table, the Table-To-Table Synchronization rule will perform cross schema synchronization. In such cases, the source schema must have permissions to update the table in the target schema.

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

Table-To-Table rules can perform their synchronization operations using three different methods. These synchronization methods are enabled using the options on the Options Tab of the Table-To-Table rule form (see above). Some methods are faster than others and some are not appropriate in various situations.

Source Table Driver

This method is the most generic and is the default mode. This method works best if the target table is larger than the source. This synchronization method does not require the join columns of the source table to be indexed. However, if the join columns in the target table are not indexed the rule will be slow. This method handles situations in which the join columns in source or target tables (or both) contain duplicate values. This method will issue periodic commits so as not to consume excessive system resources during the updates.

Target Table Driver

This method works best if the target table is much smaller than the source table. It does require that the join columns specified in the source table be indexed as a unique or primary key. It does not matter if the join columns on the target table are indexed. The Group Target Join Cols sub option for this synchronization mode should be used if there many duplicate values in the join columns of the target table. If there are no, or few, duplicate values in the join columns of the target table then enabling this options will just slow things down slightly. This method will issue periodic commits so as not to consume excessive system resources during the updates.

Single Pass

This is usually the fastest of the three synchronization modes. However, it does not issue any commits until the entire synchronization operation is complete. This also means you will not be able to monitor it's progress in the Data Masker statistics screens. This synchronization method is not suitable if the target table is large as excessive undo or rollback may be consumed. The join columns specified in the source table must be indexed as a unique or primary key, however, it does not matter if the join columns on the target table are indexed.

The Table-To-Table Rule Where Clause Tab

Where Clause options are used when the choice of rows affected by the 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 used must be appropriate to the driver table mode set on the Rule Options Tab. In other words, if the Source Table Driver option is selected then the Where Clause must be written to select rows from the source table of the Table-To-Table rule. If the Target Table Driver or Single Pass options are selected then the Where Clause must be written to select rows from the target table of the Table-To-Table rule.

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