13.6 Handling Relative Record Numbers
Under the IBM i, when a record is added to a database file, it is automatically allocated a unique relative record number.
This number can be used for the fast access of records when the relative record number is known.
A record's relative record number may also change when a database file is reorganized.
No user intervention is required for any of this to happen.
Visual LANSA does not have the same concept of relative record numbers as the IBM i database.
Under Visual LANSA, this automatic allocation of a relative record number must be "emulated". The emulation of relative record numbers will allow your existing applications that use relative record numbers to execute without being changed.
You should be aware of the following:
- To achieve this, an extra column @@RRNO (defined in the LANSA repository) is included with every table definition.
- An index is created for the @@RRNO column in the SQL/ODBC table and this is used for relative record access to the table by RDML functions.
- Because of this required "emulation" of relative record numbers, data access by relative record number will not have the same performance improvements under Visual LANSA as it does under the IBM i, and there is a small performance impact on INSERT operations to assign a new relative record number.
- Relative record numbers uniquely identify a row within a table on the current platform. There is no association at all between relative record numbers on differing platforms/servers. The only way to uniquely identify a row across multiple platforms is by its "primary key".
- The use of the relative record number column as a Foreign key in another File is not recommended. The primary objection is that your application will not be portable to the System i. On the System i, the relative record number is not a data element in the file; its a physical offset on the disk. This offset must be re-arranged periodically as part of a well maintained system. This changes the relative record number of a data row. There is also the less important issue that even on non System i platforms, the relative record number may need to be renumbered if all the numbers become used. Given that it provides for up to 15 digits worth of rows to be created, it is unlikely to be reached in any practical application for many years to come.
There are two methods of assigning the relative record number.
- Method 1 - Set auto generation flag to Yes
This is the preferred method of assigning the relative record number. It is faster than Method 2 and is easier to administer as it is contained entirely in the database.
Set the flag set to Yes, to automatically assign the number.
- Method 2 - Use an external file
Note: This method of assigning the relative record number is for backward compatibility and may become obsolete in the future.
The number assigned to this column when a row is inserted into a table is allocated from a file created in the path specified by the RPTH= parameter of the x_run function.
Where the SQL/ODBC table resides on a server and is shared by multiple clients, so should the file nominated by the RPTH= parameter. This means that all users sharing the SQL/ODBC table(s) are also sharing the relative record number assignment files and thus allocating numbers from the same "bucket".
There is one file created for each SQL/ODBC table in this format: <Table Name>.RRN.