Creating a unique value across multiple partitions

This is another case of stumbling upon something that I am likely to use in the future. As I work in a multiple partition environment I need to keep rows of data unique not only with each file but also across partitions. Using an identity column will keep the rows unique within one table, but not across the same table in different partitions.

While searching for something else I found the SQL scalar function GENERATE_UNIQUE. This creates a 13 long bit data character value that is made up of an UTC timestamp and the system serial number. This can be used for a unique value in the table as each successive row that is added has a different timestamp value.

I can how what this looks like using the following statement:

VALUES GENERATE_UNIQUE()

This returns the following:

00001 -------------- ☑ac冡Äf‹w&☑

I can extract the timestamp from the result using the TIMESTAMP function:

VALUES TIMESTAMP(GENERATE_UNIQUE())

Which gives me:

00001 -------------------------- 2023-06-22 12:59:42.133424

I have been unable to find a way to extract the serial number from the bit data.

To show this in a "real life" scenario I need a SQL DDL table:

01 CREATE TABLE MYLIB.TESTTABLE ( 02 UNIQUE_ID FOR COLUMN "UNIQUEID" CHAR(13) FOR BIT DATA, 03 IDENTITY FOR UNIQUE NUMERIC(10,0) GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CYCLE), 04 OTHER_COLUMN FOR COLUMN OTHERCOL VARCHAR(20), 05 PRIMARY KEY(UNIQUE_ID,IDENTITY)) ;

Line 1: I am creating the table TESTTABLE in my library.

Line 2: This is the line that defines the column I will be inserting the value generated by GENERATE_UNIQUE. It has to be a 13 long character column. And needs to be defined to contain bit data, the FOR BIT DATA does that.

Lines 3: I have added an identity column to this table. I still see a use for an identity column as the combination of the unique id and the identity will guarantee uniqueness.

Line 4: Just another column. Its purpose is just to be used in the insert statement.

Line 5: The primary key defines that this is the primary key for the table. A table can only have one primary key, but can have multiple UNIQUE constraints.

After the table is created I can then insert data into it:

01 INSERT INTO TESTTABLE (UNIQUE_ID,OTHER_COLUMN) 02 VALUES(GENERATE_UNIQUE(),'Hello')

Line 1: I only have to populate the UNIQUE_ID add OTHER_COLUMN columns. The identity is populated when I insert the new row.

Line 2: I use the GENERATE_UNIQUE scalar for the value to insert into the UNIQUE_ID column.

To see what has been inserted I use a simple Select statement:

SELECT * FROM TESTTABLE

The result is:

UNIQUE_ID IDENTITY OTHER_COLUMN --------------- -------- ------------ ☑ac冡À[Š;☑ 1 Hello

The unique id was populated with the unique bit data. The identity was populated with the next identity value, which is "1" as this is the first row inserted into the table.

As I showed above with the VALUES I can retrieve the timestamp part of the unique id. Below is how I can retrieve it from the unique column in the table using the TIMESTAMP function:

SELECT UNIQUE_ID,TIMESTAMP(UNIQUE_ID) FROM TESTTABLE

The result is:

UNIQUE_ID 00002 --------------- -------------------------- ☑ac冡À[Š;☑ 2023-06-22 13:04:38.491619

I know this is not related to GENERATE_UNIQUE, but if I wanted to get the serial number using SQL I do it in one of two ways:

I can retrieve it from the QAOMHWR table in the QPFRDATA library. The machine's serial number is in the DOSSER column:

SELECT DOSSER FROM QPFRDATA.QAPMHDWR LIMIT 1

Which returns the following:

DOSSER -------- 99-9999X

Or I can retrieve the serial number from the system values with the following statement:

SELECT CURRENT_CHARACTER_VALUE FROM QSYS2.SYSTEM_VALUE_INFO WHERE SYSTEM_VALUE_NAME = 'QSRLNBR'

This returns:

CURRENT_CHARACTER_VALUE ----------------------- 999999X

 

You can learn more about the GENERATE_UNIQUE SQL scalar function from the IBM website here.

 

This article was written for IBM i 7.5 TR2 and 7.4 TR8.