Number of indexed entity occurrences and key length

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

Number of index levels

In most situations, you should design indexes with three levels. However, your index may consist of from one to four index levels. Indexes with few entries or a short key can be built with only two levels; indexes with many entries or very long keys 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 SR8 entities

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

SR8-size =

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

Key-length equals the sum of the lengths of all data elements in the index key.

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

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

Number of bytes required

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

Total-#bytes-required = #SR8s * SR8-size

Note: Level-0 refers to the bottom level of the index structure.

Page size for the index area

Plan to store at least three SR8s on a page; use a page reserve of up to 29% of each page. 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) * (SR8-size)

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

+ page-header-footer-length

The header-footer length is 32 bytes for an area. Compare the resulting page size with the table under "Step 2: Determining the page size" 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

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 should be 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 + #SR8s-per-page - 1)

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

#SR8s-per-page

Note that the quotient should be truncated, not rounded.