OWB 11gR2 – Debugging
By David Allan on Sep 06, 2010
The mapping debugger is a useful tool when testing business logic in your mappings. It allows you to step through row by row, or operator by operator, or with breakpoints and see how the data is transformed by each operator. It does this in the context of the objects you have used to design the mapping, these are logical ETL objects used for describing the transformations. So each operator has a specific detail panel that lets you see before, during and after views of the data.
The example below illustrates the filter operator where we can see the data before and after the operator execution, also the property inspector is open so we see the actual filter expression itself that is applied to the source rows. Pretty much like any debugger there are common functions like step, skip, step into, setting breakpoints and watching data.
Some debugging illustrations below, I like this view of the operators with real data since you can actually visualize what the operator is doing (so these are actually useful to look at even to get a quick idea of some of the operators);
- Union (set operation example)
- Pluggable mapping (user-defined, reusable components)
So for those that like using debuggers the above is all good! What about the other type of developer? Commonly when I am problem solving I will use the intermediate code generator within mapping. This lets me take snippets of the SQL and execute manually to figure out a problem. For each operator in the graph you can get the SQL before, after and for data operators get loading (insert/update/merge etc.) or incoming (select) SQL.
The generated SQL has the operator name in comments to allow you to pinpoint the responsible operator for that part of the SQL. This is handy for the SQL-savvy users that want to run the SQL manually and do their own debugging. Note in the above panel there are a few other buttons; the explain plan provides a graphical view of the estimated Oracle explain plan, statistics details provides actual information from executing the SQL including the plan an statistics and the SQL Tuning advisor uses DBMS_SQLTUNE (a comprehensive tuning task is started).
There we have it, a quick peek at some of the operators in debug mode and tuning capabilities in OWB. I think this could be continued to explain some other mechanisms for debugging the package and database views for seeing lines numbers etc.