You are here: LANSA for i User Guide > 3. Files > 3.17 Database File Attributes > More About High Speed Tables
More About High Speed Tables

These points provide basic information about the IBM i high speed table facility. They should all be read and understood before this facility is used in any way.

     In a traditional commercial application that had, say, 40 database files open, there is a significant overhead in space and time to open and keep open the 40 files.

     However, if 20 of these files were implemented as high speed tables, then the space and time overheads would be reduced by a factor of approximately 50%. This would probably significantly enhance the performance of this application.

To be valid as a high speed table a file definition must conform to the following rules. Most of these things are checked during the "make operational" phase of creating/changing a file. If a rule is violated the make operational will fail with appropriate error message(s). These rules apply to the basic physical file definition and all logical views defined over it:

Some common questions asked about high speed tables

Q: What type of files are candidates to be high speed tables?

Broadly speaking, database files that have these characteristics are good candidates for high speed table implementations:

Q: Where is the high speed table data kept?

A LANSA file definition flagged as a high speed table is set up just like any other file. The actual file data is stored and maintained in this normal file. However, the data is also mirrored into a "read only" high speed index to allow very fast access from "read only" applications.

The high speed index is actually an IBM i User Index (object type *USRIDX). It is automatically created in, and must always remain in, the module (or program) library of the current partition. You do not have to create this index, but you may choose to periodically delete and rebuild it. See the following points for an example of this. It is named DC@TBLIDX or if option *HSTABEXTEND is in the system data area DC@OSVEROP it is named DC@TBLIDY.

Q: Do I need to backup the high speed index?

Not really. Since each individual table has an associated data real file containing the "real" data, then you can actually re-create the high speed index for all tables in just a few minutes by using the Built-In Function REBUILD_TABLE_INDEX.

However, a synchronized backup of the index and all the associated database files containing the "real" data may simplify and speed up your restore procedures, should they need to be invoked.

Q: When is the high speed index accessed?

At various points the LANSA code translators may generate code to access database files. When this is done and the file involved is a high speed table, then the high speed index will actually be accessed instead of the real file in these situations:

Q: Can I use *DBOPTIMISE/*DBOPTIMIZE with high speed tables?

Yes you can in all situations except where the function updates a high speed table. Functions that update a high speed table must do all their I/O to the table via the associated I/O module. This ensures that real data file and the mirrored high speed index are updated together.

Q: When is the high speed index updated?

When the I/O module for a file that is flagged as a high speed table is created extra code is added to it to count the number of inserts, updates and deletes performed to the file.

When the file is being closed this count is examined, and if greater than 0, all existing entries for the file are erased from the high speed index, then the real file (and its views) are read from end to end to insert new entries into the high speed index.

This architecture has some impacts on the use of high speed tables:

Q: Can the "real" file and the index get out of synchronization?

From the preceding points it can be seen that it is possible for a file and its mirrored high speed index to get out of synchronization. For example, a function may insert 3 new entries to a table and then fail. At this point the new entries are in the real file but they are not reflected in the high speed index.

Q: How can the lack of synchronization be corrected?

If a file and its high speed index get out of synchronization then they may be desynchronized by:

The sequence of commands:

EXEC_OS400 CMD('DLTUSRIDX DC@TBLIDX')

or with option:

*HSTABEXTEND EXEC_OS400 CMD('DLTUSRIDX DC@TBLIDY')

USE BUILTIN(REBUILD_TABLE_INDEX) WITH_ARGS('''*ALL''')

will physically delete the entire IBM i user index area and then rebuilt the indices of all high speed table within the current partition. The first file rebuild will recreate the IBM i user index if it does not currently exist.

Q: What happens when I change the layout of a file?

If you change the layout of a file and then "make the change operational" a resynchronization of the table and index will be automatically performed. This automatic synchronization is not performed if you then export the changed definition to another system.

Q: What happens if I import a high speed table to another system?

A high speed table is imported to another system just like a normal file. However, if the file data is imported, or the file layout is changed, the associated index is not automatically updated/reformatted. To do this you should trigger a "resynchronization" of the file and its index using any of the techniques described in this guide.

Note: A user index greater than 1 gigabyte or with an entry record length greater than 108 bytes cannot be saved to or restored from an OS400 release prior to V2R2M0.