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
-- an example of multiple SQL Statements designed to execute on a remote Sql Server database. drop table DM_STAGING_TABLE1; create table DM_STAGING_TABLE1 (IDCol number, DataCol varchar(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 Oracle database over to the Sql Server 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
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 Sql Server database will return an Cannot drop the table 'TempTable', because it does not exist or you do not have permission 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 all or part of the error message 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 implment 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