Version 5.8.01, which will be released shortly, includes support for 64-bit data set IDs. The following article explains the benefits and consequences of this extension.
Introduction
The dataset ID is a component of each dataset stored in the database and uniquely identifies it (within its table). The dataset ID is assigned by the database server using the so-called prime counter. This is a 32-bit signed value that is managed for each table. A newly created table initially has a prime counter of 0. If data records are inserted, the value of the prime counter changes continuously. The number of available data record IDs is therefore reduced with each inserted record. Deleting a record, on the other hand, does not change the prime counter, which means that the prime counter will inevitably overflow if a large number of delete and insert operations are carried out. In this case, no more data records can be inserted into the table without further action.
The prime counter can only be reset if the table does not contain any data records. In the event of an overflow, the consequence of this is to play out the existing data records, reset the prime counter (perform a recover) and then import the data records again. The process can take a very long time depending on the data volume and existing keys. Furthermore, the next overflow is foreseeable as the underlying problem has not changed.
Conversion to 64-bit
As the prime counter is represented by a 32-bit value, this results in a value range of -2,147,483,647 to +2,147,483,647. This results in a maximum number of 4,294,967,295 insert operations until an overflow occurs. The solution to the problem is to switch the prime counter to 64-bit and thus to over 18 trillion (18,000,000,000,000,000,000,000,000) different values. This will cover the supply of data set IDs for the foreseeable future (Erläuterung).
Version 5.8.01 follows on from the considerations described above, so that it is now possible to convert tables to 64-bit primes. However, this requires the database to be converted to version 5.8.
Allocation of prime values
The prime counter from version 5.8.01 is defined as a 64-bit unsigned value. The dataset IDs are therefore all positive (> 0) and are assigned consecutively.
However, existing data records (from version 5.7 or earlier) also contain data record IDs that are negative. These are now also returned by the database server in a positive representation. The dataset ID of an already saved dataset is therefore different from the dataset ID of version 5.8. If the dataset ID is saved by the application as a reference to a target dataset, adjustments must be made in the application so that the referenced dataset can be found.
We will publish a blog article with solutions to this topic separately.
Activation of the 64-bit dataset IDs
The 64-bit data record IDs must be activated explicitly. To do this, activate the “64-bit data record IDs” checkbox for the desired table in the data structure editor (see Fig. 1).
Activation can not be carried out under the following conditions (check mark is grayed out):
- This is a subordinate file.
- The “sequential insert” option is not set.
- The prime counter (visible at the top right of the data structure editor) is greater than 0xFAFFFFFFFF.
Activation is not required for subordinate tables, as processing is defined via the main file.
64-bit dataset IDs are not possible for files without sequential insertion. If you still want to activate it, the “sequential insert” option must be activated. However, this is only possible if the file is empty.
If the prime counter is greater than the limit value 0xFAFFFFFFFF, the datasets must be exported, the prime counter reset and then the datasets re-imported.
In the majority of cases, however, this limit will never be reached. This means that 64-bit dataset IDs can be activated by simply setting the option. The lengthy procedure described above (export/import of datasets) is no longer necessary.
Checking existing tables
A small procedure is available at the end of the article so that you can check which tables may have exceeded the prime counter (i.e. > 0xFAFFFFFFFF) before upgrading to version 5.8. This analyzes the prime counter of all tables in the database and generates a status code according to the traffic light system. After starting the procedure in the database to be analyzed, a text file is generated and displayed:
Prime statistics for MyDataBase database
File : 1
Prime : -20.000
Used prime values : 20.000
Available prime values (5.7) : 4.294.947.295 (99%)
Available prime values (5.8) : 4.194.283.999 (99%)
Usage for version 5.8 : green
File : 2
Prime : -2
Used prime values : 2
Available prime values (5.7) : 4.294.967.293 (99%)
Available prime values (5.8) : 4.194.303.997 (99%)
Usage for version 5.8 : green
* Summary
2 files checked.
2 files with status green.
No file with status yellow.
No file with status red.
________________________________________________________
green : The file can be used in version 5.8 without modification.
yellow : The file can be used in version 5.8. However, the 64-bit data set IDs should be activated.
red : No more prime values are available. For use with version 5.8, the data records must be played out,
a recover must be carried out and then the data records must be imported again.
The used and available prime values are displayed for each table. In the above example of the ‘MyDataBase’ database, there are two tables, both with status ‘green’, as there are still 99% available prime values. The summary at the end of the output shows how many tables exist with status green, yellow and red. This is followed by an explanation of the colors:
- green : The file can be used in version 5.8 without any changes. An overflow of the prime counter has not yet occurred.
- yellow : The file can be used in version 5.8. However, the 64-bit data set IDs should be activated.
- red : No more prime values are available. The prime counter has already overflowed. For use with version 5.8, the data records must be exported, a recover performed and then the data records imported again. The 64-bit data record IDs should then be activated for the table.
Outlook
The second part of the article explains why the procedural processing of 64-bit data set IDs also requires adaptations to some concept 16 commands.