Universally unique identifier (UUID) values in Db2 for z/OS
FL 508 A universally unique Identifier (UUID) is a 128-bit label used to uniquely identify objects in computer systems, without central authority. UUIDs are commonly used in microservices and cloud services.
Other identifier types, such as identity columns, might only be unique within a single database or application, they are often not universally unique. This lack of universal uniqueness makes them unsuitable for distributed environments and cloud services, where multiple nodes can generate IDs independently, and cannot reliably identify data elements across systems, applications, and databases. Other types of identifiers can also be easy to guess or predict, which renders them ineffective as a defense against malicious referencing and unsuitable as public identifiers.
Db2 for z/OS supports generation of UUID values as defined in the version 4 algorithm defined in the RFC 4122 industry standard. For more information about the RFC 4122 industry standard, see https://www.ietf.org/rfc/rfc4122.html.
The version 4 algorithm generates UUIDs with high randomness to ensure a high degree of uniqueness, where UUID is 128 randomly generated bits with six bits at certain positions set to particular values and formatted into blocks of hexadecimal digits separated by hyphens ('-'). In the following version 4 example, shows a version 4 UUID value. The digit at position 1 is reserved for the version, and is always '4'. The digit at position 2 is reserved for variant, and is always one of the following characters: '8', '9', 'A' or 'B'.
AA97B177-9383-4934-8543-0F91A7A02836
^ ^
1 2
You can use the following built-in scalar functions to generate and convert UUID values in Db2 for z/OS.
| Function | Purpose |
|---|---|
| GENERATE_UUID |
The GENERATE_UUID function returns the formatted string representation of a universally unique identifier (UUID) by using the version 4 algorithm. The result of the function is a CHAR(36) value that has the following format: xxxxxxxx-xxxx-4xxx-xxxx-xxxxxxxxxxxx |
| GENERATE_UUID_BINARY |
The GENERATE_UUID_BINARY function returns the binary string representation of a universally unique identifier (UUID) by using the version 4 algorithm. The result of the function is a BINARY(16) value. |
| VARBINARY_FORMAT | The VARBINARY_FORMAT function returns a binary string representation of a character string that has been formatted using a format-string. |
| VARCHAR_FORMAT_BINARY | The VARCHAR_FORMAT_BINARY function returns a character string representation of a bit string that has been formatted using a format-string. |
To call these functions, applications must run at application compatibility level V13R1M508 or higher.
The GENERATE_UUID and GENERATE_UUID_BINARY functions also require the availability of true random number generation features that are available only when Db2 is running on IBM z14® or later hardware. Otherwise Db2 issue SQLCODE -904 with reason code 00E73007 and resource type 00002301.
Performance considerations for storing UUID values in Db2 for z/OS
For example, the probability that a UUID value will be duplicated is not zero due to the randomized nature of the UUID, but when UUIDs values are stored in a Db2 table column, a unique index on the UUID column can guarantee absolute uniqueness. However, applying a unique index to a key with random distribution can impact performance, so you want to use the GENERATE_UUID_BINARY function in this situation. The GENERATE_UUID_BINARY function returns a BINARY(16) key, which is shorter and generally more efficient than the CHAR(36) key returned by GENERATE_UUID.
Memory usage is also a concern when storing UUID values in Db2 because the formatted CHAR(36) string representation occupies 36 bytes in memory. For this reason, the recommendation for tables that store UUID values in many rows is to store UUIDs in the binary format that the GENERATE_UUID_BINARY function generates. In this case, applications that must display a readable text form of the UUID values can call the VARCHAR_FORMAT_BINARY scalar function to convert from the BINARY(16) form to the CHAR(36) representation.
Example of using UUID values in a table column
The following statement creates and example table named EMP_UPDATE with a column named UUID.
CREATE TABLE EMP_UPDATE
UUID CHAR(36),
EMPNO CHAR(6),
TEXT VARCHAR(1000));The following statements call the GENERATE_UUID function to generate values for the UUID column when inserting rows into the EMP_UPDATE table.
ROWS=2;
INSERT INTO EMP_UPDATE VALUES
(GENERATE_UUID(),'000020','Update entry 1...');
INSERT INTO EMP_UPDATE VALUES
(GENERATE_UUID(),'000050','Update entry 2...');
The following statement selects UUID values from the EMP_UPDATE table.
/* Select UUIDs from table */
SELECT UUID FROM TABLE EMP_UPDATE;The following statement uses a readable text value in the WHERE clause
/* Specify the UUID as readable text in SQL*/
SELECT EMPNO
FROM EMP_UPDATE
WHERE UUID = UPPER('59cdd884-2c67-4350-aaa0-1a18fbf50b23') 