Tuesday, July 25, 2023

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.

3 comments:

  1. You can generate a cross system unique identifier already since the early days of IBM i with the _GENUUID function (you could create a wrapper for use in SQL). For more information see https://www.ibm.com/support/pages/genuuid-can-generate-more-random-uuid

    ReplyDelete
    Replies
    1. I will be writing about that alternative, and comparing it to what I have described above.

      Delete
  2. You can also use hex(generate_unique()) to get a human readable value, and can put this result into a character field for easier recognition.

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.