Overview

Oracle database connection issues can be the most frustrating part of application installation and implementation. The many options available with its JDBC and ODBC drivers can be daunting.

This article covers standard Oracle data source configuration procedures for the IRI Voracity data management platform and its component FieldShield and DarkShield data masking tools, CoSort data transformation and NextForm data migration tools, and RowGen test data synthesis and subsetting tool. If you connect to Oracle through TLS, see this article as well.

The IRI software stack uses both JDBC and ODBC drivers for different purposes. IRI Workbench requires JDBC connections for profiling and searching data, parsing metadata, and enumerating tables in relational databases. The IRI back-end SortCL runtime engine requires ODBC connections to move and manipulate data between Oracle (and other relational database) source and target tables.

The use of IRI Workbench is optional. However, its database metadata integration and many wizards and editors make it much easier to create and maintain the job scripts and configuration files used by the SortCL data processing engine. You can also use IRI Workbench for Oracle database management (DBA) tasks like table creation and population, query construction, and SQL execution.

The instructions and examples in this article are based on the Oracle Easy Connect (EZCONNECT) method. If for any reason your Oracle databases can not use EZCONNECT, you will have to rely on documentation provided by Oracle, and other resources, to configure your connections.

Gather Information

There are several details that you will need to know about your database before configuring any connection. This information should be available from a database administrator. If you administer the database yourself, this information can be found in the listener.ora configuration file.

Collect the details shown in Table 1 for the database that requires a connection. This information is needed for both JDBC and ODBC connections. 

Table 1 – Details needed to create a database connection.

More about the information needed:

• Host may be a hostname, or an IP address. Hostnames may be one word, with an implied domain, a fully qualified name including the domain.
• Port is a single integer number. The default is 1521 but may be changed by the database administrator.
• Every database instance has an SID. The SID must be unique if there is more than one instance sharing the same host and port. Sometimes the instance is referenced by a Service Name, instead of the SID. If the database that you are connecting to has a Service Name, you should use it.
• Username should be the Oracle username associated with the schema or schemas you will be working with. It is best to choose a user with a limited scope of schema privileges. Using a SYSTEM user account may result in an unmanageable number of schemas and tables being visible.
• Password must match the specified username.

Once the correct information has been gathered, you are ready to create the ODBC and JDBC connections.

ODBC

The ODBC manager, and drivers, come in two bit architectures, 32-bit, and 64-bit. The bit architecture of the manager and driver must match the bit architecture of the application using the connection. On Windows®, IRI runtime engines are often the 32-bit variety, even when running on a 64-bit operating system. On Linux and Unix, 64-bit applications prevail. 

Before choosing the bit architecture of your ODBC drivers, it is important to know the bit architecture of the IRI SortCL runtime. You can check that with the command:

sortcl /v

The Help > Version Information menu item in IRI Workbench also displays that information. The bit architecture can be found in the version string, as shown in Table 2.

Table 2 – Determining CoSort (SortCL) bit architecture.

Determine the correct bit architecture. You will need it to identify the correct ODBC Manager to use, and to download and install the proper ODBC drivers. The use of ODBC Managers and drivers on Linux, Unix, and the Mac, is more complicated, and beyond the scope of this article. 

The examples here will use the ODBC Manager supplied by Microsoft with Windows. If you use another operating system and need help, email support@iri.com.

ODBC connections are identified with a unique identifier known as a Data Source Name (DSN). Microsoft supplies the ODBC Data Source Administrator program with Windows, for defining and configuring DSNs. 

It is important to choose the ODBC Administrator program with the correct bit architecture to match the application needing database access, and the drivers. Choose the program from Table 3. It is confusing that the 32-bit program is in the folder SysWOW64, while the 64-bit program is in the folder System32. 

Table 3 – ODBC Data Source Administrator programs.

If you have an Oracle database server installed on your workstation, then you probably have an Oracle ODBC driver already installed. You can check for the presence of an Oracle ODBC driver by running the ODBC Data Source Administrator program. 

Look in the Drivers tab for an entry similar to the one shown in Figure 4, The name must start with the word Oracle. You cannot use the driver named Microsoft ODBC for Oracle, this one will not work.

Figure 4 – Oracle driver in ODBC Data Source Administrator program.

The drivers are listed in alphabetical order. If you do not see an Oracle driver present, then you will need to install an Oracle client. Oracle has a different version of the client for each database version. Typically, you can use a newer client with an older database, but not necessarily the other way around. 

Suppose that you want to install the 32-bit client for Oracle 18c. At the time this document was written, the installer was available for download from Oracle. Go to the download page, and look for the text Oracle Database 18c Client (18.3) for Microsoft Windows x86 (32-bit), as shown in Figure 5. 

Download the linked file NT_180000_client.zip. You will need an online Oracle Account to download the client installer; you can register for free if you do not have a login. 

Figure 5 – Oracle client download which includes ODBC and JDBC drivers.

Unzip the downloaded zip file into a temporary folder to install the client. Go to the temporary folder, and look in the client32 folder for setup.exe, you will run this program to install the client. You will need Administrator privileges to install the client. 

Choose an installation type as shown in Figure 6. For just the ODBC driver, JDBC driver, and SQL*Plus command line utility, choose the Instant Client type. If you want to install all of the management tools, choose the Administrator type. 

Follow the instructions in the installer wizard. You can probably accept the default values for any additional options. There is a help button at the bottom of the installer wizard. For more help on this particular installer, Oracle has additional documentation available online.

Figure 6 – Oracle Client installation type.

Once you have the ODBC drivers installed, you can configure a DSN for an Oracle database connection. Run the ODBC Data Source Administrator program specified in Table 3. Select the tab to create either a User DSN or a System DSN. a User DSN will only be visible to the user who created it. 

A System DSN is visible to all users of that workstation. It is most typical to create a User DSN, so select that tab. Select the Add button to create a new connection DSN. Select the correct Oracle driver as shown in Figure 7, then select the Finish button.

Figure 7 – Selecting the Oracle ODBC driver.

You will be presented with the Oracle ODBC Driver Configuration dialog, like the one shown in Figure 9. Name your connection. This is the DSN. It is best to avoid whitespace and punctuation in the DSN, except for possibly underscores, hyphens, and periods. 

Make the name descriptive of the database to which you are connecting. The TNS Service Name can vary based on the Oracle connection method being used. 

The example in Figure 8 uses EZCONNECT, and the example details that were gathered in Table 8. 

Table 8 – Details for an example database connection.

The format for an EZCONNECT connection string is: host:port/ServiceName

Note that there is no separate place in the dialog to save the password. But Oracle allows you to save the password by appending it to the Username, separating it with a forward slash, like shown in the example details in Figure 9. 

Although the password is stored in plain text, the DSN is only visible to the user who is logged onto the workstation at the time, and does not pose a significant security risk in most cases. If you have an IT department with security policies, you may not be allowed to store your password this way. The password may be typed in whenever a connection is established.

It is also possible to store the password in IRI Workbench, where it will be applied to jobs that access the data through this DSN. However, if allowed, saving your password in the DSN as shown below is the most convenient method.

Figure 9 – Driver configuration dialog with example details.

After configuring these details, select the Test Connection button. Hopefully, you will see the Connection Successful result, as shown in Figure 10. 

If there is a problem with your details or connection, or you have omitted storing the password, you will be presented with the Oracle ODBC Driver Connect dialog, as shown in Figure 11. This will give you the opportunity to supply missing details, or change the details already entered.

If edits enable the connection to succeed, they will have to be applied in the configuration dialog shown in Figure 9 before selecting OK. There are four tabs of options at the bottom of the configuration dialog. Change these options only if you are sure that they are needed. 

Figure 10 – A successful connection test.

Figure 11 – A chance to try other details, or enter a password.

Once you have a working ODBC DSN configuration, you can set up the JDBC connection in IRI Workbench with the same details. The ODBC DSN and JDBC connection will be linked together, later, in the Data Connection Registry in the IRI section of Preferences.

JDBC

Selecting up Oracle JDBC drivers is a little easier than ODBC, as JDBC drivers do not have any bit architecture. Depending on how you installed the Oracle ODBC client, you probably have the JDBC drivers already installed. Look for a folder named jdbc, under the install folder for the Oracle client.

Inside the jdbc folder should be a lib folder containing the JDBC java archive (JAR) files. The name of the JAR file to use will vary by the Oracle and Java versions. In general, use the driver file with a name like odbc8.jar, where the number corresponds to the the Java version in use.

If the JAR files are not already installed by either the database server or Oracle client installation, search for an appropriate download at the Oracle website.

IRI Workbench

IRI Workbench is the Integrated Development Environment (IDE) built on Eclipse™ for creating, maintaining, and executing the job scripts for all IRI products: CoSort, FACT, NextForm, RowGen, FieldShield, and DarkShield. It is also the main user interface for the Voracity platform which includes them. 

A JDBC connection within IRI Workbench is used for many purposes, including:

• Examining data values for job design, data classification, and data discovery.
• Browsing table and schema structure.
• Automatic and manual metadata extraction, conversion, and exchange.
• Executing SQL commands and queries, and examining results.
• Bulk extraction and loading of data values to and from flat files.
• Generation of entity relationship (ER) diagrams.

There are several ways database connections can be created in IRI Workbench. The most common way is with the Data Source Explorer (DSE) view. The DSE view is a navigator, normally docked along the left hand edge of the IRI Workbench window. If it has been closed, there are two easy ways to open it again:

• From the main menu, select Window > Perspective > Reset Perspective.
• From the main menu, select Window > Show View > Data Source Explorer.

A database connection in IRI Workbench is referred to as a Connection Profile, sometimes shortened to just Profile. To create a new profile from the DSE view, select the New Connection Profile button from the mini-toolbar in the view, as shown in Figure 12. 

Figure 12 – New Connection Profile toolbar button in DSE.

On the first page of the New Connection Profile wizard, choose Oracle from the list of Connection Profile Types. Give the profile a name, similar to the DSN. Make it descriptive, and avoid unnecessary white space or punctuation. Figure 13 shows this page with the same name used for the ODBC DSN.

Figure 13 – Oracle Connection Profile wizard page.

Select the Next > button to go to the next page of the wizard. You will need to create a new driver definition using the icon button as shown in Figure 14. 

Figure 14 – New Driver Definition button.

The New Driver Definition wizard has three tabs. On the first tab, Name/Type, select the Oracle Thin Driver template version to match your database version. Figure 15 shows the selection of the template for Oracle 18c database.

Figure 15 – Name/Type tab of the New Driver Definition wizard page.

The second tab, JAR List, is for specifying the JDBC driver library file. Select the existing JAR file in the JAR files list. Then select the Edit JAR/Zip button on the right, to browse for the location of the JAR file. 

Find the location of the Oracle JDBC driver library, discussed in the JDBC section of this document. Figure 16 shows the JAR list tab after locating the JDBC driver file. 

Figure 16 – The JAR list after location the JDBC driver file.

The third and final tab is the driver Properties tab, shown in Figure 17. The only change that you may need to make on this tab is to add a trailing slash to the Connection URL property if it is not already present.

Figure 17 – The driver properties tab with the trailing slash on the Connection URL.

Select the OK button to complete the driver definition. Now you can fill in the connection details properties with the same values that were used from Table 8 in the ODBC DSN creation section, and select the Save password checkbox. 

Figure 18 shows the Connection Details page, all ready to try the Test Connection button. If you do not see a Success message box, check the details on the Error message box for additional information. This may indicate a problem with the database server, network connection, or the connection details.

Figure 18 – The completed connection details page, ready to test the database connection.

If the test is successful, you can select the Finish button, and be connected to the database. Even if the test fails, you may still wish to select the Finish button, this will preserve the work that you have done so far, while you research the error. You will be able to edit the connection profile properties later, fix any problems, and try again. 

Figure 19 shows the DSE view after connecting successfully. Oracle uses a large number of internal schemas, and depending on the privileges granted to your user, you may see quite a few schemas besides the ones with the data values of interest to you. 

The connection profile can be filtered at the Schema, and even table, level. This will make it easier to locate and work with the data values of interest to you.  

Figure 19 – DSE view with no schema filtering.

The simplest type of schema filtering will hide all of the schemas except the one owned by the configured user. Right-click the Schemas folder to display the context menu. Select the Properties from the context menu, as shown in Figure 20.

Figure 20 – Selecting schema properties from the context menu.

This will display the Filter Properties dialog, shown in Figure 21. Start by deselecting the Disable filter checkbox near the bottom. Then, fill in the name of the USER schema in the Expression group at the top. Select the Apply and Close button when you are done. 

This will enable simple filtering, showing and processing only the schemas which match the expression. Much more complex filtering can be performed. Check out the IRI Blog article Table Filtering in IRI Workbench for more information.

Figure 21 – Simple schema filtering.

Refresh the Schema list from the same context menu used before. The only schema now visible is the one belonging to the user, as shown in Figure 22.

Figure 22 – DSE view after schema filtering.

IRI Data Connection Registry

All that remains to be done is to reconcile the ODBC DSN and the JDBC connection profile. When creating jobs in IRI Workbench using the JDBC connection profile, it is necessary to know which ODBC DSN corresponds to the same database and user.  

Open the Data Connection Registry in the Preferences by selecting Window > Preferences from the main menu. Navigate to IRI > Data Connection Registry in the tree on the left hand side. The registry page is shown in Figure 23.

Figure 23 – The Data Connection Registry preference page.

There should be a row in the table for every DSN that has been configured in the ODBC Data Source Administrator. If you do not see the DSN that matches your Data Connection Profile, try selecting the Refresh button on the right hand side of the page. 

Highlight the row for the DSN that should be mapped to your Data Connection Profile, then select the Edit button on the right hand side of the page. This will display the Data Connection Registry dialog, shown in Figure 24.

Figure 24 – The Data Connection Registry dialog.

 

You may optionally apply the same schema filter as was used in the Data Connection Profile. If you did not save the username or  password in the DSN, you may optionally save it in this dialog. The only required information in this dialog is the Connection Profile at the bottom. 

Select the OK button when you are done. Make sure that the checkbox in the Active column is selected, as shown in Figure 25, and then select the Apply and Close button.

Figure 25 – Properly configured DSN entry in the Data Connection Registry.

Congratulations, you are now ready to begin working with the data in your connected Oracle database. See this index page of how-to Blog articles on working with Oracle (and other) data from IRI Workbench.

 

Follow along with our YouTube video!

Glossary