Record overflow in IDMS
Article ID: 24921
Updated On:
Products
Issue/Introduction
The correct definition of an overflow record is a record occurrence that had to be placed on a page other than its original target page because of a lack of space on the original page. For example, if a record that was targeted to page 1001 gets put on 1002 it is considered an overflow.
Environment
Release: All supported releases.
Cause
Why is it important to know about overflow in an area?
In some instances, overflow can cause performance degradation. The important point is the possibility of issuing another physical I/O to get the next record. Concentrate on determining how many I/Os it takes to walk the average set and compare that to the anticipated number of pages required to access the average cluster size. Depending on the size of the buffer and if a dataspace is being used, this may not be much of a concern, but if the overflow grows significantly, the I/O cost will go up for these records. There may be fewer physical I/Os if a single page is accessed multiple times and the buffer pool was large enough to retain the page between times when the page was accessed.
Resolution
How to identify record overflow?
Use the database analysis utility, IDMSDBAN, to determine the total number of overflows in a database. That information is provided within IDMSDBAN report 5 in the histogram that counts the number of page changes per set occurrence and the histogram that documents the number of record occurrences per set occurrence.
See example below:
SET: DEPT-EMPLOYEE (CONTINUED)
OWNER: DEPARTMENT
MEMBERS: EMPLOYEE
PAGE CHANGE HISTOGRAM
NUMBER OF PAGE NUMBER OF SETS PERCENT OF
CHANGES FOR SET TOTAL SETS
0 2,650 56
2 164 5
3 61 2
4 49 1
5 - 8 148 4
9 - 16 158 5
17 - 32 150 3
33 - 64 122 4
65 - 128 53 1
129 - 256 6 0
257 - 512 0 0
OVER 512 0 0
AVERAGE PAGE CHANGES (ALL SETS): 5
AVERAGE PAGE CHANGES (NON-EMPTY SETS): 18
HISTOGRAM OF PAGES USED TO STORE SET
NUMBER OF PAGES NUMBER OF SETS PERCENT OF
USED FOR SET TOTAL SETS
0 2,650 56
1 0 0
2 164 5
3 - 4 187 5
5 - 8 198 6
9 - 16 180 5
17 - 32 119 3
33 - 64 63 2
65 - 128 0 0
129 - 256 0 0
257 - 512 0 0
OVER 512 0 0
AVERAGE PAGES (ALL SETS): 2
AVERAGE PAGES (NON-EMPTY SETS): 10
SET: CALC (CONTINUED)
AREA: F045PRIM.PAYMENT-AREA
PAGE CHANGE HISTOGRAM
NUMBER OF PAGE NUMBER OF SETS PERCENT OF
CHANGES FOR SET TOTAL SETS
0 2,103 100
2 0 0
3 0 0
4 0 0
5 - 8 0 0
9 - 16 0 0
17 - 32 0 0
33 - 64 0 0
65 - 128 0 0
129 - 256 0 0
257 - 512 0 0
OVER 512 0 0
AVERAGE PAGE CHANGES (ALL SETS): 0
AVERAGE PAGE CHANGES (NON-EMPTY SETS): 0
HISTOGRAM OF PAGES USED TO STORE SET
NUMBER OF PAGES NUMBER OF SETS PERCENT OF
USED FOR SET TOTAL SETS
0 2,103 100
1 0 0
2 0 0
3 - 4 0 0
5 - 8 0 0
9 - 16 0 0
17 - 32 0 0
33 - 64 0 0
65 - 128 0 0
129 - 256 0 0
257 - 512 0 0
OVER 512 0 0
AVERAGE PAGES (ALL SETS): 0
AVERAGE PAGES (NON-EMPTY SETS): 0
In this example, for set DEPT-EMPLOYEE it means that you have 122 set occurrences that have between 33 and 64 page changes. That means that to walk the set there are 63 set occurrences where the page used to hold the set's members changed from a minimum of 33 times to a maximum of 64. This could equate to 33 to 64 physical I/Os but may be less if a single page were encountered multiple times and the buffer pool was large enough to retain the page between times when the page was encountered.
For the CALC set, this report tells us that out of 2,103 set occurrences, 100 percent of them are totally contained in a single page (NO OVERFLOW).
Feedback
