Performance Tuning for Optim Open Data Manager (ODM) and Optim Connect

Optim™ Open Data Manager (ODM) allows third-party applications to access Optim archived data by using standard mechanisms, such as open database connectivity, Java™ database connectivity (JDBC), and XML. With Optim ODM, business reporting tools can retrieve Optim archived data as if the data resides in relational databases. Optim ODM is implemented by using Optim Connect Server or Optim Connect thin clients.

The response time of Optim ODM data queries is slower than the response time of comparable queries to a typical production database. However, you can tune Optim ODM data queries to get acceptable response times for archived information. Performance tuning is important for Native Application Access (NAA), where data is joined across database links so that the data appears to be local to the production database server.

Consider the following performance tuning strategies for Optim ODM environments:
  • Archive only information that is used infrequently. If you require active and high-speed access to data, keep the data in the database.
  • When you create archive requests, consider which Optim ODM queries are to be made for each archive file so that you can design the archive request appropriately. For example, if you join two frequently accessed tables, ensure that the tables are part of the same archive file and are designated as reference tables in your archive definition.
  • Tune SQL queries against the database before you tune the queries against archive files with Optim ODM. Because database response times are faster than archive file response times, it is faster to tune a query against the database than it is to tune the query against the archive file.
  • Limit the number of times that you join data in an archive file to data in a database. Optim ODM tables in an archive file can appear to the database as a federated data source, which introduces several layers of operation in getting to the data and degrades performance. If you must join data in an archive file to data in a database, ensure that service level agreements are clear that infrequently accessed data cannot always be accessed quickly.
  • When you work with federated sources and NAA environments, perform WHERE clause processing at the data source wherever possible. Performing WHERE clause processing at the data source minimizes the amount of data that must be transported back to the application. Performing WHERE clause processing at the data source also reduces the number of data type conversions between the source and the federated data.
  • Use SQL Explain Plan to validate the use of the index that Optim ODM uses (Optim ODM cannot use more than one index in a query).
  • When you use range-based SQL queries, ensure that the first segment of the Optim ODM query specifies the column that you are using as the index.
  • When you query or join data across archive files, store the archive files as close as possible to the reporting environment to minimize the impact of I/O and the network on performance.
You can use archive collections to combine data from multiple archive files into a single logical view. However, archive collections can degrade performance if the archive files are too dissimilar. To minimize performance degradation from using archive collections, consider the following performance tuning strategies:
  • Minimize the amount of data that is included in an archive collection where possible. All the archive files in a collection are opened at the same time, and larger collections require more time to return the initial rows for an SQL query.
  • Standardize the column widths and data types that are used in archive files in an archive collection. If different archive files in a collection have different column lengths or different data types for the same data, Optim ODM must reconcile the differences between the archive files, which degrade performance.
  • Where possible, minimize the overlap in the data that is contained in the archive files in an archive collection. When a query or SQL statement is run against an archive file collection, Optim ODM performs a UNION ALL operation on the tables across all the archive files in the collection.
  • Use a balanced index for all archive files in a collection. Indexes on different columns might cause a full table scan to be performed. For example, if table AFX1 uses column CUSTOMERID as an index, and table AFX2 uses column CITY as an index, then ESQL performs a scan instead of a key lookup.


Feedback