DISPLACEMENT

Index Displacement

The following steps are used to calculate page-count for the DISPLACEMENT parameter of the CREATE INDEX statement:

Calculate number of bottom-level and higher-level SR8s:

Set N             = #-of-rows
High-level-SR8s   = 0
Bottom-level-SR8s = 1

Repeat

  N = (N + IBC-key-count - 1) / IBC-key-count (truncate)

  If N = 1, exit

  If High-level-SR8s = 0,
        High-level-SR8s   = 1
        Bottom-level-SR8s = N
  Else  High-level-SR8s = High-level-SR8s + N

Set Total-SR8s = High-level-SR8s + Bottom-level-SR8s

Determine the number of SR8s per page:

Calculate size of an SR8:

SR8-size = 32 + (IBC-key-count + 2) * (Key-length + 8)

Calculate number of SR8s per page:

(Page-size - Page-reserve - 32) / (SR8-size + 8)

Establish the INDEX DISPLACEMENT:
- If the number of higher-level SR8's is less than 2, set the DISPLACEMENT = High-level-SR8s. (For a one- or two-level index, displacement is 0 or 1 respectively).
- If the number of higher-level SR8s is greater than 1, compute the displacement page count:
```
(High-level-SR8s + SR8s-per-page - 1)
-------------------------------------  +  1 (truncate)
            SR8s-per-page
```
  If the calculated displacement is greater than the number of pages in the area containing the index, then:
```
Displacement-page-count = Number-of-pages-in-area / 2
```

Table Displacement

Table displacement is the number of pages that the rows of a table are displaced from the index around which they are clustered. The table displacement is 0 if the table and index reside in different areas. It is calculated as described below if they reside in the same area:

If the total number of SR8s is less than 2, set the table's displacement to 0.
If the total number of SR8s is greater than 1, the table's displacement is:
```
(Total-SR8s + SR8s-per-page - 1)
-------------------------------------  +  1 (truncate)
         SR8s-per-page
```
If the calculated displacement is greater than the number of pages in the area containing the index, then:
```
Table-displacement = Number-of-pages-in-area * 3 / 4
```