OWB 11gR2 – MySQL Open Connectivity

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).

owb_mysql0

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

jdbc:mysql://localhost/employees?capitalizeTypeNames=FALSE

Update: The MySQL user used when connecting must be defined with an uppercase name. That is, when creating the user in MySQL use

CREATE USER 'DEMO'@'%' IDENTIFIED BY '***';

not

CREATE USER 'demo'@'%' IDENTIFIED BY '***';

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
int INTEGER  
text VARCHAR(MAX)  
char CHAR length also mapped
decimal DECIMAL precision and scale also mapped
numeric NUMERIC precision and scale also mapped
date DATE  

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).

owb_mysql2 owb_mysql3

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;

owb_mysql_oj

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.

owb_postgre3

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.

owb_mysql8

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'.

owb_mysql9

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).

owb_mysql10

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.

owb_mysql11

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;

owb_mysql20 owb_mysql21

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....

owb_mysql22

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...

owb_mysql23

After deployment and execution I can see that the hint has been successfully included in the generated code...

owb_mysql24 

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.

Comments:

So this is timely-- MySQL is now an Oracle database! so why not have OWB support it? :)

Posted by snarky commenter on February 15, 2010 at 04:37 PM PST #

I really hope you guys keep MySQL. Think of it as the free taste that the crack dealer hands out.... And as long as there is a clear path to migrating to your higher end paid product (oracle), it should be a huge boon to you.

Posted by SMU Cox MBA on April 27, 2010 at 10:56 PM PDT #

I really don't get it why jdbc connection form foces upper case of user name on "test connection" button's event? I can't connect to external hosting because miwa_admin != MIWA_ADMIN... I have to use "miwa_" prefix.

Posted by tonery on August 05, 2010 at 06:52 AM PDT #

Next question is : Why I don't have mySQL in "databases". I have to use "generic connection". That's fine, but all this stuff connection + imported tables are invisible for me. What I have to do to see mySQL in databases?

Posted by erwu on August 05, 2010 at 07:59 AM PDT #

Hi To see the MySQL node under the Databases node, you have to define the MySQL platform which is included in the post (the platform_mysql.tcl script is linked from the blog entry above). The upper casing of the name is not so flexible I agree, probably some throwback to when it was purely Oracle support. Cheers David

Posted by David Allan on August 05, 2010 at 08:12 AM PDT #

I want to ask about how to run tcl script.
There is a problem. I use source.sh to run tcl in OMBPLUS , but it gives me JavaNullPointerException Error.

Can you help me ?

Posted by DynDasE on October 10, 2011 at 08:44 PM PDT #

You should be able to run the command ./OMBPlus.sh from the owb/bin/unix directory.

Cheers
David

Posted by David on October 11, 2011 at 08:51 AM PDT #

Hi, I see mySQL in databases in Projects and in Locations, but I don't see the tables... any idea why?

Posted by maria on October 20, 2011 at 04:42 AM PDT #

Hi, I see mySQL in databases in Projects and in Locations, but I don't see the tables... any idea why?

Posted by Maria on October 20, 2011 at 04:43 AM PDT #

Hi Maria

Is the connection testing OK? When you say you don't see the tables, do you mean when you run the database import in the wizard?

Cheers
David

Posted by David on October 20, 2011 at 04:58 AM PDT #

yes. the connection test is ok, just like in your picture, but I don't see the structure. Does it mean that I have to start the import wizzard? What should I specify in C:\app\Administrator\product\11.2.0\dbhome_1\owb\mdl\ for the metadata file?
Thank you

Posted by Maria on October 20, 2011 at 05:20 AM PDT #

Hi Maria

When you create a module there are different ways of importing and creating metadata. You can import from a database, this reverse engineers from the data dictionary for example, you can import from MDL, this is an OWB formatted file, you can can export and import between OWB repositories. The option you are mentioning is the MDL route, you really want to use the Import -> Databases Objects option when you right click on the module.

Cheers
David

Posted by guest on October 20, 2011 at 05:32 AM PDT #

David,

In Design Center, in OMB I run the script platform_mysql.tcl, but I made a change defining the platform_type medium_unsigned and smallint_unaigned, because I got an JavaNullPointerException Error...
After I did this, I can see MySql platform in my Project and in the Locations. Than I edited the MySql Location, I entered the user, pass , the URL, just like in the example.
What is the next step now? How can I see the MySql tables?
From your answer I thought that I should use this import wizzard, but if you think there is an easier way I would like to know.... My goal is just to make the connection and see the tables...
I appreciate a lot your help because I'm a newbe in this ...
Thank you

Posted by Maria on October 20, 2011 at 05:49 AM PDT #

What do you mean by "When the tables are reverse engineered into OWB "?
We have an sql file that defines the mysql database structure...

Posted by Maria on October 20, 2011 at 06:00 AM PDT #

Hi Marija

Just sharing a summary of our subsequent thread to resolve your problem ...

You will have to import the database objects into OWB. OWB takes a copy of the definitions from the MySQL database in this import and captures the table metadata in the OWB design repository.

Something went wrong creating the platform for you, you shouldn't get JNPs creating it, to clean up, I suggested;
1. deleting the MySQL modules and locations (if any) that you have created and exiting the client.
2. then from OMBPlus, connect to the repository and;
* drop the MySQL platform - OMBDROP PLATFORM 'MYSQL'
* creating the MYSQL platform using the platform_mysql.tcl script.
* perform OMBCOMMIT
3. Then start the OWB client and try creating the module, location etc...

Finally you were able to import the tables, but columns were skipped, this was down to the capitalization of the type names through the JDBC driver. The URL in the location should also include

?capitalizeTypeNames=FALSE

I mention it in the blog above. After you have added this importing the tables again gets you up and running.

Hopefully this is useful for anyone else with similar problems.
Cheers
David

Posted by David on October 21, 2011 at 06:19 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today