Optim Open Data Manager (ODM) allows third-party applications to access Optim archived data using standard mechanisms, such as open database connectivity (ODBC), Java™ database connectivity (JDBC), and XML. With Optim ODM, business reporting tools can retrieve Optim archived data as if the data resided in relational databases. Optim ODM is implemented using Optim Connect (server and thin clients) with a special driver named PST_GDB to access Optim archive files and collections.
Optim provides two thin clients for providing connectivity to third-party applications: Optim Connect ODBC thin client and Optim Connect JDBC thin client. The use of the JDBC thin client is similar in all platforms that Optim supports, but for the ODBC thin client, the setup steps vary based on platform. This article focuses on the process for setting up the Optim Connect ODBC thin client for Linux/UNIX environments. It will also pose considerations on how to use the JDBC thin client and XML utility.
For more information on how to configure the Optim Connect Server, Optim ODBC thin client, driver manager, and the ODBC DSN on Linux and UNIX environments, please refer to the following topics. For more information on how to create a ODBC DSN and using the Optim Connect ODBC thin client on Windows, see "Create ODBC data source using ODBC Administrator in Windows."
As Figure 1 illustrates, Optim ODM allows universal access to Optim archived data.
Figure 1. Access from applications to active application data and archives
Although some applications can integrate with ODBC drivers directly, the common scenario is to define an ODBC data source name (DSN) and have the application using the ODBC DSN through the driver manager. An ODBC DSN provides information related to the connection, such as server IP, port, and user/password, and driver.
A driver manager carries out a number of tasks for the application, including:
- Ensuring that the proper driver is loaded or unloaded
- Carrying out validation tasks
- Calling ODBC versions (3.5 to 3.0 to 2.0) and data mapping
Most calls to the driver manager get passed onto the loaded driver to be further processed, but that is of little concern to the application. Some advantages to using an ODBC driver manager also include:
- Portable data access code
- Runtime binding to a data source
- Ability to easily change the data source information, especially if multiple applications share the same ODBC data source
The driver manager usually provides the interface for applications to connect and run SQL dynamically, loading the specified driver for the application to connect. In the Optim case above, when installing Optim Connect in a non-Windows environment, it is necessary to integrate the Optim Connect driver with a third-party driver manager. The two most commonly used drivers managers for Linux and UNIX are unixODBC and iODBC. In this article, we will use unixODBC. Steps for setting up unixODBC and iODBC are similar, and the differences usually rely on file paths or filenames.
As stated, some applications can integrate with ODBC drivers directly, but use of a driver manager is recommended.
Figure 2. Using a driver manager
While Windows platforms provide a built-in ODBC driver manager to configure ODBC access, Linux and UNIX environments require manual setup of a third-party driver manager. On Windows, the ODBC thin client is rather easily installed and configured, whereas on Linux or UNIX, it must be manually configured.
Whether the client is on a Windows or Linux/UNIX environment, after the environment is properly set it is necessary to define an ODBC DSN.
Access to archived data using ODBC follows this process:
- The application sends requests to retrieve data using the ODBC DSN/Optim Connect ODBC Driver, which then sends requests back to the Optim Connect Server.
- Optim Connect Server refers to the Optim Server to retrieve data on archive file/collection details.
- Optim Server looks for metadata of archive file/collection from Optim Directory.
- If the archive file/collection exists, Optim Connect Server will attempt to read it using the PST_GDB driver.
- The archive file/collection will be exposed as a data source.
Figure 3. Accessing archive data using Optim ODBC thin client
The following software products are used in this article:
- RedHat Enterprise Linux (RHEL) 5 64-bit
- unixODBC 32-bit
- Optim for RHEL 5
- Optim for Windows®
- Cognos BI 10 64-bit for Linux
Optim provides ODBC thin clients within the Windows installation folder for ODBC 2.5 and 3.5 standards. It also supports AIX®, HP-UX, ItaniumVMS, Linux (Red Hat), Solaris, and Windows, providing drivers in 32- and 64-bit versions for each platform. If you decide to use a 64-bit driver, a 64-bit driver manager must be used, whereas a 32-bit driver requires a 32-bit manager. As in Cognos System Requirements, Cognos 64-bit will integrate with 64-bit Java environments only. Other integration points require third-party 32-bit libraries. Therefore, unixODBC 32-bit will be used in this article.
To configure the environment, in the next section we will assume your Optim environment is already set as follows:
- Optim First Workstation and Optim Connect Studio on Windows are installed and configured.
- Optim Server on Linux/UNIX and Optim Connect Server are installed and configured.
- DBAlias and Archive Request are configured, and data is archived.
If any step above is missing, please refer to the manual on how to complete them.
We will assume that the prerequisites above are satisfied since the focus of this article is to show the steps for setting up the Optim Connect ODBC thin client in a Linux/UNIX environment.
Before setting the ODBC DSN, make sure that the ODM data source is properly set and that the driver is available. In Optim Connect Studio, follow these steps to ensure that the Optim Connect Server is properly set:
- Add the information related to where the ODM server is operating.
- Click on Add Machine... to add information to
the Optim Connect Server.
Figure 4. Adding a machine to the server
- node4 is our Linux server where Optim Server
and Optim Connect Server are running. The three fields
explained below are mandatory:
- Host name/IP address: Use the hostname if your Windows workstation can resolve the server name; otherwise, specify the IP address.
- Port: The default for Optim Connect Server is 2551.
- Display name: The alias to be displayed in Optim Connect Studio.
Figure 5. Specifying the Optim Connect server details
- Click on Add Machine... to add information to the Optim Connect Server.
- Update the Workspace server mode.
- Expand the Daemon folder and open the IRPCD daemon.
Figure 6. Opening the IRPCD daemon
- Click on the Server Mode tab.
Figure 7. Selecting Server Mode
- To access Optim Archive Files efficiently, update the
server mode, selecting singleClient. In this
mode, each client receives a dedicated server process.
Figure 8. Selecting single client
- Open the bindings definition. Under the bindings folder,
select Open as XML.
Figure 9. Opening the bindings definition
- Expand the Daemon folder and open the IRPCD daemon.
- Edit the data sources definition and add the applicable archive file
or archive collection.
- Add the data-source information (name and type, which must be
PST_GDB) as the following:
- name: Name of the data source for the archive file or the archive file collection.
- type: Type of the data source. For archive file or archive file collections, use PST_GDB.
- dirdb: The optimdir that contains the metadata about the archive file or the archive file collection.
- collection: Name of the archive file collection. Not applicable if using an archive file.
- arcv_file: The full path and name of the archive file. Not applicable if using an archive file collection.
- If using an archive file collection, provide the same
collection name stored in the Optim directory.
Listing 1. Collection name
<datasource name="ORDERS" type="PST_GDB"> <config COLLECTION="ORDERS" DIRDB="LNOPTDIR"/> </datasource>
- If using an archive file, provide the full path to where it is stored.
Listing 2. Path to archive file
<datasource name="ORDERS" type="PST_GDB"> <config ARCV_FILE="/opt/IBM/Optim/rt/data/ARC20100112_002.AF" / DIRDB="LNOPTDIR"/> </datasource>
- Add the data-source information (name and type, which must be PST_GDB) as the following:
For implementation purposes, the best practice is to always make use of archive file collections. Providing only the collection makes it easier to gather Optim archived data without having to configure a data source for every archive file. Since it uses information stored in the Optim directory, it also eases maintenance in case the archive file path changes.
You should now have the Optim Connect Server listening on port 2551 and referring to the archive file as the ORDERS data source. The next step will involve setting up the 32-bit unixODBC:
- Locate and extract the Optim ODBC thin client.
- Set up both odbc.ini and odbcinst.ini files.
- Export the ODBCINI and LD_LIBRARY_PATH variables for Linux and Solaris. For AIX and HP-UX, LIBPATH and SHLIB_PATH must be used respectively instead of LD_LIBRARY_PATH.
Optim brings along with the installation files the ODBC thin clients under the $PSTHOME/rt/odm/install folder. The thin clients can be listed as below.
Listing 3. Locate the Optim Connect ODBC thin client in $PSTHOME/rt/odm/install directory
[optim@node4 install]$ pwd /opt/IBM/Optim/rt/odm/install [optim@node4 install]$ ls OptimConnect*ODBC*.tar.Z OptimConnect-53211-ODBC_Thin_Client-linuxrh.AS3.tar.Z OptimConnect-53214-ODBC_3_5_Thin_Client-linuxrh.AS3.tar.Z
If you need ODBC thin clients for any other platform, they are located in the Windows installation folder under [installation_folder]\Optim Connect\Thin Clients\ODBC Thin Clients\.
After locating the ODBC thin clients, you need to select the desired version to be used. In this example we will use the Optim thin client that supports ODBC 3.5 standards:
- The first step for setting up the Optim thin client is to extract the
.tar.Z file and place the contents in a folder providing access to the
driver and the libraries.
Listing 4. Use the complete path of thinapi.so (legacy version) or thinapi3.so (version 3.5)
[optim@node4 3.5]$ pwd /usr/optimconnect/3.5 [optim@node4 3.5]$ ls aisconf.h libnavshr.so nav_util samples brand.bin libnvbaseshr.so nvdb_disam.so thinapi3.a libacodbc3shr.so navcli3.h odbcinc.h thinapi3.so
- The second step is to create the /etc/odbcinst.ini and /etc/odbc.ini
files. The odbcinst.ini file is related to the driver information,
pointing the driver name and location. The odbc.ini file is related to
the DSN (data source name) information, providing the driver to be
used (the one configured in /etc/odbcinst.ini) and the connection
information such as the BindURL and the data-source name. It also
supports additional parameters, such as user and password. Below is how
each file should be structured. Also remember that the driver must
have executable permission.
Listing 5. The /etc/odbc.ini and the /etc/odbcinst.ini structure
For file /etc/odbcinst.ini, containing the driver information [Driver1] Description = "Description for Driver1" Driver = "/filepathfordriver1/driver" FileUsage = 1 [Driver2] Description = "Description for Driver2" Driver = "/filepathfordriver2/driver" FileUsage = 1 For file /etc/odbc.ini, containing the ODBC DSN information [ODBC Data Sources] DSN1="1st DSN in this example" DSN2="2nd DSN in this example" [DSN1] Driver="Driver1" Description="Description related to this DSN1" BindURL="server":"port"/Navigator DefTDPName="RemoteDataSourceName" [DSN2] Driver="Driver2" Description="Description related to this DSN2" BindURL="server":"port"/Navigator DefTDPName="RemoteDataSourceName"
Note that many DSNs can be defined. In this example, our DSN is configured as the outputs below from /etc/odbc.ini and /etc/odbcinst.ini.
Listing 6. The /etc/odbc.ini and the /etc/odbcinst.ini files for this example
[optim@node4 3.5]$ cat /etc/odbcinst.ini [OptimConnect] Description = ODBC for OptimConnect Driver = /usr/optimconnect/3.5/thinapi3.so FileUsage = 1 [optim@node4 3.5]$ cat /etc/odbc.ini [ODBC Data Sources] ORDERS=ODM Data Source [ORDERS] Driver=OptimConnect Description=Optim Connect ODBC access to ORDERS BindURL=node4:2551/Navigator DefTDPName=ORDERS
- The third and final step for setting up the unixODBC with the Optim
ODBC thin client is to set up the variables in the user log-on profile.
Export the ODBCINI variable (that will be used for other
applications) and the LD_LIBRARY_PATH variable (which contains the
location where the driver and its libraries resides). The following
commands can be added in the system profile (in case of RHEL
/etc/profile) or in .bashrc for an individual user.
Listing 7. Export the library path and the ODBCINI globally or for the user who will run the application
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/optimconnect/3.5 export ODBCINI=/etc/odbc.ini
Test the ODBC connection. In the following example, we are executing a test query using nav_util (Optim Connect Tool) command line.
Listing 8. Connect to the ODM data-source using nav_util utility (part of ODM package)
[root@node4 ~]# nav_util execute ORDERS NavSQL > select count(*) from OPTIM_ORDERS; $$CALC_1 845 1 rows returned
Listing 9. Connect to the ODM data-source using isql utility (part of unixODBC package)
[root@node4 ~]# isql -v ORDERS +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> select count(*) from OPTIM_ORDERS; +--------------+ | $$CALC_1 | +--------------+ | 845 | +--------------+ SQLRowCount returns -1 1 rows fetched
Compare the results from executing the same query through nav_util and isql. If both utilities produced consistent results, you should have everything properly configured. Time to proceed with Cognos BI 10.1 configuration.
IBM Cognos BI is a business analytics tool that provides reporting capabilities to data stored in the most popular RDBMS. There are multiple guidelines around the web showing how you can configure reports using Cognos Framework Manager. For more information on how to access Optim archived data and create reports with Cognos Framework Manager, please refer to "Make the archive data accessible to report writers using Cognos Framework Manager." The process should be the same as long as the Cognos application residing on Linux/UNIX systems can connect to the Optim Connect Server. For doing that, we will combine the steps applied above with a new connection using the same DSN defined in the previous section.
For testing the ODBC connection with IBM Cognos installed in a Linux/UNIX environment:
- Specify the connection Type (ODBC)
- Provide the connection name and description
- Provide the DSN name specified in the odbc.ini file and authentication credentials if applicable
- Test the connection
Here are step-by-step instructions:
- When creating the data-source connection in IBM Cognos select
ODBC type and click Next.
Figure 10. Selecting connection type
- Define the Connection Name, Description, and Screen tip and click
Figure 11. Specifying name and description in the new data source wizard
- For the ODBC data source, use the name in the
/etc/odbc.ini file configured earlier.
Figure 12. Specifying the ODBC connection string
If your archive file collection is secured with user ID and password, also provide the authentication information in the Signon section.
- Scroll to the bottom of the page and click Test the
Figure 13. Testing the connection
- Review the DSN information. If the name is correct, select
Figure 14. Testing the connection
- Check the test status. Cognos will attempt to connect using the ODBC
DSN configured earlier. If connection is successful, the
screen displays the status Succeeded.
Figure 15. Viewing the results
- Go back to the previous screen, click Finish.
Figure 16. Previous screen for Optim archive file
The connection will then be available for being used with Cognos Framework Manager.
Optim through the Optim Connect thin client provides not only ODBC connectivity but also JDBC connectivity. The process for setting up the JDBC drivers is the same on Windows and Linux/UNIX platforms. In a Linux/UNIX Optim Server installation, the set of Java libraries can be found in $PSTHOME/rt/odm/java/Averify/. The Optim Connect JDBC thin client provides a type-III JDBC driver for JDBC 2. The set of JAR files necessary for providing applications with JDBC connectivity to Optim ODM are:
The process of setting up Optim Connect JDBC thin client usually involves copying the files listed above to the application's library folder and restarting the application as described in "Mash active and archived data using IBM Mashup Center and Optim." See the section "Editing the mashup application to track both active and archive orders."
We have seen that Optim through ODM allows third-party applications to
access Optim archived data via ODBC/JDBC. Optim ODM is packaged with an
archive file to XML converter called atoxml.jar. This allows users to
convert Optim archived data into XML format on demand.
This utility can be used on Linux, UNIX, and Windows, and
requires Java 1.4 or later to run. On Linux/UNIX environments, it can be
found at $PSTHOME/rt/odm/java/. To invoke the converter,
either double-click the atoxml.jar file or issue the command
java -jar atoxml.jar.
The following example shows that an Optim archived record is being converted to XML format.
|Server||node4:2551||Optim Connect Server name, followed by ":" and default server port 2551|
|Data source||ORDERS||Pre-defined ODM data source for archive file in Optim Connect Studio|
|Maximum rows||10||Maximum number of rows to convert|
|Root name||ORDXML||Name of root XML tag|
|Row-set name||orders||Name of XML tag in which data is placed|
|Generate||XML||XML generates XML file; schema generates XML schema file|
|Mode||Element||Element will generate an XML tag for each data mode value; attribute will generate row-tag attributes for each data value|
|SQL select||Select * from OPTIM_ORDERS;||Specify all (*) data as selection criteria|
The result will be an XML as in the figure below.
Figure 17. Utility to convert archived data to XML
Below are some common errors you may have missed during ODBC setup on Linux or UNIX. Here are some suggested resolutions to these common mistakes:
- wrong ELF class: ELFCLASS64 — This can be found if you have installed unixODBC 64-bit or compiled it from the source without using 32-bit flags while using 32-bit drivers.
- unixODBC home not found — This can occur if the ODBCINI variable was not exported or does not contain the correct location, or the folder where odbc.ini resides does not have the proper permissions.
- cannot open shared object file — This can occur if LD_LIBRARY_PATH, LIBPATH, or SHLIB_PATH variables were not exported or do not contain the correct path, or the folder where the Optim ODBC thin client was extracted does not have the proper permissions.
Optim provides easy ongoing access to Optim archived data through ODBC and JDBC. While configuring the Optim Connect JDBC thin client on Windows and Linux/UNIX environments may be straightforward, using the Optim Connect with ODBC on Linux/UNIX environments may require some manual work since a third-party ODBC driver manager is required. The steps in this article will help you to configure ODBC access to your archived data from a third-party reporting tool in a Linux/UNIX environment.
Learn about InfoSphere Optim Solutions.
Be sure to read Cognos BI 10 — Testing the ODBC connection.
Read "Make the archive data accessible to report writers using Cognos
Framework Manager" for more information.
Learn more by reading "Editing the mashup application to track both active and archive
Find out more about unixODBC.
Visit iODBC.org to learn more.
Explore the Cognos
Read "IBM Embraces Open Source: How to Set up unixODBC on DB2 for
- Learn more about Information Management at the developerWorks Information Management
zone. Find technical documentation,
how-to articles, education, downloads, product information, and
- Stay current with
developerWorks technical events and webcasts.
- Follow developerWorks on
Get products and technologies
- Build your next
development project with
IBM trial software,
available for download directly from developerWorks.
- Now you can use
DB2 for free. Download DB2 Express-C, a no-charge
version of DB2 Express Edition for the community that offers the same core
data features as DB2 Express Edition and provides a solid base to build
and deploy applications.
Alan Fischer e Silva is a software engineer in the Optim and Guardium Technology Ecosystem team at the IBM Canada Lab. He provides end-to-end InfoSphere Optim and Guardium enablement from skills transfers and demos/PoTs/PoCs to product best practices, helping accelerate partner success and progress on the InfoSphere Optim and InfoSphere Guardium products.