Being Groovy without an ODI IDE

Many people using Oracle Data Integrator (ODI) are already familiar with how Groovy can be used to script many tasks within ODI, often by running the Groovy scripts from within the ODI Integrated Development Environment (IDE). However, automating ODI tasks outside of the IDE can also be useful in various scenarios, such as improving productivity, reducing errors, and enabling continuous integration and delivery in software development projects.

In this blog post, we will show you how to execute Groovy scripts outside of the ODI IDE, opening up the opportunity to include the scripts in workflows and pipelines. We will also provide more detailed explanations and examples of some of the libraries and classes used in the Groovy script, as well as some tips and best practices for writing efficient and effective scripts.

Here are some real-world applications where automating ODI tasks outside of the IDE can be useful:

• Automated peer review of Packages, Mappings, Procedures, etc
• Exporting objects from ODI, checking them into Git and eventually importing them into other environments as part of a release
• Analysing new JSON event data and generating the Package and Mappings to extract information into normalised table structures

Using Groovy outside of the IDE is a matter of running Java with the correct set of parameters so that it can find the necessary JAR files. Here is an example of the command we typically use (with the backslash splitting the command across lines in Linux):

java -cp $ODI_HOME/oracle_common/modules/oracle.jdbc/ojdbc8.jar:$ODI_HOME/oracle_common/modules/thirdparty/groovy-all-2.5.14.jar:$ODI_HOME/odi/sdk/lib/* \
          groovy.lang.GroovyShell  \
          my_groovy_script.groovy \
          groovy_odi.properties

In the command we are using Java CLI tool to run our Groovy script. You can read about Java at this page:

https://docs.oracle.com/en/java/javase/19/docs/specs/man/java.html

In this example, we are using a machine that has the ODI 12.2.1.3 client installed, and the Groovy script may differ for various ODI releases. We are leveraging ODI and Groovy libraries, so we need to provide Java with information about where they are located, which we do with the classpath option (-cp). It's important to verify that the version of your ODI SDK aligns with the version of your work repository. In this example we know that the work repository and our ODI client have matching versions.

To keep things simple and concise we have provided you with the most critical set of libraries for the classpath to get your Groovy scripts working outside of the IDE. You may notice a bit of noise warning some classes are not found. These are not critical classes but if you wanted to suppress the warnings more JAR files can be added to the classpath.

In our command, we have used a variable to point to the installation directory of the ODI client with something like:

export ODI_HOME=/my/install/path

Then we supply Java with the name of the main class we would like to execute, in this case, groovy.lang.GroovyShell. You can read about the Groovy shell in the documentation here:

https://groovy-lang.org/groovysh.html

The next parameter is the name of the Groovy script we’ve created and would like to execute. The final parameter is the name of a properties file we have created.

In this example we will use a file with the following contents:

master_jdbc=jdbc:oracle:thin:@hostname.network:1521/SID
master_jdbc_driver=oracle.jdbc.OracleDriver
master_username=ODI_SCHEMA
master_password=ODI_PASSWORD
work_name=WORK_REPOSITORY
odi_username=MY_GROOVY_USERNAME
odi_password=MY_GROOVY_PASSWORD

The first five lines of the file are describing the information needed to connect to the database hosting the ODI repository. The last two lines are describing the username and password for the ODI user that you want groovy to execute code, this user will need the appropriate permissions to complete the required tasks.

Now, here is an example of how to use the properties file to connect to the ODI repository:

// import all the libraries needed for our script
import oracle.odi.core.config.*;
import java.util.*;
import java.text.*;
import java.io.*;
import oracle.odi.core.*;
import oracle.odi.core.security.*;

// get the file from the first argument supplied to this script
// load the file into a properties object
String properties_path = args[0];
Properties properties_file = new Properties();
FileInputStream inp = new FileInputStream(properties_path);
properties_file.load(inp);
inp.close();

// create an object that ODI can use for JDBC pooling
PoolingAttributes pool_attrs = new PoolingAttributes();

// use our properties to create some ODI objects 
MasterRepositoryDbInfo master_repo = new MasterRepositoryDbInfo(properties_file.getProperty("master_jdbc"), properties_file.getProperty("master_jdbc_driver"), properties_file.getProperty("master_username"), properties_file.getProperty("master_password").toCharArray(), pool_attrs);
WorkRepositoryDbInfo work_repo = new WorkRepositoryDbInfo(properties_file.getProperty("work_name"), pool_attrs);
OdiInstanceConfig odi_config = new OdiInstanceConfig(master_repo, work_repo);
OdiInstance odiInstance = OdiInstance.createInstance(odi_config);

// authenticate using our ODI user
Authentication  auth = odiInstance.getSecurityManager().createAuthentication(properties_file.getProperty("odi_username"), properties_file.getProperty("odi_password").toCharArray());
odiInstance.getSecurityManager().setCurrentThreadAuthentication(auth);

// we now have an odiInstance that can be used in the same way as you would from within the IDE

// do something

// and then disconnect
odiInstance.close()

In this example, we first import all the libraries needed for our script. We take the path provided as an argument to the script and load the properties file into a Properties object and create an object that ODI can use for JDBC pooling. We use the properties from the properties file to create some ODI objects, including a MasterRepositoryDbInfo object and a WorkRepositoryDbInfo object. We then create an OdiInstanceConfig object with these repository objects and use it to create an OdiInstance.

We authenticate using our ODI user by creating an Authentication object with the username and password from the properties file, and then setting it as the current thread authentication for the OdiInstance. We now have an OdiInstance that can be used in the same way as you would from within the IDE. Finally, we do something (such as executing a task), and then disconnect by closing the OdiInstance.

You need to remember its not just a case of authenticating against the ODI repository database but you will also need to make sure you setup an ODI user that will be used to perform the tasks you need.

Think of the endless automation opportunities available to you! If you want some inspiration check out our other blog posts.