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:

• No form of alternate collating sequence is supported. The IBM i User Index facility only supports simple binary collation. From "SC21-8226", the manual that elaborates upon access to an IBM i User (or Independent) Index: "Each entry is inserted into the index at the appropriate location based on the binary value of the argument. No other collating sequence is supported."
• All key columns must be ascending, unsigned values.
• When a table with date, time or timestamp columns in its key list is mirrored in a high-speed table, a LANSA function with read-only access to the table will not use the OAM. The date, time or timestamp column is treated as an alphanumeric column in the high-speed table. Therefore values must be entered in full (for example, as 1999-01-02 not as 1999-1-2) when fetching a record. Also, if an invalid value is entered, the LANSA function will not check if it is a valid date, time or timestamp, but just return a not-found status.
• The table can have no more than 99 columns.
• The maximum table entry record length depends on the system data area DC@OSVEROP. If option *HSTABEXTEND has been inserted, the maximum entry record length is 1988 bytes (this is an OS400 limit) and a maximum key length of 108 bytes (this is a LANSA limit for storage and performance reasons). The key is included in the 1988 record length. If option *HSTABEXTEND is not in the system data area, the table entry record length cannot exceed 108 bytes. WARNING entry record lengths greater than 108 bytes cannot be saved to or restored from an OS400 release prior to V2R2M0. Note for decimal columns their decimal length is counted, rather than their byte length. For more information refer to Allow extended tables to be added to HST in Compile and Edit Settings.
• The base physical table must have one or more primary key columns.
• The concepts of table members, run time library list changes and any form of table override or rename are NOT supported in any way in the high-speed table execution environment. There is one high-speed table index per LANSA partition. When an application is invoked that needs to access the index, it uses the single index associated with the current partition.
• No select omit logic can be specified.
• No batch control logic can be specified.
• No form of open, read or close trigger can be specified for any column in the table, either at the column or table level.
• No virtual columns or logic (code) can be defined.
• No read security will be actioned for the table. This means that a function cannot be stopped from reading the content of the high-speed table. However, they can be stopped from modifying it in the normal manner (because they are actually modifying the normal database table, not the high-speed index). This restriction exists to ensure maximum performance in read-only applications. Applying read security would severely impact the performance of tables where only a few accesses are made. In fact the security checking time would be far longer than the actual time taken to access many table entries.
• Functions that modify (INSERT, UPDATE or DELETE) tables that are tagged as high-speed tables cannot use *DBOPTIMISE, *DBOPTIMISE_BATCH or any other option that infers these options. This restriction exists because the special logic required to "mirror" the real table data into the high-speed index only exists in the associated OAM. Thus all "table modifiers" must be forced to use the appropriate OAM.
• Functions that only read from a high-speed table may use *DBOPTIMISE or *DBOPTIMISE_BATCH in the normal manner.
• When the definition (i.e.: layout) of a high-speed table is changed all functions and OAM validation rules that read from the high-speed table rather than the real table need to be recompiled. Again, this restriction exists to provide maximum performance. By definition tables are largely static in design and content, so this should not be a problem. If it proves to be, remove the high-speed table option from the definition of the table.
• No form of locking is supported in applications that only read from high-speed tables. If you need record locking in a "read only" function, then your table is not a good candidate for the high-speed table facility.
• The use of any of the following facilities with high-speed tables is not checked, but they are not supported in any form within functions that require "read only" access to high-speed tables:
• The use of the OPEN command with the *OPNQRYF option.
• The use of the *BLOCKnnn option in any form.
• The use of SELECT_SQL in any form.
• The use of WITH_RRN, RETURN_RRN or any form of relative record addressing.
• The ISS_MSG parameter in any form.

...

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.

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.

...