Number of index entity occurrences

The requirements of your database will determine this value. You may want to use an inflated number to allow space for future growth.

Number of index levels

In most situations, you should design indexes with three levels. However, your index could consist of from one to four index levels. Indexes with few entries can be built with only two levels; indexes with many entries might require four levels. Indexed relationships or indexes with extremely few entries might require only one level.

Number of entries per SR8

For an n-level index:

#SR8-entries =

nth-root-of-#indexed-entity-occurrences

For example, to build a 3-level index:

#SR8-entries =

cube-root-of-#indexed-entity-occurrences

The results of this calculation should be rounded up to the next higher integer.

Size of SR8s

Determine SR8 size (including line index space) by using the following formulas:

Level-0-SR8-size =

40 + (#SR8-entries + 1) * 4

Non-level-0-SR8-size =

40 + (#SR8-entries + 1) * 8

Round the value up to the next higher number divisible by 4. The level-0 SR8 length is nearly half that of the non-level-0 SR8. This means that a page for an index sorted on db-key can hold nearly twice as many bottom-level SR8s as higher-level SR8s.

Number of SR8s

Determine the number of SR8s required for your index by level:

#Level-0-SR8s =

(#indexed-entity-occurrences + #SR8-entries - 1)

----------------------------------------------

#SR8-entries

#Level-1-SR8s =

(#level-0-SR8s + #SR8-entries - 1)

---------------------------------

#SR8-entries

#Level-2-SR8s =

(#level-1-SR8s + #SR8-entries - 1)

---------------------------------

#SR8-entries

The quotient should be truncated, not rounded. Continue calculating the number of SR8s at each level until the quotient equals 1. One of the above calculations will be required for each level in your index. The total number of SR8s required for your index is equal to the sum of all counts computed above.

Number of bytes required

Calculate the total number of bytes of space you will need to accommodate the index:

#Bytes-required-for-level-0-SR8s =

#level-0-SR8s * Level-0-SR8-size

#Bytes-required-for-non-level-0-SR8s =

#non-level-0-SR8s * non-level-0-SR8-size

Total-#bytes-required =

level-0-bytes + non-level-0-bytes

Page size for the index area

Plan to store at least three SR8s on a page; use a page reserve of up to 29 percent of the page size. The page reserve factor actually increases the size of your database page so that additional SR8s can be accommodated without generating overflow. Use the following formulas to estimate page size:

Page size =

(#SR8s-per-page) * (non-level-0-SR8-size)

Total-page-size = page-size + page-reserve

+ page-header-footer-length

The header-footer length is 32 bytes for a standard area. Compare the resulting page size with the result from the previous table and select the next larger page size that's compatible with your DASD device:

• If the page size determined in this way is too large, the number of index levels will have to be increased until a satisfactory compromise between page size and number of index levels is reached.
• If the page size determined is much smaller than 4K, use a 4K page size instead; this allows more than three SR8s to be stored on each page.

Number of SR8 displacement pages needed

For improved efficiency, sorted indexes should make use of SR8 displacement pages to displace bottom-level (level-0) SR8s from the top-level and intermediate-level SR8s. To determine the number of displacement pages needed, perform these calculations:

#Non-displaced-SR8s =

total-#SR8s - #level-0-SR8s

#SR8-displacement-pages =

(#non-displaced-SR8s + #SR8s-per-page - 1)

------------------------------------------ + 1

#SR8s-per-page

Note that the quotient is truncated, not rounded.

Number of pages needed for the index

After calculating the displacement pages, determine the total number of pages needed for the index:

Total-#Pages-needed =

#SR8-displacement-pages +

(#level-0-SR8s + #level-0-SR8s-per-page - 1)

--------------------------------------------

#level-0-SR8s-per-page

Note that the quotient is truncated, not rounded.