Deploying JDBC drivers with a JEE ODI Agent

In this recipe, we will be looking at how to deploy additional JDBC drivers to a JEE ODI Agent. ODI is delivered out of the box with support for many relational databases, however there are times when additional data sources may be needed. The ODI generic knowledge modules support virtually any Type 4 JDBC drivers. If the new source does have a Type 4 JDBC driver, this driver can be used to extract data from the source and can also be used to load to a target.

In this recipe, we will use a JDBC driver that is not shipped out of the box with ODI. Microsoft SQL Server 2012 was released after ODI 11.1.1.6.0 was released. Although there is no specific support listed in the ODI Certification Matrix for ODI 11.1.1.6.0 and SQL Server 2012, the new JDBC driver can be deployed to ODI Studio. Once the driver is deployed to the ODI Studio and the ODI JEE Agent, a SQL Server 2012 data server can be set up in the ODI topology and then used within an integration.

Getting ready

This recipe we will use the Microsoft SQL Server 2012 JDBC driver as an example. This file can be downloaded from Microsoft.com and deployed to ODI as outlined in the following section. No other prerequisites are required.

How to do it…

1. Download the Microsoft JDBC Driver SQL 4.0 with SQL Server 2012 support from Microsoft.com.

2. First, make sure to exit all ODI processes on the machine where the driver will be deployed.

3. Copy the .jar file to the odi\oracledi\userlib path, which is located in the appdate environment variable. For example, on Windows 2008 Server the directory is C:\Users\dsgray\AppData\Roaming\odi\oracledi\userlib\.

4. Restart ODI Studio, go to topology, and add a new data sever to Microsoft SQL Server Technology.

   

5. Set Name, User, and Password to match your SQL 2012 Server settings.

   

6. On the JDBC tab, click the search icon to get the list of JDBC drivers. Notice that the new driver now shows in the list Microsoft SQL Server 2005 Driver for JDBC. Select this driver and click on OK.

   Note

   This driver shows SQL Sever 2005, however this is from the description embedded in the driver, not ODI.

   

7. Set the JDBC URL appropriately for your SQL 2012 Server.

   

8. Click on the Test Connection button.

   

9. We have now successfully tested the new driver within ODI Studio. However, we can also test the ODI JEE agent here by selecting OracleDIAgent that was setup in the previous Deploying a JEE ODI Agent recipe.

10. Click on the Test Connection button.

    

    Note

    Notice there was an error during this test. This was expected as the ODI JEE Agent could not load the appropriate class since the SQL Server 2012 JDBC .jar file has not been copied to the WebLogic server.

11. Before copying the SQLServer2012 JDBC .jar file to the Weblogic server, the ODI domain needs to be shut down. Shut down the ODI JEE Agent base domain.

12. Once the domain is completely shut down, copy the SQLServer2012 JDBC jar to the Middleware_Home\user_projects\domains\base_domain\lib path.

    1. Restart the ODI JEE Agent domain, base domain.

    2. Within ODI Studio, click on the Test Connection button again.

    

13. The new JDBC driver has now been successfully set up on the ODI JEE Agent.

There's more...

This recipe demonstrated adding a new relational JDBC driver to the ODI JEE Agent. JDBC drivers do exist for non-relational targets and sources such as Microsoft Excel, or also for applications such as SalesForce.com. Setting up the additional JDBC drivers for these types of technologies would follow the same steps as outlined for the SQL Server 2012 driver.