Cross Database Command rules are designed to enable the execution of user defined SQL statements in a target database. The target database does not have to be an SQL Server database (although it can be) and the target database can be located on a different server than the SQL Server database referenced in the Rule Controller of the masking set. An unlimited number of Cross Database Command rules can be added to a masking set.

The New Cross Database Command rule help file contains full details regarding the configuration of a Cross Database Command rule.

Features of the Cross Database Command rule

The Target Database

The target database does not have to be located on the same server as the source database and can be of a different type than the source database. Currently both SQL Server and Oracle target databases are supported. Provision has been made to add other database types at a later date.

The SQL Statement 

The SQL statement to execute is entered in the SQL Command area at the bottom of the form. This SQL statement will execute on the remote database without interpretation by the Data Masker software and is always written in a format acceptable to the remote database type. Multiple SQL statements can be entered as long as they are each located on a new line and are terminated by a semi colon end of line delimiter.

-- an example of multiple SQL Statements designed to execute on a remote Oracle database.
drop table DM_STAGING_TABLE1;
create table DM_STAGING_TABLE1 (IDCol number(10), DataCol varchar2(60));

In the above example the Cross Database Command rule will ensure a new empty temporary table is created and a subsequent Table Mover rule could be used to copy rows from the SQL Server database over to the Oracle instance for later synchronization by other rules. It should be noted that if synchronization is the desired result this procedure could also be implemented in one step using a dedicated Cross Database Table-to-Table rule as well

Error Management and Handling

Normally, the Data Masker will stop processing the rule and the masking set if the remote database reports any errors. These errors are reported using the typical error codes and messages appropriate to the remote database type. These will not necessarily be the same as SQL Server error codes and messages if the remote database type is something other than an SQL Server database.

Sometimes, when running Cross Database Command rules, it is desirable to be able to ignore reported errors and continue processing. An example of this is an SQL statement of the form:

drop table TempTable;

If the table does not exist, the remote Oracle database will return an ORA-00942 table or view does not exist error which would completely halt the run of the masking set. However, in this case, since the rule is attempting to drop a table which already does not exist, the error is of no consequence. Enabling the Ignore Listed Errors option on the Error Mgr. tab and specifying either the error code ORA-00942 or part of the error message (or both) in the errors panel will cause those errors to be ignored for every statement in the Cross Database Command rule and allow subsequent statements to be processed.

It is important to realize that the Ignore Listed Errors option applies to all SQL statements in the Cross Database Command rule. If it is not appropriate to ignore the specified error code for all statements in the rule then it is advisable to split the statements out into separate Cross Database Command rules.

One drawback to including multiple SQL statements in one Cross Database Command Rule is that if errors occur, it can be harder to relate the messages returned from the remote database back to the SQL statement that caused the problem. In such cases it may be preferable to implement multiple Cross Database Command rules with one statement per rule. If multiple Cross Database Command rules are used to implement a series of statements, remember to implement rule blocks or dependencies in order to control the exact sequence in which the commands execute.

Creating Cross Database Command Rules

Cross Database Command rules are created by launching the New Cross Database Command rule form using the New Rule button located on the bottom of the Rules in Set tab.

As with all Data Masker rules - Cross Database Command rules execute in the order specified by their Rule Block and Dependency state.

How to Create a New Cross Database Command rule