Monthly Archives: February 2010

DB2 SQL – Select query to get somewhat unique record numbers

Many old tables cannot be changed because compiled RPG programs and display files rely on that table.  To change the table would require re-compiling the programs after modifying the programs to account for the changes to the table.  That rules out altering the table and creating a ROWID or IDENTITY field.

I tried to use the SQL statement: “SELECT row_number() over() FROM MY_TABLE” to get the row number, but that only returns a relative number and not the actual record id as you would see in the green screen.

Therefore to get the unique identifier for the row you can use RRN(MY_TABLE) (Relative Record Number Sequence) to get the record id.  This will only work if the table you are using won’t be reorg’ed.  This is how you would get the record id:

SELECT RRN(MY_TABLE) AS CUSTOMER_ROWID, MY_TABLE.*
FROM MY_TABLE

When you needed to update or delete that record you would do the following

UPDATE MY_TABLE
SET FIELD1='35'
WHERE RRN(MY_TABLE)=2;

Which would update the row with row id 2

Altering the Table to create a unique column

If you’re able to alter the table  you could add a key field with the identity column attribute, which will automatically generate the value/key. Remember that this requires you to re-compile ALL compiled programs (RPG , etc..) that use this table possibly. The value will be generated even if your using non-SQL interface like RPG,CL. You get to set the column name, data type (example below BIGINT), what number to start the count at (START WITH 1) and how much to increment the number (INCREMENT BY 1). Cycle means it will restart at 1 after it is at the highest value capable for BIGINT. Generate Always means the DB always generates the value, you could use Generate By Default, and it will only generate if there wasn’t a value given for the identity column.

ALTER TABLE CUSTOMER
    ADD COLUMN ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY
    (START WITH 1 INCREMENT BY 1 CYCLE);
SELECT ID,CUSTOMER.* FROM CUSTOMER -- See the ID col next to all the rows
--Inserting rows into a table with identity column
INSERT INTO CUSTOMER (NAME) VALUE('BOB');
INSERT INTO CUSTOMER VALUE(DEFAULT,'BOB');
--If you don't want the system to generate the values when your pulling from another table you can use OVERRIDING SYSTEM VALUE
INSERT INTO CUSTOMER OVERRIDING SYSTEM VALUE
   (SELECT * FROM THIS_WEEK_CUSTOMERS)
-- Or you can override and do it per insert statement
INSERT INTO CUSTOMER VALUE(32,'BOB') OVERRIDING SYSTEM VALUE;

You can also use a sequence object to re-sequence a preexisting ID field. Just make sure you ensure referential integrity.

CREATE SEQUENCE MYLIB.SEQNUM AS DEC(7, 0);
UPDATE MYLIB.MYTABLE SET KEYFIELD1= DIGITS(NEXT VALUE FOR MYSEQ);
DROP SEQUENCE MYLIB.SEQNUM;

--or

SELECT NEXT VALUE FOR MYLIB.SEQNUM AS NEXT_SEQ_NUM FROM sysibm.sysdummy1
--Use the NEXT_SEQ_NUM value

Set a Primary Key constraint to ensure uniqueness

The code below will add a primary key constraint on the column “ID”.  This will force uniqueness on that column and if an INSERT or UPDATE statements will fail if they try to add a non-unique id. The identity attribute only generates values but doesn’t enforce uniqueness.

ALTER TABLE MYLIB.MYTABLE ADD PRIMARY KEY (ID)

 

Note: the SQL function row_number() with over() can only be used in SELECT statements because of OLAP which makes it kind of worthless…

SELECT ROWID,KEYFIELD1,FIELD1
FROM (SELECT row_number() over (order by KEYFIELD1) as ROWID, KEYFIELD1, FIELD1
      FROM MY_TABLE) as Table1

An OLAP specification is not valid in a WHERE, VALUES, GROUP BY, HAVING, or SET clause, or join-condition in an ON clause of a joined table. An OLAP specification cannot be used as an argument of an aggregate function in the select-list.

I wish the DB2 UDB had an easier way of retrieving a unique record ID in sql.

Get Last Identity Id

IDENTITY_VALUE_LOCAL() – will return the last id generated by the identity column in a job

--Get the last id generated for this job
VALUES IDENTITY_VALUE_LOCAL() INTO :MYVARIABLE
-- Insert multiple records and get multiple ids back
SELECT ID FROM FINAL TABLE(/*MULTIPLE INSERT STATEMENTS*/)

DB2 Get next identity value

-- Get a good guess of what the next value will be:
SELECT TABLE_SCHEMA, TABLE_NAME, NEXT_IDENTITY_VALUE
FROM QSYS2.SYSPARTITIONSTAT
WHERE TABLE_SCHEMA = 'CUSTOMER';

Alternative Solution: UUID AKA GUID

A universally unique identifier (UUID) is a 128-bit number that is randomly generated so that it is nearly impossible to have a duplicate created with even hundreds of trillions of records your chance of a duplicate is 1 in a billion chance of a duplicate – https://en.wikipedia.org/wiki/Universally_unique_identifier In this technique the application can create the UUID and then save it to the database and not rely on the database to generate the unique identifier. For LARGE datasets that you need to sort or compare guids (used in a where clause, or a join, or an db index etc….) there will be a performance hit due to the randomness of the data and the value being a 128 bit value instead of a 32 bit value if you were to use an integer. Its easier to replicate a row that has a UUID since your not bound to the database handing out IDs. Another advantage is that the UUID is so unique that it could be used across multiple applications. One must weigh the pros and cons of both approaches and analyze the system they are building to see what is the best fit for their solution.

Advertisements