3.7.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:

• A high speed table is not a "thing" in itself. A high speed table is a normal LANSA table definition that has its "high speed" flag set to YES.

• A LANSA table definition flagged as high speed table is actually implemented as a normal database table. All functions which insert, update or delete data in the table actually access the normal database table.
  This normal database table actually contains the data, so there is no difference in the risk of data loss between a normal table definition and a high speed table.
  This also means that you can, at any time, set the high speed table flag back to NO and revert to a normal database table again without any loss of definition or data.
  The difference between a normal table and a high speed table is that a high speed table uses an extra object (over and above the normal database table). This object is called an IBM i "User Index" and it contains a duplication or "mirror" of the data in the associated database table and its indexes.
  Functions that only read the table actually access the "mirror" data in the user index. Such a method of access has some strong advantages:

• It is very fast.
• There is no open or close overhead.
• There is very little of the normal space (PAG) overhead associated with having a normal database table open.

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:

• 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.

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:

• The data content is widely used for decode (e.g.: state code 'CA' is printed as description "CALIFORNIA") and validation (e.g.: is state code 'CA' valid?).

• The data content is relatively stable. (e.g.: How often is a new state acquired). Generally this means tables that are not subject to continual and random change on a daily basis. A "product" table would be a good candidate if it only contained descriptive details because products are not created/changed often. However if it contained stock levels it would not be a good candidate because stock levels are continually changed.

• There are usually a small number of records in the table (say, for example, 5000 or less).

• There is usually only one application that "maintains" the table, and it is not used often (say, once per day or less often).

• The vast bulk of applications only "read" from the table for decode and validation purposes.

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 :

• In RDML functions that only "read" from the table via CHECK_FOR, FILECHECK, FETCH or SELECT commands. When an RDML function is compiled it is checked for direct access to a high speed table. If all accesses to all the high speed tables used in the function are "read only" then the I/O will be directed to the high speed table rather than to the real database table.

• In inter table validation checks. OAMs or *DBOPTIMISE generated code that needs to lookup a table entry as the result of a validation check will always look in the high speed index rather than the real table.

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:

• The table and the mirror index are not actually maintained simultaneously. When the table is being closed the existing mirrored index entries are erased and then recreated from the updated version of the table.

• The table and all its views are maintained as separate high speed index data. This means that a table with 4 views actually uses 5 times the index space of the source table. One for the table and one for each of the views.

• Contention may occur if multiple users attempt to update a table that has a high speed index mirror simultaneously. This problem is easily overcome by ensuring that applications which update high speed tables are restricted to single user access.
  There are a variety of simple methods that may be used to restrict a function to single user access. Contact your product vendor if you require assistance in designing such an application.

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:

• 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?

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

• 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.

Platform Considerations

• IBM i: This table attribute applies to IBM i databases only.