Record overflow

Document ID : KB000024921
Last Modified Date : 14/02/2018
Show Technical Document Details

Issue:

What is record overflow? 


The correct definition of an overflow record is a record 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.

 

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. You should 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. 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.

 

Resolution:

How to identify record overflow? 


You can 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).