Generating identity values in a multi-host database management system
Various approaches for generating key values in a database that is shared in a multi-host data processing arrangement. According to an example method, a shared sequence control structure contains a next key value. Mutually exclusive update access to the sequence control structure is provided for a plurality of instances of a database management system (DBMS) executing on the multi-host data processing arrangement. For a request for a new key value for a database record, a temporary copy of the next key value from the sequence control structure is stored, the sequence control structure is updated with a new next key value, and temporary copy of the next key value is stored as the new key value of the database record.
1 . A processor-implemented method for generating key values in a database that is shared in a multi-host data processing arrangement, comprising:
storing a sequence control structure including a next key value;
providing mutually exclusive update access to the sequence control structure by a plurality of instances of a database management system (DBMS) executing on the multi-host data processing arrangement; and for a request for a new key value for a database record, storing a temporary copy of the next key value from the sequence control structure, updating the sequence control structure with a new next key value after storing the temporary copy, and storing the temporary copy of the next key value as the new key value of the database record after updating the sequence control structure.
2 . The method of claim 1 , wherein the updating includes committing the new next key value to retentive storage prior to the storing of the temporary copy of the next key value as the new key value of the database record.
3 . The method of claim 1 , wherein the storing includes storing the sequence control structure in a database file in which the record is to be added to the database.
4 . The method of claim 1 , further comprising:
storing an increment value in the sequence control structure; and wherein the updating of the sequence control structure with a new next key value includes adding the increment value to the next key value.
5 . The method of claim 4 , wherein the storing of the increment value is in response to a name and value specified in a structured query language (SQL) CREATE TABLE statement.
6 . The method of claim 4 , further comprising:
wherein the increment value is negative;
storing a minimum key value in the sequence control structure;
storing a maximum key value in the sequence control structure; and
in response to the new next key value being less than the minimum key value after the adding of the increment value, storing the maximum key value as the new next key value.
7 . The method of claim 4 , further comprising: wherein the increment value is positive; storing a minimum key value in the sequence control structure; storing a maximum key value in the sequence control structure; and in response to the new next key value being greater than the maximum key value after the adding of the increment value, storing the minimum key value as the new next key value.
8 . The method of claim 4 , further comprising: wherein the increment value is negative; storing a cycle flag value in the sequence control structure; storing a minimum key value in the sequence control structure; storing a maximum key value in the sequence control structure; in response to the cycle flag value having a first value and the new next key value being less than the minimum key value after the adding of the increment value, storing the maximum key value as the new next key value; and in response to the cycle flag value having a second value and the new next key value being less than the minimum key value after the adding of the increment value, stopping generation of new key values for subsequent requests.
9 . The method of claim 8 , wherein the storing of the cycle flag value, the minimum key value, and the maximum key value is in response to respective name-value pairs specified in a structured query language (SQL) CREATE TABLE statement.
10 . The method of claim 4 , further comprising: wherein the increment value is positive; storing a cycle flag value in the sequence control structure; storing a minimum key value in the sequence control structure; storing a maximum key value in the sequence control structure; in response to the cycle flag value having a first value and the new next key value being greater than the maximum key value after the adding of the increment value, storing the minimum key value as the new next key value; and in response to the cycle flag value having a second value and the new next key value being greater than the maximum key value after the adding of the increment value, stopping generation of new key values for subsequent requests.
11 . The method of claim 1 , further comprising: storing a key cache value in the sequence control structure; storing an increment value in the sequence control structure; and wherein the updating of the sequence control structure with a new next key value includes adding a product of the increment value and the key cache value to the next key value.
12 . The method of claim 11 , wherein the storing of the key cache value and the increment value is in response to respective name-value pairs specified in a structured query language (SQL) CREATE TABLE statement.
13 . The method of claim 11 , further comprising: storing the key cache value in respective host control structures for the DBMS instances; storing the increment value in the host control structures; wherein for the request for a new key value being by one of the DBMS instances, adding the increment value from the host control structure of the one DBMS instance to the temporary copy of the next key value resulting in a DBMS-instance next key value, and storing the DBMS-instance next key value in the host control structure for the one DBMS instance; wherein, for each of a number of subsequent requests for new key values needed for database records by the one DBMS instance, the number of subsequent new key values being equal to the key cache value in the respective host control structure, the DBMS instance uses the DBMS-instance next key value from the respective host control structure and updates the DBMS-instance next key value using the increment value in the host control structure; and in response to the number of subsequent requests for new key values exceeding the key cache value in the respective host control structure, accessing the sequence control structure for the next key value.
14 . The method of claim 1 , wherein the storing of the sequence control structure is in response to a structured query language (SQL) CREATE TABLE statement.
15 . The method of claim 1 , further comprising updating the sequence control structure in response to a structured query language (SQL) ALTER TABLE statement.
16 . An apparatus for generating key values in a database that is shared in a multi-host data processing arrangement, comprising: means for storing a sequence control structure including a next key value, wherein the sequence control structure is shared by a plurality of database management system (DBMS) instances; means, responsive to a request for a new key value for a record to be added to the database, for locking for update by one of the plurality of DBMS instances, a respective host control structure associated with the one DBMS instance and the sequence control structure; means for updating the host-local control structure with a next key value from the sequence control structure; means for updating the sequence control structure with a new next key value; means for storing the sequence control structure after updating the sequence control structure; means for unlocking the sequence control structure after storing the sequence control structure; means for unlocking the host-local control structure; and means for storing the next key value from the host-local control structure in a record added to the database.
17 . A multi-host data processing arrangement, comprising: at least two host data processing systems (hosts), each host including a processor arrangement and a memory coupled to the processor arrangement; a persistent storage arrangement coupled to each host for retentive storage of a database, wherein the persistent storage arrangement has stored therein a sequence control structure including a next key value; wherein each host executes a respective database management system (DBMS) instance that provides access to the database, and each host in executing a DBMS instance and in response to a request from the DBMS instance for a new key value for a database record, obtains exclusive update access to the sequence control structure for the DBMS instance, stores a temporary copy of the next key value from the sequence control structure, updates the sequence control structure with a new next key value after storing the temporary copy, and stores the temporary copy of the next key value as the new key value of the database record after updating the sequence control structure.
18 . The data processing arrangement of claim 17 , wherein each host in updating the sequence control structure, commits the new next key value to retentive storage prior to the storing of the temporary copy of the next key value as the new key value of the database record.
19 . The data processing arrangement of claim 17 , wherein the sequence control structure includes an increment value, and each host in updating the sequence control structure adds the increment value to the next key value.
20 . The method of claim 19 , wherein each host in executing the DBMS instance stores the increment value in response to a name and value specified in a structured query language (SQL) CREATE TABLE statement.