Page History
...
Specify whether this table definition and associated indexes should be mirrored into a high-speed IBM i IBM i User Index to allow more rapid access in "read only" situations.
The following points provide basic information about the IBM i IBM i high-speed table facility. They should all be read and understood before this facility is used in any way:
...
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:
...
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.
...
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.
...
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:
...
- Doing a "dummy" update to the table. The associated OAM will then rebuild the index to reflect the updated table thus synchronizing the table and index again.
- Use the built-in function REBUILD_FILE_INDEX to manually trigger the OAM to rebuild the index of one or more tables. In fact, this sequence of commands will physically delete the entire IBM i user index area and then rebuild the indices of all high-speed tables within the current partition. The first table rebuild will recreate the IBM i user index if it does not currently exist.:
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?
...
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 |
|---|
| 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
- It is strongly recommended that, if option *HSTABEXTEND is added to system data area DC@OSVEROP, to make either the extended entry record length available or remove it to limit entry length. All tables tagged as high-speed tables, all read-only functions that use these tables and all other OAMs and DBOPTIMIZED functions that use high-speed tables for lookup validation rules, must be recompiled AFTER deleting the current user index. This index is DC@TBLIDX if adding *HSTABEXTEND, or DC@TBLIDY if removing *HSTABEXTEND.
- If this is not done, all functions that use a particular table and the OAM must be recompiled at the same time or they will not be pointing to the same index. The situation will be further complicated by OAMs and DBOPTIMIZED functions which use high-speed table tables for lookup validation rules also pointing to the wrong index. It may not be obvious to the user that there is a problem as the database table and one index will be unsynchronized, but it will not cause program failure.
...