IBM i General Design Standards › Design Standards for Database Files › Considerations for Database File Design › Categories of Database File › Access Paths

Access Paths

The following apply when defining access paths:

• Break up fields to the smallest component that will be required when creating keys to access the data. Data fields may be required as components in several ways:
  • for select and omit usage on access paths
  • for key specification
  • for program usage (though fields may be redefined in programs through the use of data-structures)

For example, if you have a stock code field made up of three parts, prefix/stem/suffix (ZXXXYYYY), and you know that you will require the enquiries of all items with a given prefix, or suffix range, define the field as three parts.

• Do not add unnecessary key fields to the logical view, as the number of key fields determines the size of the logical view.
• Numeric sub-fields that are to be concatenated back into a single key field (for example, possibly YY, MM, DD), should be defined as zoned.
• Dates should always be in YYMMDD order, so as to give easy historical access.

Note: An alternate collating sequence or a field level translation table is needed to put lower case alpha characters into true alphabetical order. IBM supplies a table to make the translation: QSYSTRNTBL.