New SQL scalar functions to generate UUID

At times there a coincidences, and this is one. I had been asked to add a UUID column to a SQL table that is sent to another application on a non-IBM server. My original plans were to use the MI program _GENUUID to generate the UUID. Then, to my delight I find that the latest Technology Refreshes, IBM i TR5 and IBM i TR11, includes two new SQL scalar functions that will generate an UUID. Which is ideal for me as I was using a SQL Insert statement to add data to the interface's table.

What is UUID? Without going into too much detail, it stands for "Universally Unique Identifier". It is administered by the Internet Engineering Task Force, IETF, with a standard, RFC9562, which defines it as: "A UUID is 128 bits long and is intended to guarantee uniqueness across space and time". If you are interested in learning more the standard's document is here.

In character form a version 4 UUID is 36 characters, and is formatted thus:

XXXXXXXX-XXXX-4XXX-XXXX-XXXXXXXXXXXX

There is the potential for a "collision", duplicate, being generated. The probability of generating a collision would happen in 1 in 2.71 quintillion (2.71 x 10¹⁸) transactions, which is such a small number of occurrences it can be regarded as the same as zero.

There are two scalar functions that generate the UUID:

1. GENERATE_UUID:  Generates an UUID that is 36 characters.
2. GENERATE_UUID_BINARY:  Generates an UUID that is binary and 16 characters.

Like all SQL scalar functions I can execute them using the SQL Values clause. First the GENERATRE_UUID:

01 VALUES GENERATE_UUID()

The result is:

00001 ------------------------------------ EB81B055-90D5-4104-9F82-1A4E282E5F1A

And the GENERATE_UUID_BINARY:

01 VALUES GENERATE_UUID_BINARY()

Which returns:

00001 -------------------------------- D43169EC75374E308CF6F1900C3D61A7

That is not really exciting, let me insert this data into a DDL Table and then "play" with the data.

My Table has three columns:

01 CREATE TABLE MYLIB.TESTTABLE( 02 COUNTER SMALLINT, 03 UUID_CHAR VARCHAR(36), 04 UUID_BIN BINARY(16)) ;

I can check the layout of the Table using the SQL View SYSCOLUMNS2:

01 SELECT COLUMN_NAME,DATA_TYPE,LENGTH,NUMERIC_SCALE, 02 DDS_TYPE 03 FROM QSYS2.SYSCOLUMNS2 04 WHERE TABLE_SCHEMA = 'MYLIB' 05 AND TABLE_NAME = 'TESTTABLE' 06 ORDER BY ORDINAL_POSITION

Which returns the following:

COLUMN DATA NUMERIC DDS_ _NAME _TYPE LENGTH _SCALE TYPE --------- -------- ------ ------- ---- COUNTER SMALLINT 2 0 B UUID_CHAR VARCHAR 36 <NULL> A UUID_BIN BINARY 16 <NULL> 5

The SQL SMALLINT is the equivalent of Binary data type in DDS. DDS type 5 is to indicate Binary character data.

I used the following SQL Insert statement to add three rows to the Table:

01 INSERT INTO TESTTABLE 02 VALUES (1,GENERATE_UUID(),GENERATE_UUID_BINARY()), 03 (2,GENERATE_UUID(),GENERATE_UUID_BINARY()), 04 (3,GENERATE_UUID(),GENERATE_UUID_BINARY())

I can check the results with:

01 SELECT * FROM TESTTABLE ; COUNTER UUID_CHAR UUID_BIN ------- ------------------------------------ -------------------------------- 1 479EF372-0639-44BA-834D-ED6DCCFA4442 0820997433FB4211815913131FB8908F 2 6D4B394B-F6EA-4696-BC91-2C2B5A72C38E 1D5BA09210004698874A60FA7B33FDE6 3 C5E928C3-8013-489B-B2F0-7AFBDAC62B7E 35A3B3873ACE4C0992B34BA245EBB599

The two UUIDs on each row will not be the same as their values were inserted by two occurrences of the scalar functions, each one would have calculated their own result.

How could I convert the UUID column to the other type, in other words character to binary format and vice versa.

Fortunately there are two other SQL scalar functions I can use. The first is VARBINARY_FORMAT that will convert a character string to a binary character string. I use the VARBINARY_FORMAT to convert the values generated by the GENERATE_UUID into binary.

01 SELECT COUNTER,UUID_CHAR, 02 VARBINARY_FORMAT(UUID_CHAR, 'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX') 03 FROM TESTTABLE

Line 2: VARBINARY_FORMAT requires two parameters. The first is the name of the character column or string, and the second is the format it is in. In this case I give "XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX" as that is the format of data in the UUID_CHAR column.

The results are:

COUNTER UUID_CHAR 00003 ------- ------------------------------------ -------------------------------- 1 479EF372-0639-44BA-834D-ED6DCCFA4442 479EF372063944BA834DED6DCCFA4442 2 6D4B394B-F6EA-4696-BC91-2C2B5A72C38E 6D4B394BF6EA4696BC912C2B5A72C38E 3 C5E928C3-8013-489B-B2F0-7AFBDAC62B7E C5E928C38013489BB2F07AFBDAC62B7E

At first glance the results look identical, just without the hyphens ( - ). This is somewhat true, but you have to remember that the two columns are different data types.

I can do the conversion the other, binary character to character, using the VARCHAR_FORMAT_BINARY scalar function:

01 SELECT COUNTER,UUID_BIN, 02 VARCHAR_FORMAT_BINARY(UUID_BIN, 'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX') 03 FROM TESTTABLE

Line 2: VARCHAR_FORMAT_BINARY has two parameters. The first is the name of the binary character column or string, and the second is the format I want the resulting character string to be in.

This returns:

COUNTER UUID_BIN 00003 ------- -------------------------------- ------------------------------------ 1 0820997433FB4211815913131FB8908F 08209974-33FB-4211-8159-13131FB8908F 2 1D5BA09210004698874A60FA7B33FDE6 1D5BA092-1000-4698-874A-60FA7B33FDE6 3 35A3B3873ACE4C0992B34BA245EBB599 35A3B387-3ACE-4C09-92B3-4BA245EBB599

It does look like all VARCHAR_FORMAT_BINARY did was format the string. But it did also convert the data from a binary value to character.

I will be using this in the interface program I mentioned at the start of this article as this is simpler code than using the MI program.

You can learn more about this from the IBM website:

• GENERATE_UUID SQL scalar function
• GENERATE_UUID_BINARY SQL scalar function
• VARCHAR_FORMAT_BINARY SQL scalar function
• VARBINARY_FORMAT SQL scalar function

 

This article was written for IBM i 7.5 TR5 and 7.4 TR11.