3.6.14 IBM i High Speed Table

Specify whether this table definition and associated indexes should be mirrored into a high speed IBM i User Index to allow more rapid access in "read only" situations.

The following 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 tables open, there is a significant overhead in space and time to open and keep open the 40 tables.

However, if 20 of these tables 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. The high speed table index is an IBM i "platform specific" option. On other platforms the "high speed table" flag may be ignored and the table would be implemented just like any other table.

This may be a design consideration. Do not use the very high speed of IBM i User Indexes to "over-engineer" an application to the point that it will not be able to function on other platforms using normal database management facilities. Details of the IBM i User Index facility and its use can be obtained from the IBM i Information Center. It is possible for existing 3GL based application programs to also access the high speed tables.

Default = NO (unchecked/not selected).

Usage Rules

To be valid as a high speed table a table definition must conform to the following rules. Most of these things are checked during the "make operational" phase of creating/changing a table. If a rule is violated the make operational will fail with appropriate error message(s).

These rules apply to the basic physical table definition and all logic views defined over it:

In summary, the high speed table facility is designed for use with "plain vanilla" lookup and decode style tables only. Tables that are to be used in any other "fancy" way at all should not be implemented as high speed tables.

Tips & Techniques

Some common questions about high speed tables:

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

Broadly speaking, database tables that have the following characteristics are good candidates for high speed table implementations:

Q: Where is the high speed table data kept?

A LANSA table definition flagged as a high speed table is set up just like any other table. The actual table data is stored and maintained in this normal table. 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.

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

Not really. Since each individual table has an associated data real table 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_FILE_INDEX.

However, a synchronized backup of the index and all the associated database tables 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 tables. When this is done and the table involved is a high speed table, then the high speed index will actually be accessed instead of the real table in the following 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 OAM. This ensures that real data table and the mirrored high speed index are updated together.

Q: When is the high speed index updated?

When the OAM for a table 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 table.

When the table is being closed this count is examined, and if greater than 0, all existing entries for the table are erased from the high speed index, then the real table (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" table and the index get out of synchronization?

From the previous points it can be seen that it is possible for a table 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 table but they are not reflected in the high speed index.

Q: How can the lack of synchronization be corrected?

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

  EXEC_OS400 CMD('DLTUSRIDX DC@TBLIDX')

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

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

If you change the layout of a table 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 table. However, if the table data is imported, or the table layout is changed, the associated index is not automatically updated/reformatted. To do this you should trigger a "resynchronization" of the table and its index using any of the techniques previously described.

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 OS/400 release prior to V2R2M0.

Warnings

Platform Considerations