SQL/MP Installation and Management Guide

Understanding and Planning Database Tables
HP NonStop SQL/MP Installation and Management Guide—523353-004
3-12
Relative File Structure
characteristic represents a factor that could influence both space and time
performance of a relative file.
A user performing update operations on a table stored in a relative file can update or
delete rows and can lengthen or shorten values in varying-length columns up to the
actual defined record length.
Positioning in Relative Files
You can specify the exact position where a new row (record) is to be inserted into a
relative file by using the system-key parameter in the INSERT statement. The
system software accesses this position through a record number. Alternatively, you can
specify one of these options:
•
Insert records into any available position (ANYWHERE)
•
Append records to the end of a file (APPEND)
For example, in a relative file in which only record number 10 contains data, an
application can insert a new record into any zero-length record (such as record number
5). If the application indicates that any available position is permitted, the record is
written to an empty location based on last file access point.
If you use the ANYWHERE option for a partitioned file, SQL/MP sets the initial “insert
anywhere” partition to partition 0 when the file is opened. If an insert to that partition
fails, SQL searches forward sequentially through partitions until it finds an empty
position for the record. It then sets the “insert anywhere” partition to partition 0 and
uses that partition for subsequent INSERT ANYWHERE operations. When that
partition is full, SQL continues to search forward sequentially until an empty position is
found. When the last partition in the file is determined to be full, SQL sets the “insert
anywhere” partition back to partition 0. When all partitions are full, SQL returns a file
full error.
Uses of Relative Files
Relative files are best suited for applications in which random access to fixed-length
records is required and in which the record number has some meaningful relationship
to a particular piece of data within each logical record. An inventory file, for example,
could be a relative file with the part number serving as the record number. Such use,
however, would probably waste disk space, because part-numbering schemes often
leave large gaps in the overall number sequence; as a result, many records can be
allocated but not used.
An invoice file with the invoice number serving as the record number might be a better
candidate for the relative file type because there are typically no large gaps in that type
of numbering scheme. If your invoice numbers begin at some large number, such as
10,000, you probably should use an address conversion algorithm to generate a record
number sequence that begins at zero and then include the actual invoice number as a
data field within the record.