New UUID functions in Db2 12.1.3

When I looked over the section “What’s new in Db2 12.1.3”, I saw that two functions to generate UUIDs (universally unique identifiers) are listed as part of the SQL enhancements. The functions are GENERATE_UUID and GENERATE_UUID_BINARY.

The documentation for the first function states:

The GENERATE_UUID function returns the formatted string representation of a Universally Unique Identifier (UUID) using the version 4 algorithm.

What exactly is a UUID and what does “version 4 algrothm” mean? I will discuss this later. First, let’s take a look at the function and its output:

VALUES(GENERATE_UUID());

1                                   
------------------------------------
F7F98DD8-E429-46B8-BF76-E69E854EF009

  1 record(s) selected.

What are UUIDs?

A UUID, a universally unique identifier, is a randomly-generated number. By design it is supposed to be unique from any other UUID. According to its specification, a UUID is 128 bits in size and, as text, is typically represented in the format XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX (see the output above). There are different ways of generating UUIDs which are called UUID versions, including date-time, MAC address, security-related data and hashes on environment data, and by utilizing the random number generator. If only “random” is the method applied, it is called version 4. This is the method used by IBM for Db2 LUW, z/OS and i.

Historically, there have also been different variants of UUIDs, but most systems now use what is shipped by Db2. Utilizing reserved bits, the information about version and variant is encoded in every UUID.

-- create a simple table to hold a UUID and a string
create table ut(uuid char(36), s varchar(20));

-- insert 10 rows
insert into ut values(generate_uuid(), 's1');
insert into ut values(generate_uuid(), 's2');
insert into ut values(generate_uuid(), 's3');
insert into ut values(generate_uuid(), 's4');
insert into ut values(generate_uuid(), 's5');
insert into ut values(generate_uuid(), 's6');
insert into ut values(generate_uuid(), 's7');
insert into ut values(generate_uuid(), 's8');
insert into ut values(generate_uuid(), 's9');
insert into ut values(generate_uuid(), 'sA');

The above image shows the ten rows with the generated UUIDs. I marked the positions of interest within the UUIDs. They show that indeed UUID version 4 and the RFC 9562 variant is used (values 8, 9, A, or B).

Converting UUIDs between string and binary representation

If you don’t want to use the 36 char string representation, but want to have the UUID as BINARY(16), then you could either use GENERATE_UUID_BINARY or convert the string using VARCHAR_FORMAT_BINARY. Or, coming from binary, convert back to string via VARCHAR_BIT_FORMAT.

I have tested the roundtrip which is (or seems to be) lossless:

select VARCHAR_FORMAT_BINARY(VARCHAR_BIT_FORMAT(uuid,'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX'),'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX'), s from ut

The above returns the same table as show in the screenshot above.

Conclusions

When I made plans to blog about the new UUID functions in Db2, I never thought that it would be that interesting. Digging deeper into the world of UUIDs revealed that it is more than just a unique identifier. I hope you enjoyed it, too. 🔢🦄

If you have feedback, suggestions, or questions about this post, please reach out to me on Mastodon (@data_henrik@mastodon.social) or LinkedIn.