Fragments and Relocated Records
Article ID: 50784
Updated On:
Products
Issue/Introduction
One of the most rigidly enforced rules within an IDMS database is that the dbkey of a record is assigned when a record is stored within the database and cannot change as long as that record resides on the database. A dbkey is composed of the number of the page on which a record is stored and a line index number that points the DBMS to information necessary to locate the record occurrence on the database page. This means that when the size of a record occurrence already existing on the database increases IDMS cannot simply move that record to another page if insufficient space exists on the current page since that movement would require a new dbkey to be assigned.
This article describes variable length fragments and relocated records which are structures used by IDMS to acquire the necessary space for these expanding record occurrences without changing a record's original dbkey. Performance implications of these constructs, ways to minimize their creation, and how IDMS attempts to eliminate their presence are also covered.
Environment
Release: Any supported release.
Resolution
Fragments
The type of record whose size is most likely to increase after it is stored on a database is one defined as variable length. Records are considered to be variable length when their element definitions include an OCCURS DEPENDING ON clause or the record occurrences are to be compressed. A variable length record (VLR) occurrence will increase in size when as a result of a MODIFY verb an additional reoccurring group is added to the record or changes to the record's data result in the compression algorithm providing a less effective level of compression.
When a record is defined as being variable length a Record Descriptor Word (RDW) is added to the data portion of the record and an additional pointer position is inserted at the end of the record's prefix. The RDW is used to contain the length of the entire data portion of the record occurrence and the additional pointer position is used to support an internal set known as the fragment chain.
When an existing VLR is to be expanded, and in some cases initially stored, and there is insufficient space to hold the entire record on the occurrence's target page the portion of the record that will not fit will be overflowed to another page within the area. The portion of the record that is on the original page is referred to as the record's 'root' while the overflowed piece of the record is referred to as a fragment and is assigned a record id of 4 (SR4). The root continues to be identified using the record's true record id and acts as the owner of that record's fragment chain with the SR4's being the fragment chain's members.
VLR Fragment Chain
Figure 1
When IDMS must access the fragmented VLR it will first read the page containing the root of the record as this is the portion of the record referenced by the record's dbkey. IDMS then recognizes that the entire record is not completely contained within the root portion of the record and will commence walking the fragment chain to reconstruct the record within storage. This reconstruction is transparent to the application but the need to read another page to get the record's fragment has a negative impact on the performance of the DBMS. It is possible that the fragment chain may link to multiple fragments, each on a different page. Each page containing a fragment for a VLR record occurrence can be considered to add 1 physical I/O to the amount of work performed by the DBMS to reconstruct the VLR.
Whenever a VLR is defined to a database it is very unlikely that fragmentation of some record occurrences can be completely avoided if the associated applications have the ability to increase the size of these records. However IDMS does provide some mechanisms that will allow a DBA to minimize the number of fragments that may appear within a database. The first thing to do is to insure the proper specification of the MINIMUM ROOT and MINIMUM FRAGMENT clauses on the VLR's RECORD statement within the schema.
MINIMUM ROOT IS RECORD LENGTH should be coded on the schema RECORD statement for all variable length records. This parameter tells the DBMS to insure that the page selected as the target page for a record occurrence has enough space to contain the entire record. Specifying any value other than RECORD LENGTH can result in SR4 fragments being created during the store of the VLR occurrence. In the same manner MINIMUM FRAGMENT IS RECORD LENGTH should always be coded. By coding this you are telling IDMS that whenever it must fragment a VLR during a MODIFY command it must find enough space on an overflow page for the entire fragment. Specification of any other value can result in multiple fragments being created if the database area is tight on space.
Finally, any area that contains a VLR should be defined to have a PAGE RESERVE on its AREA statement within the SEGMENT definition. By specifying a PAGE RESERVE the DBA is directing IDMS to stop adding new records to any page when the amount of free space remaining on that page reaches the specified value. That remaining space can then be used for the expansion of any VLR occurrence on that page which will prevent fragments from being created on a MODIFY command until that reserved space is exhausted.
Acknowledging the fact that VLR fragmentation can have a negative impact on a database's performance, IDMS does make an attempt to condense fragment chains when processing an area in an UPDATE mode. When a fragmented VLR is accessed and the area is in an UPDATE mode IDMS will check the free space on the root's page to see if any space has been made available. If space does exist IDMS will condense as much of the fragment chain as possible back to the original target page and will also condense any other fragments together if space allows on the pages on which fragments exist. Of course the best way to eliminate all fragments within an area is to perform an UNLOAD/RELOAD, DB-REORG, or REORG against that area with a subschema generated from a schema that had MINIMUM ROOT IS RECORD LENGTH specified for the record type.
Relocated Records
Relocated records are created by IDMS during the in-place expansion of a fixed length record or the control length of the root portion of a variable length record. This structure is necessary when such an expansion causes the record (or its control length) to no longer fit on its current page. When this situation is encountered the record occurrence is removed from its current page and replaced with an SR2 record. A new target database page is found for the data record using standard overflow algorithms but the data record is stored on the new page as a type 3 record (SR3) instead of using its original record type designation. The SR2 occurrence contains the relocated record's original record type and the dbkey of the new SR3 record.
When IDMS is directed to access a record at a specified dbkey one of the first things it does is verify the record id of the record on the database page to the type of record requested. If the record ids do not match, IDMS will check the record type on the database page to see if it is a 2 before returning an error status to the application program. If the record is an SR2, IDMS will check the record type indicator within the SR2 to see if it represents the type of record being requested. When the record id in the SR2 matches the requested type the dbkey of the associated SR3 in the SR2 is used to read the SR3 record occurrence. The data record in the SR3 is then returned to the application using its original record type designation.
The only time that SR2/SR3 records may be created for non-SQL defined databases is as a result of the execution of the RESTRUCTURE SEGMENT utility when the record prefix or data length of a record is being increased.
Expansion of records (rows) in an SQL-defined database occurs at various times but is always the result of the execution of a DDL statement. If the prefix portion of a record needs to be expanded due to the addition of a referential constraint an in-flight restructure is performed against the database during the execution of the SQL command which changes the SQL schema. If the expansion of a record will not fit during the in-flight restructure the necessary relocations occur and the required SR2/SR3 pairs created. If the definition of a table is changed by the addition of new columns no immediate restructure of the data is performed during the modification of the table's definition. However, as these database records are accessed in an update mode the DBMS will append the needed space to any occurrences that existed on the database prior to the change in the table's definition. If this expansion will not fit on the record occurrence's current page relocation occurs and the necessary SR2/SR3 records are created. This means that SR2/SR3 pairs may be created during normal application processing.
In the following example a record type 1024 was expanded and could not fit in the space available on its original page (1001). The record has been relocated to available space on page 1002 and an SR2/SR3 structure created to support the relocated record occurrence.
Relocated Record
Figure 2
Regardless of the type of IDMS database being used SR2/SR3 records will be removed from the database when a relocated record is accessed and its area is readied in an update mode and there is sufficient space on its original database page to hold the expanded record. In this case the SR2 will be removed from the original page and the record represented by the SR3 will be moved back to that page and re-assigned its original record id. The SR3 is then deleted from its overflow page.
Summary
Variable length fragments or relocated records provide transparency to application programs when the DBMS engine must locate sufficient space for a record by allowing the target record to retain its original dbkey. However this is accomplished through the cost of additional CPU and I/O operations and excessive numbers of these constructs can have a negative impact on an application's performance. DBAs should constantly monitor their database for an increase in these types of system records using the PRINT SPACE or IDMSDBAN utilities and be prepared to reduce their number of occurrences using the UNLOAD/RELOAD, DB-REORG, or REORG utilities.
Additional Information
Feedback
