Creating A GUID In DB2 For i

August 27, 2014 Michael Sansoterra

Note: The code accompanying this article is available for download here.

Normally, modesty would forbid me from saying this, but I love Four Hundred Guru. I was looking for a way to create a globally unique identifier (GUID) also known as a universally unique identifier (UUID) so that I could write some DB2 code compatible with SQL Server. Unfortunately DB2 doesn’t have a GUID/UUID function so I had to search the web to find an alternative. What did I stumble upon? A good ol’ FHG article showing how to create a GUID/UUID in RPG that could easily be used in DB2.

The RPG code for generating a GUID can be found here (based on a call to API __GENUUID).

In case you’re not familiar with it, a GUID is a 16-byte binary value that is supposed to be unique throughout time and eternity. (Well, maybe not that long.) Whereas integer sequences (like an identity column) can be used to uniquely identify data in a single database table, GUIDs are commonly used as a way to uniquely identify row values between multiple databases or systems.

See the Wikipedia entry for more info. Be warned that the “guaranteed uniqueness” claim is dependent upon the underlying API’s implementation. Under heavy load, the GENUUID API that the RPG program uses doesn’t quite live up to its name. I’ve read reports online of it creating duplicate values when heavily used across multiple jobs. (In fact, when I invoke the sample UDF several times within a single SQL statement, the numbers returned are eerily similar.)

If you need a GUID function that lives up to the hype, Java has a reliable RandomUUID method with very low chance of duplicates. The only problem with using this implementation is that the IBM i job has to stomach the overhead of the Java Virtual Machine (JVM). See this following DB2 tips page (not specific to DB2 for i) for some sample Java code that can be used as an external user-defined function.

Choose whatever implementation (Java vs. RPG) works best for your scenario (uniqueness vs performance). For now, I’m going to continue with the RPG example because my transaction load is “light.”

I tweaked the RPG code sample a tad so that it can be invoked by DB2 as an external user-defined function. The code for program GUIDR can be downloaded here. Once the RPG program is in place, it’s easy to make the logic accessible to DB2. The instructions to compile and create the external SQL function are contained in the source’s header comments.

Imaginatively enough, I called the SQL function GUID(). It accepts no input parameters, is non-deterministic, and returns a BINARY(16) value. It can be invoked just about anywhere an SQL expression is allowed:

VALUES (GUID())

Of course a BINARY(16) value can be difficult to read, especially in the green screen! The HEX function comes in handy to make it readable:

VALUES (HEX(GUID()))

SQL Server displays its GUIDs as hyphenated hex values (CHAR(36)) as shown here:

B3F40F29-752D-4CFD-B872-4C88EEDA3F87

To make the GUID easy to read, it’s simple to make a UDF to display a GUID value as text in the same format as SQL Server by using the following function:

CREATE OR REPLACE FUNCTION NEWID(@GUID BINARY(16))
RETURNS CHAR(36) CCSID 37
SPECIFIC NEWID_SQLSERVER
BEGIN
    DECLARE @GUID_CHAR CHAR(32) NOT NULL DEFAULT '';
    SET @GUID_CHAR=HEX(@GUID);
    RETURN LEFT(@GUID_CHAR,8)||'-'||
           SUBSTR(@GUID_CHAR,9,4)||'-'||
           SUBSTR(@GUID_CHAR,13,4)||'-'||
           SUBSTR(@GUID_CHAR,17,4)||'-'||
           RIGHT(@GUID_CHAR,12);
END

This “formatting” function is called NEWID because that’s the name of SQL Server’s GUID function. Although the GUIDs are meant to be stored as BINARY(16), whenever they need to be shown to a user, the NEWID UDF or HEX built-in function can be used.

As usual, any logic that can be implemented in high level language or Java code can be easily shared with DB2 thanks to external routines and triggers. Problem solved in less than 30 minutes!

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.

Generating a Unique Identifier

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot