Optim Open Data Manager and Optim Connect thin clients for Linux and UNIX environments

Accessing Optim archived data from pre-packaged applications on Linux and UNIX platforms

Legal compliance and business requirements often require organizations to retain data for certain period of time. IBM® InfoSphere® Optim™ offers a database archive solution for archiving data from either production databases (to control data growth) or from legacy applications (for application retirement purposes). Through Optim Open Data Manager (ODM), Optim can provide continuous access to archived data using open standards such as ODBC, JDBC, and XML. Learn the difference among the available methods for accessing Optim archived data on Linux®/UNIX® environments and how to configure ODBC access to Optim archived data using reporting tools, such as IBM Cognos® in a Linux environment.

Share:

Alan Fischer e Silva (alanfischer85@gmail.com), Software Engineer, IBM

Alan Fischer e SilvaAlan 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.



10 May 2012

Also available in Chinese Russian

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.

Figure 1. Access from applications to active application data and archives
Diagram shows how Optim can archive inactive data from production databases, allowing universal access to the archived data and providing restore on demand

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.

Figure 2. Using a driver manager
Diagram shows how applications can integrate with a driver manager to load the connection information and the driver to be used instead of loading the driver directly

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.
Figure 3. Accessing archive data using Optim ODBC thin client
Diagram shows how Optim allows access to the archived data via Optim ODBC thin client

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.


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
      Image shows using the Optim Connect Studio click Add Machine
    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
      Image shows how to provide the Optim Connect Server machine details
  2. Update the Workspace server mode.
    1. Expand the Daemon folder and open the IRPCD daemon.
      Figure 6. Opening the IRPCD daemon
      Image shows how to expand the Daemon folder and open the IRPCD Daemon
    2. Click on the Server Mode tab.
      Figure 7. Selecting Server Mode
      Image shows Server Mode tab
    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
      Image shows how to update the workspace mode to SingleClient
    4. Open the bindings definition. Under the bindings folder, select Open as XML.
      Figure 9. Opening the bindings definition
      Image shows that under the bindings folder, open the Navigator as a XML
  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.


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.

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:

  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.

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.


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
    Image shows selecting ODBC ans 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
    Image shows Optim Archive file; Description: Optim archive file containing orders older than 2 years; screen tip: OPTIM_ORDERS
  3. For the ODBC data source, use the name in the /etc/odbc.ini file configured earlier.
    Figure 12. Specifying the ODBC connection string
    Image shows ODBC data source is ORDERS

    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
    Image shows highlighted test connection
  5. Review the DSN information. If the name is correct, select Test.
    Figure 14. Testing the connection
    Image shows name correctly set to ORDERS
  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
    Image shows status succeessful
  7. Go back to the previous screen, click Finish.
    Figure 16. Previous screen for Optim archive file
    Image shows Cognos6

The connection will then be available for being used with Cognos Framework Manager.


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


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.

ATOXML utility parameters
ParametersValueDescription
Servernode4:2551Optim Connect Server name, followed by ":" and default server port 2551
Data sourceORDERSPre-defined ODM data source for archive file in Optim Connect Studio
Maximum rows10Maximum number of rows to convert
Root nameORDXMLName of root XML tag
Row-set nameordersName of XML tag in which data is placed
GenerateXMLXML generates XML file; schema generates XML schema file
ModeElementElement will generate an XML tag for each data mode value; attribute will generate row-tag attributes for each data value
SQL selectSelect * 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
AtoXML utility with parameters listed in the table above

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.

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

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

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=813343
ArticleTitle=Optim Open Data Manager and Optim Connect thin clients for Linux and UNIX environments
publish-date=05102012