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.