By Cynthia Suo and Paul McWilliams.
Function level 508 of Db2 13 for z/OS introduces four new built-in scalar functions support generation and formatting of universally unique identifier (UUID) values in Db2 for z/OS, GENERATE_UUID and GENERATE_UUID_BINARY for generating the UUID values, VARBINARY_FORMAT and VACHAR_FORMAT_BINARY for converting the UUID values.
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. With this enhancement, UUIDs can be used to uniquely identify rows in a Db2 table. As Db2 for z/OS users manage increasingly complex and distributed data environments, the need for UUIDs that can be reliably generated and used across multiple systems, applications, and databases has become essential.
Before this enhancement, Db2 supports various kinds of identifiers that can be unique within a single Db2 environment, such as the GENERATE_UNIQUE_BINARY function, AS IDENTITY columns, sequences, ROWID columns, or even user application generated values. However, these values are not unique outside of the Db2 environment where they are generated, and in some cases, they are easy to predict. UUIDs provide a solution that enables identifiers to be effectively unique beyond the boundaries of a single Db2 environment.
These following new built-in functions support generation of UUID values as defined in the version 4 algorithm defined in the RFC 4122 industry standard. For more information, see https://www.ietf.org/rfc/rfc4122.html.
GENERATE_UUID 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, with uppercase characters:
xxxxxxxx-xxxx-4xxx-xxxx-xxxxxxxxxxxx
GENERATE_UUID_BINARY returns the binary string representation of a universally unique identifier (UUID) by using the version 4 algorithm. The result is a BINARY(16) value.
VARBINARY_FORMAT returns a character string representation of a bit string that has been formatted using a format-string.
VARCHAR_FORMAT_BINARY function returns a character string representation of a bit string that has been formatted using a format-string.
These new functions can be called by applications that run at application compatibility level V13R1M508 or higher.
The probability that a UUID can be duplicated is not zero, but he probability to find a duplicate within 103 trillion version 4 UUIDs is one in a billion. When you store a UUID generated values in a Db2 table column, you can create unique index on the UUID column to guarantee absolute uniqueness. However, applying a unique index to a key with random distribution can impact performance, so you might want to use the GENERATE_UUID_BINARY function in this situation. GENERATE_UUID_BINARY returns a BINARY(16) key, which is shorter and generally more efficient to process than the CHAR(36) key returned by the GENERATE_UUID function.
#Db2forz/OS