Enable UUID as a column type, and generation of UUIDs

Honestly, I'm not confident how many costomers will use this function. However, I think It's meaningful to implement because many costomers think about cloud integration.

Hello Rune, Just a friendly reminder, if you can please explain why UUID is a better choice compared to Identify, ROWID. This information will help as reasons to promote this epic in our approval process. Thank you so much!

Sincerely,

The Db2 for z/OS development team

This looks like a very nice feature indeed. Not just for cross system/enterprise JOINing of data but for normal data processing where a "guaranteed unique" key across the world would be extremely useful!

Hello, We greatly appreciate your interest in our new solution design! Regarding your question - will it be possible to specify the UUID as clear text (readable) in SQL, Cobol and datasets? Our answer is YES.

It was mentioned in the Aha idea that UUID is used in combination with cloud services. For us to better understand your requirement, please give an example of use case (with specific cloud application service syntax if possible), and why it’s important to use the UNIQUE ID in this format. Additionally, Db2z provides ROWID, Identity columns and Sequences to uniquely identify rows in a table. Please explain why UUID is a better choice compared to Identify, ROWID. This information will help as reasons to promote this epic in our approval process. Thank you so much!

For your example, if you have the following DDL:

CREATE TABLE EMP_UPDATE

(UUID CHAR(36),

EMPNO CHAR(6),

TEXT VARCHAR(1000));

INSERT INTO EMP_UPDATE VALUES

(GENERATE_UUID(),'000020','Update entry 1...');

INSERT INTO EMP_UPDATE VALUES

(GENERATE_UUID(),'000050','Update entry 2...');

You can use the same query with the UPPER function since "the result of the function GENERATE_UUID() is a CHAR(36) value formatted like xxxxxxxx-xxxx-4xxx-xxxx-xxxxxxxxxxxx. Characters in the string are in upper case." as we stated before.

SELECT EMPNO

FROM EMP_UPDATE

WHERE UUID = UPPER('59cdd884-2c67-4350-aaa0-1a18fbf50b23');

SIncerely,

The Db2 for z/OS development team

Dear Rune, Just a friendly reminder- we would like to know if our solution providing functions to generate the UUID would meet your requirements?

Kind regards,

The Db2 for z/OS development team

Dear Rune,

We have an update for you, below, and would like to know if our solution providing functions to generate the UUID would meet your requirements?

Background:

SLC had several discussions about UUID. And concluded that we will support new UUID Scalar functions instead of new data type.

GENERATE_UUID
The GENERATE_UUID function returns the formatted string representation of a Universally Unique Identifier (UUID) using the version 4 algorithm. The result of the function is a CHAR(36) value formatted like xxxxxxxx-xxxx-4xxx-xxxx-xxxxxxxxxxxx. Characters in the string are in upper case. The CCSID of the result is the default SBCS CCSID at the current server. The result cannot be null. GENERATE_UUID is a non-deterministic function.

GENERATE_UUID_BINARY
The GENERATE_UUID_BINARY function returns the binary string representation of a Universally Unique Identifier (UUID) using the version 4 algorithm. The result of the function is a BINARY(16) value. The result cannot be null. GENERATE_UUID_BINARY is a non-deterministic function.

I have developed the following example:

CREATE TABLE EMP_UPDATE
(UUID BINARY(16),
EMPNO CHAR(6),
TEXT VARCHAR(1000));

ROWS=2;
INSERT INTO EMP_UPDATE
VALUES
(GENERATE_UUID_BINARY(),'000020','Update entry 1...'),
(GENERATE_UUID_BINARY(),'000050','Update entry 2...');

So you can select UUID from table: SELECT UUID FROM TABLE EMP_UPDATE;

Please let us know if you have any questions or comments.

Sincerely,

The Db2 for z/OS development team

Dear Rune,

Thank you for submitting this Db2 for z/OS enhancement request. We have reviewed this request and believe it is a good candidate for the Db2 for z/OS product and placed it in Future Consideration state. However, after additional consideration, we have since realized that it does not align within functionality prioritized for delivery within the next 12 months. Therefore, unfortunately, we will not be considering this idea at this time.

We appreciate your input to the Db2 for z/OS development team. We hope that you will continue to submit enhancement suggestions for improvements as customer feedback is a key component to shaping the future direction of Db2 for z/OS.

Sincerely,

Db2 for z/OS Team

Being able to join data using UUID is essential to our architecture as we span several platforms having DB2 as a common data repository. By not having an effective method supporting UUIDs in JOIN I would be compelled to consider other Databases for my architecture.