Connecting to your Optim Archive Files
31 December 2014
As I noted in a previous article, it is great to use Optim to archive old data out of your production databases to make them perform better.† However, the reason many organizations donít purge old data is because it might be needed.† Optim archiving solution shines in this area because it allows you to connect any ODBC or JDBC application or report writer to the archived data.† Since the archives include metadata like table names, column names and data types it becomes quite straight forward to display the data with the right headings and in context.† This ability to query the archive is the difference between deleted data in some file and true archiving.† In this article I will describe the main components involved in allowing this access to archives.
The main component that allows you to access the archive files created by Optim Archiving is Optim Connect.† Once it is configured it listens on a port for JDBC and OCBC connections and processes SQL to return query results like databases including DB2 or Oracle do.† Additionally the install of Optim adds the JDBC and ODBC drivers to a directory that can be copied to any client that needs to connect.† Those drivers can be used by your favorite report writer like Cognos or Business Objects, a spread sheet like Microsoft Excel or even your own custom application.† To see where Optim Connect fits into an example architecture, please see Figure 1 in my Archive Old Data from Host Systems article.
Optim Connect, formerly called Optim Data Manager, is a process that runs on the Optim server that provides access to the archived data.† Optim Connect is very similar to a database managements system (DBMS) in that it runs continuously, listens on a port for connections and process SQL queries through those connections.† One useful aspect of Optim Connect is that you can create a collection of all of the archive files to look like one big database to the clients, or files can be grouped into different sets with each collection looking like different database.† Optim Connect Studio is the user interface to configure Optim Connect.† Figure 1 shows an example where I have created multiple different data sources (databases) that can be used by the clients.† As you can also see in figure 1, sources include iDemo and DB2Archive that will appear to the client as different databases.†
To view instructions for configuring and using Optim Connect, please see the Optim Connect User Guide and Reference. It can be found in the install media after decompressing, prior to installation and it is a good idea to read it prior to installing.† On Windows it can also be found at C:\IBM\InfoSphere\Optim\rt\ODM\doc\OptimConnect_533_User_Guide_and_Reference.pdf after you install Optim.† It will be in a similar directory on other operating systems.† This manual also contains information on installing and configuring the ODBC and JDBC clients.† ††Other installation manuals exist in the C:\IBM\InfoSphere\Optim\rt\ODM\doc\Install Manuals directory as well.
The Optim Open Data Manager and Optim Connect thin clients for Linux and UNIX environments article on developer works contains an excellent overview of the client architecture and gives detailed instructions for configuring your ODBC client on any operating system.† It also gives some good information on the JDBC client as well.†
The ODM section of the Optim Knowledge Center provides additional information as well.†
First you will need to copy the ODBC driver from the Optim Server to your client machine.† In the directory where Optim was installed you will find the ODBC and JDBC driver directories.† You can copy them from the server to your client machine.† They are in the directory: C:\IBM\InfoSphere\Optim\Installer\Optim Connect\Thin Clients\ODBC Thin Clients.† Instructions for the install of the driver is in a PDF under this directory.† You will see two other subdirectories there as well.
ODBC 2.5 and ODBC 3.5 drivers are included.† Some applications will only work with one of these types of ODBC drivers and others will work with both.† You will need to review the documentation of your application to verify this.† Within each of these ODBC directories is a list of subdirectories corresponding to different operating systems like Windows, AIX or LinuxRH.† Under each directory will be two compressed install files -- one for 32-bit and one for 64-bit.† Copy the file you need to your client machine and install.† For example if you want the 64-bit ODBC 3.5 driver then use OptimConnect-53318-ODBC_3_5-windows.exe.† Generally 32-bit applications will need 32-bit drivers and 64-bit applications will need 64-bit drivers.†
I tried both of these drivers on my Windows 7 64-bit laptop.† I had to uninstall the 32-bit driver to install the 64-bit driver.† I was able to connect Excel to my archive using both.† I made an interesting observations with these drivers.† When creating an ODBC system data source after installing the 32-bit driver I tried doing using the ODBC Data Source Administrator (Data Sources (ODBC)) from the Windows Control Panel, I could not create it because I could not see the driver.† My colleague and Optim Expert, Jody Hair, told me how to resolve this.† I needed to use the 32-bit ODBC Data source Administrator that you get by executing:
Since many organizations use Windows clients, I will provide some details here about configuring the ODBC driver on Windows clients.† There is a lot of information on the web about using the ODBC Data Source Administrator, so I will not cover that here.† However, I will provide a few bits of information important to Optim Connect.† After you have installed the driver and started the ODBC Data Source Administrator, you can create either a User DSN or System DSN.† Go to the tab you want and click add to add the driver.† Select the Optim Connect for ODBC X.X from the list as shown in Figure 2.
It you entered the connect information correctly and connected to Optim Connect, then you will see all of the data sources defined in the Optim Connect Studio from the ďDefault datasourceĒ drop down as shown in Figure 3.† In this example I picked iDemo which you can also see in Figure 1 where it was defined in Optim Connect Studio.†
I then used Microsoft Excel to query my archive using the System Data Source Name (DSN) I created.† You donít actually need to set up your ODBC data source to allow Excel to connect to Optim Connect data since it can just use the drivers directly that you installed.† On my install, the DSN I created in the 64-bit driver didnít show up as a source, but I was able to use the driver directly.† The 32-bit System DSN I created did show up though.† If you want to see your archived data using Excel, but are unfamiliar with configuring a data source please see the Use Microsoft Query to retrieve external data web page.†
You can also connect to your application or report writer to the archives using a JDBC connection.† To do that you would download the zip file that contains the driver files.† It has 3 JAR files and 1 class file.† They are:
On Windows you can find the zip file here:† C:\IBM\InfoSphere\Optim\installer\Optim Connect\Thin Clients\Java Thin Clients\OptiimConnect_JDBC_220.127.116.11.zip.†† The directory structure will be similar for other operating systems.†
Once you have downloaded the zip file, you would apply the files to your application or report writer as you would with any other JDBC drivers.† More information about how to connect using this client is described in the documentation section above.† Also here is a simple example of the URL you could use to make the connection to the iDemo data source defined in figure 1 above.†
Once you have configured Optim, Optim Connect, clients and have done at least one archive, you can connect to your archive as you would for any other databases. Please add any thoughts that that you on this topic to the Facebook Page or the db2Dean and Friends Community and help the extended community.