It is possible to implement multiple Rule Controllers in the same extraction or loader set. In a loader set, multiple Rule Controllers serve primarily as a convenience which avoids the requirement to run many single Rule Controller based loader sets. Multiple Rule Controllers do not usually significantly increase the complexity of a loader set since there is no interaction between the schemas during the load process. This is usually not true when multiple Rule Controllers are used in extraction sets. The usage of multiple Rule Controllers in extraction sets is very much an advanced topic as it almost always greatly increases the complexity of the resulting extraction rules.
The use of multiple Rule Controllers in an extraction set permits the extraction of a subset of data which is referentially correct within each schema and also between each schema configured in the Rule Controller.
Referentially correct data relationships between schemas are implemented with Cross Schema Table-To-Table rules. These rules function in the same manner as normal Table-To-Table rules except that the source and target tables are in different schemas. In general, it is usually better not to have loops in the cross schema Table-To-Table rule relationships because it greatly increases the probability of an extraction set explosion. The following is a simple example, consider an extraction set with two Rule Controllers which connect to SCHEMA_A and SCHEMA_B respectively.
Rule 01-0001 <Rule Controller, connects to SCHEMA_A> Rule 01-0002 <WHERE Clause Rule on SCHEMA_A.TABLE_A> Rule 01-0003 <CROSS_SCHEMA Table-To-Table Rule (SCHEMA_A.TABLE_A -> SCHEMA_B.TABLE_X> Rule 02-0004 <Rule Controller, connects to SCHEMA_B> Rule 01-0005 <CROSS_SCHEMA Table-To-Table Rule (SCHEMA_A.TABLE_X -> SCHEMA_A.TABLE_A>
If rule 0002 working under Rule Controller 0001 extracts 100 rows for TABLE_A then rule 0003 will have to activate. It might cause 1000 rows to be extracted for TABLE_X in SCHEMA_B (assuming a one-to-many relationship). Those newly extracted rows in TABLE_X will activate rule 0005 which could possibly extract even more rows for SCHEMA_A.TABLE_A which then, after the duplicates are sifted out, reactivates rule 0003 and extracts more rows for SCHEMA_B.TABLE_X. So the extraction set explosion continues until all rows from SCHEMA_A.TABLE_A and SCHEMA_B.TABLE_X have been extracted. This effect may not be desirable.
A better way to arrange cross schema Table-To-Table rules is to avoid loops and back links and use a fan out principle. The example below shows an example with four Rule Controllers.
Rule 01-0001 <Rule Controller, connects to SCHEMA_A> Rule 01-0002 <WHERE Clause Rule on SCHEMA_A.TABLE_A> Rule 01-0003 <CROSS_SCHEMA Table-To-Table Rule (SCHEMA_A.TABLE_A -> SCHEMA_B.TABLE_X> Rule 01-0003 <CROSS_SCHEMA Table-To-Table Rule (SCHEMA_A.TABLE_A -> SCHEMA_C.TABLE_Q> Rule 01-000? <.... other rules ....> Rule 02-0004 <Rule Controller, connects to SCHEMA_B> Rule 01-0005 <CROSS_SCHEMA Table-To-Table Rule (SCHEMA_A.TABLE_X -> SCHEMA_D.TABLE_M> Rule 01-000? <.... other rules ....> Rule 03-0006 <Rule Controller, connects to SCHEMA_C> Rule 01-0007 <Table-To-Table Rule (SCHEMA_C.TABLE_Q -> SCHEMA_C.TABLE_R> Rule 01-000? <.... other rules ....> Rule 04-0008 <Rule Controller, connects to SCHEMA_D> Rule 01-0009 <Table-To-Table Rule (SCHEMA_D.TABLE_M -> SCHEMA_D.TABLE_R> Rule 01-000? <.... other rules ....>
In the above example, rule 0001 is the primary Rule Controller and the WHERE Clause rule it contains (rule 0002) determines the contents of the tables in the all of the other schemas. Note that although rule 0005 is a cross schema Table-To-Table rule which requires rows to be present in tables in SCHEMA_D there are no outbound cross schema rules in SCHEMA_D which form a loop to the other Rule Controllers. It is possible to build a viable extraction set with interconnecting, looped, cross schema Table-To-Table rules however it is much harder to do and it is recommended that they be avoided if possible.
Note: Table-To-Table rules execute in stage two of the extraction process. If multiple Rule Controllers are used it really does not matter to the Table-To-Table rules which order the rule block values of the Rule Controller are configured. The Table-To-Table rules will be repeatedly reactivated, as required, until their extraction relationships is satisfied. This is not true for any All Rows, Where Clause and Command rules which are configured. These rules execute in stage one and stage 3 of the extraction process. If there are multiple Rule Controllers, the Set Extractor application will process each Rule Controller in order of that Rule Controllers rule block value. When a Rule Controller is activated for stage one processing, then all rules in stage one of that Rule Controller will be completed. In other words, the rule block values of the Rule Controllers are only meaningful when compared to other Rule Controllers. For example, a Rule Controller with a rule block of 01 will execute to completion all of its stage one rules before a Rule Controller with a rule block of 02 starts. The rule blocks of the dependent rules in each Rule Controller are irrelevant to the rules in other Rule Controllers. Once a Rule Controller begins executing stage one or stage three rules, then those rules execute to completion before any other stage one or stage three rules in any other Rule Controller execute.