Tuesday, August 16, 2011

Connecting to MySQL Database using ODI

While working with Oracle Data integrator 11g and MySQL, we come across a strange situation where ODI does not provide any default JDBC connector (in-built). Though MySQL is shown up as one of the technologies in Topology Manager, but when user tries to establish a physical connection, there is no pre-defined connector available in ODI.

Steps to connect to MySql
ODI is a java based tool using JAR files to establish connection to different technologies. On similar grounds, we need to provide Java based driver file to it.

Download MySQL JDBC connector
MySQL JDBC connector is available on MySQL site for download. Download the MySQL Connector/J from following URL: http://dev.mysql.com/downloads/connector/j/

Unzip the downloaded Drivers
Unzip the downloaded file. We will have the following set of files after unzip. In this example, we are using MySql drivers’ ver 5.1.17.

Placing JAR file
Place this JAR file in the following 2 locations:

a.    %ODI_HOME%\oracledi\agent\drivers. %ODI_HOME% is the location of complete installation directory for ODI under Oracle\MiddleWare. Usually the path is C:\Oracle\Middleware\<ODI_HOME>
b.    <UserProfile>\Application Data\odi\oracledi\userlib. <Userprofile> is the directory with the same name as the user. It has the following path in Windows C:\Documents and Settings\<user>
Note: Restart your ODI studio (client).
Establish connection to MySQL
Go to Physical Topology and add new server under the technology MySQL. Provide connection name, server name, user id and password. Move to JDBC tab.

Since no driver is available as built-in, we need to write the driver name & URL. Provide the following details:
JDBC Driver: com.mysql.jdbc.Driver
JDBC URL: jdbc:mysql://<servername>:<port>
Port for MySql by default is 3306. You can provide port as 3306, incase you have used the default port, or contact MySql Administrator for details.