Kay.Rozeboom

Calculating space for user-owned indexes

Discussion created by Kay.Rozeboom on Oct 15, 2013
Latest reply on Dec 3, 2013 by dbadave
At the State of Iowa, we plan to make use of the new MPGI feature in R18.5. We have used mixed page groups for many years, but our largest database has one particular segment (page group) that we have found difficult to break up. MPGI seems to provide the answer.

What I have not seen addressed in any of the documentation is the issue of providing space for the user-owned indexes. A user-owned index is stored in the owner record's area and requires, at a minimum, 48 bytes for every owner that has members. Since anyone considering the use of MPGI is presumably doing so because they are already running out of pages, you can see how this could be a problem.

Below is how we calculated the space required for each new user-owned index:

1) Sweep the owner area. For each owner, count the number of members. Create a table or spreadsheet listing the number of owners that have each number of members. For example, 100 owners have 1 member each, 98 owners have 2 members each, etc. Each number of members is referred to in the formulae below as a "column".

2) Calculate the total bytes for keys for each column:

- Total bytes for keys (A) = (total number of members) X (key length)

3) Calculate the total bytes for SR8 records for each column:

- Number of SR8's per owner (B) = ( (number of members per owner) / (IBC value ) + 1
- Total number of SR8's (C) = B X (number of owners)
- Total bytes for SR8 overhead (D) = C X 44

4) Calculate the total bytes for each column:

- Total bytes for column (E) = A + D

5) Calculate the total bytes for the entire index:

- Total bytes for index (F) = total of E values for all columns

Running the program in step (1) proved to be quite resource-intensive. We found that, as long as not many owners have over 100 members, the counts provided by DB-ANALYZER give similar results with much less effort.

Kay Rozeboom
Information Technology Enterprise
Iowa Department of Administrative Services
Telephone: 515.281.6139 Fax: 515.281.6137
Email: Kay.Rozeboom@iowa.gov

From: Rozeboom, Kay [DAS]
Sent: Tuesday, August 20, 2013 7:42 AM
To: Van Der Heyden, Scott [DHS]
Subject: RE: dist_cnt.xlsx

For each column:

Total bytes for keys (A) = (total number of members) X 4
Number of SR8's per owner (B) = ( (number of members per owner) / 73 ) + 1
Total number of SR8's (B2) = B X (number of owners)
Total bytes for SR8 overhead (C) = B2 X 44
Total bytes for column (D) = A + C
Total bytes for index (E) = total of D values for all columns
Total cylinders (F) = E / 668,325

Kay Rozeboom
Information Technology Enterprise
Iowa Department of Administrative Services
Telephone: 515.281.6139 Fax: 515.281.6137
Email: Kay.Rozeboom@iowa.gov<mailto:Kay.Rozeboom@iowa.gov>

From: VanDerHeyden, Scott [mailto:svander@dhs.state.ia.us]
Sent: Monday, August 19, 2013 2:41 PM
To: Rozeboom, Kay [DAS]
Subject: dist_cnt.xlsx

Outcomes