Introduction

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.

Back to top

ODBC and driver manager background

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.

Back to top

Optim Connect and ODBC data connectors

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:

1. 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.
2. Optim Connect Server refers to the Optim Server to retrieve data on archive file/collection details.
3. Optim Server looks for metadata of archive file/collection from Optim Directory.
4. If the archive file/collection exists, Optim Connect Server will attempt to read it using the PST_GDB driver.
5. The archive file/collection will be exposed as a data source.

Back to top

Prerequisites

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:

1. Optim First Workstation and Optim Connect Studio on Windows are installed and configured.
2. Optim Server on Linux/UNIX and Optim Connect Server are installed and configured.
3. 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.

Back to top

Setting up the Optim Connect Server

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:

1. Add the information related to where the ODM server is operating.
   1. Click on Add Machine... to add information to the Optim Connect Server.
      
      Figure 4. Adding a machine to the server
      
      
      
   2. node4 is our Linux server where Optim Server and Optim Connect Server are running. The three fields explained below are mandatory:
      1. Host name/IP address: Use the hostname if your Windows workstation can resolve the server name; otherwise, specify the IP address.
      2. Port: The default for Optim Connect Server is 2551.
      3. Display name: The alias to be displayed in Optim Connect Studio.
      
      
      Figure 5. Specifying the Optim Connect server details
      
      
      
2. Update the Workspace server mode.
   1. Expand the Daemon folder and open the IRPCD daemon.
      
      Figure 6. Opening the IRPCD daemon
      
      
      
   2. Click on the Server Mode tab.
      
      Figure 7. Selecting Server Mode
      
      
      
   3. 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
      
      
      
   4. Open the bindings definition. Under the bindings folder, select Open as XML.
      
      Figure 9. Opening the bindings definition
      
      
      
3. Edit the data sources definition and add the applicable archive file or archive collection.
   1. 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.
   2. 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>

      
      
   3. 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>

      
      

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.

Back to top

Setting up unixODBC and Optim Connect ODBC thin client

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:

1. Locate and extract the Optim ODBC thin client.
2. Set up both odbc.ini and odbcinst.ini files.
3. 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.

[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:

1. 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

   
   
2. 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

   
   
3. 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.

	
	[root@node4 ~]# nav_util execute ORDERS

	NavSQL > select count(*) from OPTIM_ORDERS;


	$$CALC_1        

	845             

	1 rows returned
	

	
	[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.

Back to top

Testing Cognos access to Optim archived data

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:

1. Specify the connection Type (ODBC)
2. Provide the connection name and description
3. Provide the DSN name specified in the odbc.ini file and authentication credentials if applicable
4. Test the connection

Here are step-by-step instructions:

1. When creating the data-source connection in IBM Cognos select ODBC type and click Next.
   
   Figure 10. Selecting connection type
   
   
   
2. Define the Connection Name, Description, and Screen tip and click Next.
   
   Figure 11. Specifying name and description in the new data source wizard
   
   
   
3. 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.

4. Scroll to the bottom of the page and click Test the connection.
   
   Figure 13. Testing the connection
   
   
   
5. Review the DSN information. If the name is correct, select Test.
   
   Figure 14. Testing the connection
   
   
   
6. 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
   
   
   
7. 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.

Back to top

Accessing archived data using JDBC

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:

• nvjdbc2.jar
• nvapispy2.jar
• nvlog2.jar

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."

Back to top

Accessing archived data using XML

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.

The result will be an XML as in the figure below.

Back to top

Error handling

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:

1. 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.
2. 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.
3. 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.

Back to top

Conclusion

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.

Resources

Learn

• 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 orders."
  
  
• Find out more about unixODBC.
  
  
• Visit iODBC.org to learn more.
  
  
• Explore the Cognos system requirements.
  
  
• Read "IBM Embraces Open Source: How to Set up unixODBC on DB2 for AIX5L."
  
  
• Learn more about Information Management at the developerWorks Information Management zone. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Stay current with developerWorks technical events and webcasts.
  
  
• Follow developerWorks on Twitter.
  
  

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.
  
  

Discuss

• Check out the developerWorks blogs and get involved in the developerWorks community.
  
  

About the author

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.

Rate this article

Comments

Back to top

Table of contents

• Introduction
• ODBC and driver manager background
• Optim Connect and ODBC data connectors
• Prerequisites
• Setting up the Optim Connect Server
• Setting up unixODBC and Optim Connect ODBC thin client
• Testing Cognos access to Optim archived data
• Accessing archived data using JDBC
• Accessing archived data using XML
• Error handling
• Conclusion
• Resources
• About the author
• Comments

Next steps from IBM

• Try: Cognos Business Intelligence
• Demo: Video: Cognos 8 BI
• Community: Cognos BI
• Buy: Cognos Business Intelligence

Dig deeper into Information Management on developerWorks

IBM SmartCloud trial. No charge.

Unleash the power of hybrid cloud computing today!

---

Public and private solutions in one trial.

Special offers