OWB 11gR2 – MySQL Open Connectivity
By David Allan on Jan 07, 2010
With the 11gR2 release of OWB there were significant changes to mapping to support native heterogeneous connectivity to systems, prior to this release the heterogeneity was achieved by using Oracle Database Gateways - in OWB each system was projected as an Oracle system, so the data-types for those systems was mapped to Oracle by the Database Gateway. In 11gR2 the types for these systems is captured within OWB - so when tables are designed or reverse engineered for example the native types for these systems are captured.
So what? Now as well as the Oracle connectivity provided via Database Gateways, OWB 11gR2 can provide native connectivity for other systems and an open pluggable framework for adding more systems and a framework to add code templates for such systems. How is the connectivity determined? When creating the module a choice is made to either use (Oracle Database) Gateway or Native Database Connection for the access method.
To take an example let's look at a MySQL example (using the sample schema here), the OWB platform definitions can be extended and using the MySQL JDBC driver the tables reverse engineered into OWB. The location is setup to use the MySQL JDBC driver and a DEMO user used (which I created with the example).
Update: When you connect ensure and use the capitalizeTypeNames=FALSE option in the connection url since the platform types are defined in lower case. So the connection URL would look like
Update: The MySQL user used when connecting must be defined with an uppercase name. That is, when creating the user in MySQL use
The platform definition for MySQL is added via a script and can be downloaded here, the platform includes data types, type mappings and various properties used by the code template framework. The properties range from functions used to get the current date for the system (used by the Control Code Template framework for example), to SQL alias separators for columns and tables to the default JDBC driver class and URL template for connection. Here is an excerpt of some of the types and their mapping to the OWB core data types;
|MySQL Data Type||OWB Core Data Type||Notes|
|char||CHAR||length also mapped|
|decimal||DECIMAL||precision and scale also mapped|
|numeric||NUMERIC||precision and scale also mapped|
When the tables are reverse engineered into OWB we see the tables in the tree and the MySQL data types captured for the table definitions. Just like other data definitions in OWB we can apply rules to this table, for tables accessed via native connectivity the rules can be enabled using the Control Code Templates (aka CKMs).
To construct an outer join example we will create a bonus table (remember and import into OWB) in the employees database and insert a row for the employee with the minimum employee number (emp_no);
create table bonus (emp_no int, bonus int);
insert into bonus values (10001, 100000);
In the example we want all employees returned along with the bonuses for those that have one, this is illustrated with the following diagram;
For natively accessed systems, mappings should be constructed under the 'Template Mappings' node, it is here that mappings can be constructed to leverage the open connectivity and code templates. Here we create a mapping named JOIN_LOGICAL_ANSI which will illustrate the outer join reading and writing to MySQL, no Oracle database is used in the runtime.
The outer join in the example uses the tables employees and bonus and is defined using ANSI SQL 92 grammar. The Joiner operator has been enhanced in OWB 11gR2 to support the definition of the join as a rich set of metadata, prior to this release Oracle syntax (using '+' notation and so on) was used to capture the join definition (OWB could generate Oracle or ANSI SQL based joins prior to this, but not capture the design in a logical way). Graphically we see how the employees and bonus tables are joined below, the '+' indicating the Oracle style visual for the outer joined table.
Note the join condition simply has the columns used in the join condition above. Below we see how the group for the bonus table is annotated with the join input role of 'Outer Join'.
Inspecting the execution unit view we see there is a single unit since all of the tables are defined on the same MySQL system (so no execution units to move data from one system are needed). The execution unit can make use of the code template to perform incremental update on the resultant table - this code template is a generic ANSI SQL code template for incremental update - I switched off the flow control for now, next post will illustrate that.
The OWB 11gR2 release primarily has code templates for Oracle but there are also templates for loading and integrating (and control) ANSI SQL systems (as well as files and so on), this set can be extended to write specific templates for specific systems, more on this later. Also ...... just like regular mappings you can have multiple steps, so as well as load and integration units you can chain them together within a mapping (so can do, many loads and many integrations for example).
When the mapping is generated it generates a very small EAR file for the mapping and the code templates each have their own EAR file too (the templates used by a mapping are not embedded in the mapping but referenced). We can deploy the mapping to any Control Center Agent and then execute. A mapping is executed on a single agent, but obviously may make remote calls such as JDBC executions or any other remote invocation you can dream up.
When the mapping is executed within the designer the Audit Information panel (just like the ODI Operator window) will let you inspect the execution of the mapping and see the code execute before and after it was converted from template to code.
You can then apply logical rules on the target table which can be leveraged from within the Control Code Template framework - for example check constraints on the table (these are not physical constraints but enforced in the execution of the flow). More on this in the next post!
What about hints for performance optimization? Well if we want to add some hints into the mapping, how do we do it? Well with Oracle traditional mappings you would configure the mapping and set hints on the tables for example for extracting or loading or just switch on automatic hints for defaults. With template based mappings it is generally supported via code template parameters.
If we check out the ANSI SQL incremental update code template it has not built-in support for hints whereas the Oracle code template does. Compare the parameters for both templates;
So what? Well this means we have to build a new ANSI SQL incremental update template to support hints if we want them. Its easy to do, we can copy-paste the existing template, add a parameter and change the template. So the new template looks like this....
Then in the mapping I can change the template assignment to use my new custom one with the hint and set the mapping to use a MySQL hint such as SQLCACHE...
After deployment and execution I can see that the hint has been successfully included in the generated code...
So there you go, a quick run through of various odds and ends to illustrate some of the changes to mappings in OWB 11gR2 to support native heterogeneous connectivity to systems.
The mapping illustrated depends on no Oracle database, its using a java engine to execute the mapping (via SQL through JDBC in this example) and auditing to the file-system (can also plug in an ANSI SQL database for storing audit trails). I'll take this further in subsequent postings to illustrate some topics mentioned including error detection using the check/control framework and also unloading all using MySQL.