OWB 11gR2 – MySQL Error Detection
By David Allan-Oracle on Jan 10, 2010
This is an introduction to the error detection capabilities using the check/control pattern supported in the code template framework and carries on from the previous post on OWB 11gR2's Open Connectivity using MySQL. The error detection scenario will leverage the mapping from the previous post. The check/control pattern is a generic framework for error handling that is supported out of the box with the supplied code templates, you will see how rules are logically declared and enabled within the mapping.
The framework is incorporated into integration code templates and can be initiated whilst the data is being integrated (flow in image below) or after inserting into tables (static in image below).
Using the example from the previous post as a basis which had employees and their bonuses, let's illustrate some error capture. Say we want to have a business rule that rejects data with a bonus greater than equal to 77777, then we can define a check constraint in OWB on the EMP_BONUS table which will check 'BONUS< 77777'. Can also use OWB's data rules to define common rules and apply them to tables.
Since we are adding this into a table used in an existing map, we'll have to synchronize the table operator in the mapping and enable the Control Code Template items. Below when editing the mapping if you select the EMP_BONUS operator the properties panel has a section on 'Control CT' properties, these are the rules you want to enable for the framework, as in Oracle Data Integrator, the rules can be enabled for Flow (before the data is loaded to the target) or Static (after the data is inserted).
In the property group the different properties represent rules taken from the underlying object (ie. a table's foreign key, check constraint etc.);
check constraints (any check constraints in object will be listed here)
data rules (any data rules in object will be listed here)
foreign key (any foreign keys in object will be listed here)
not null attributes (any not null columns in object will be listed here)
primary key (any primary keys in object will be listed here)
unique key (any unique keys in object will be listed here)
The check constraint BONUS_RANGE has been enabled for Flow Control.
Now the integration execution unit uses a code template that incorporates the Check/Control pattern, so we can enable the FLOW_CONTROL option in the Integration/Load Code Template tab...
Notice above that there is an option RECYCLE_ERRORS, this let's you easily add the error table as a source to be consumed in the mapping - so if there are errors they can be manually fixed and easily reprocessed.
Then for the 'Control Code Template' since we are using MySQL we can use the generic ANSI SQL Control Code Template CCT_SQL.
That's it, we are ready to deploy the mapping and execute it. This time there are more steps in the audit, you will see there are steps for 11_FLOW_CONTROL, below you can see the code for how the business rule for bonuses is checked and reported into the error table.
As a result of the execution we see the error logged into the E$_EMP_BONUS table with the details about when the error was checked and also the error message 'Check Constraint BONUS_RANGE failed' - along with the columns containing the data. See an extract below of the data (I hit a problem with the data viewer in OWB, so showing the data from mysql).
If we had recycle errors enabled we could fix this error manually in the error table and re-execute the mapping to process the errors rows (the inclusion of the error table would be automatic in the framework).
You may wonder where the E$_ prefix comes from...? Along with other temporary tables created by the framework the defaults for these tables are captured for each location and may be changed (maybe you have standards for names for these tables other than E$_ I$_ and so on). Below you can see my MySQL location and the values used.
So that's a very quick pass through some of the capabilities of the Control Code Templates using the MySQL example tables and hopefully let's you see how the framework has been exposed in OWB. Next up, I'll show how the bulk extract works and how you can easily leverage a systems features (again using MySQL) to unload in a native and efficient manner. Fun stuff!