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.

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

• A LANSA file definition flagged as high speed table is actually implemented as a normal database file. All functions which insert, update or delete data in the table actually access the normal database file.
  
  This normal database file actually contains the data, so there is no difference in the risk of data loss between a normal file 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 file again without any loss of definition or data.
  
  The difference between a normal file and a high speed table is that a high speed table uses an extra object (over and above the normal database file). This object is called an IBM i "User Index" and it contains a duplication or "mirror" of the data in the associated database file and its logical views.

• Functions that only read the file 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 file open.

     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.

• 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 file would be implemented just like any other file.
  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.

• For details of the IBM i User Index facility and its use, refer to IBM documentation.

• It is possible for existing 3GL based application programs to also access the high speed tables. Contact your product vendor for more information about this feature.

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:

• No form of alternate collating sequence is supported. The IBM i User Index facility only supports simple binary collation. From 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 fields must be ascending, unsigned values.

• When a file with date, time or timestamp fields in its key list is mirrored in a high speed table, a LANSA function with read-only access to the file will not use the I/O module. The date, time or timestamp field is treated as an alphanumeric field 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 799 fields.

• 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 IBM i 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 IBM i release prior to V2R2M0. Note that, for packed fields, their decimal length is counted, rather than their byte length. Refer to the Compile and Edit Settings in Review System Settings for information about setting these option.

• The base physical file must have one or more primary key fields.

• The concepts of file members, run time library list changes and any form of file 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 field in the file, either at the dictionary or file level.

• No virtual fields 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 file, not the high speed index).
  
  This restriction exists to ensure maximum performance in read only applications. Applying read security as above 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) files 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 file data into the high speed index only exists in the associated I/O module. Thus all "table modifiers" must be forced to use the appropriate I/O module.

• 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 I/O module validation rules that read from high speed table rather than the real file 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 file.

• 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 file is not a good candidate for the high speed table facility.

• The use of any of these 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 simple lookup and decode style files only. Files that are to be used in any other "fancy" way at all should not be implemented as high speed tables.
  Warning:It is strongly recommended that if option *HSTABEXTEND is added to system data area DC@OSVEROP to make the extended entry record length available, or is removed to limit entry length, that all files tagged as high speed tables, all read only functions that use these files, and all other I/O modules and DBoptimized functions that use high speed tables for lookup validation rules be recompiled AFTER deleting the current user index which is DC@TBLIDX if adding *HSTABEXTEND, DC@TBLIDY if removing *HSTABEXTEND.
  If this is not done all functions that use a particular file and the I/O module must be recompiled at the same time or they will not be pointing to the same index. The situation will be further complicated by I/O modules and DBoptimized functions which use high speed table files 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 file and one index will be unsynchronized but this will not cause program errors.

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:

• 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 files that are not subject to continual and random change on a daily basis. A "product" file 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 file (say, for example, 5000 or less).

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

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

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:

• In RDML functions that only "read" from the file 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 file.

• In interfile validation checks. I/O modules or *DBOPTIMISE generated code that needs to lookup a file entry as the result of a validation check will always look in the high speed index rather than the real file.

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:

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

• The file 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 file that has a high speed index mirror simultaneously. This problem is easily overcome by ensuring that applications that 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 require assistance in designing such an application.

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:

• Doing a "dummy" update to the file. The associated I/O module will then rebuild the index to reflect the updated file thus synchronizing the file and index again.

• Use the Built-In Function REBUILD_TABLE_INDEX to manually trigger the I/O module to rebuild the index of one or more files.

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.