Create primary keys from one shared sequence in DB2

Question

I would like to use single sequence for all primary keys in DB2 (10.5 LUW).

However my blind eyes have not managed to find a way to do this.. Closest I can get is to use a function, but it fails when multiple values are inserted in a same statement. This is because the function is evaluated only once. Could tweak this by adding a dependency from column values but do not like to do that.

The question relates to my previous one

CREATE  SEQUENCE TEST.ID_SEQ AS BIGINT START WITH 10000 INCREMENT BY 1 NO MAXVALUE NO CYCLE CACHE 100 
GO
--- Bad "hack" since the function is not deterministic
--- see comments below
CREATE OR REPLACE FUNCTION TEST.OBJECT_ID( )
  RETURNS BIGINT SPECIFIC TEST.OBJECT_ID
  CONTAINS SQL DETERMINISTIC NO EXTERNAL ACTION 
  BEGIN
     DECLARE B BIGINT;
     SET B = NEXT VALUE FOR TEST.ID_SEQ ;
     RETURN B ;
  END
GO

CREATE TABLE TEST.TEST (
  ID  BIGINT     NOT NULL PRIMARY KEY GENERATED ALWAYS  AS (TEST.OBJECT_ID()),
  TEST VARCHAR(10)
)
GO

Why do you want to do that? I mean having a single Sequence for all primary keys does not look like a good idea to me as it will be a hot spot. — MichaelTiefenbacher, Oct 08 '16 at 08:34
I would like to have a single unique id for all entities. Also in this way developers can never insert wrong foreign keys (other table's pk) into a tables since the keys exist only once. — JMX, Oct 08 '16 at 09:50
Firstly, your function is _not_ deterministic, because it can never return the same value. But that's beside the point -- your entire approach seems wrong. — mustaccio, Oct 08 '16 at 14:47
Thanks for this! Did not notice that... just copy pasted the code. Likely that is the reason. — JMX, Oct 08 '16 at 15:28
@mustaccio Seems the *answer* to the issue, despite any reservations about the *approach*, is almost as simple as `DETERMINISTIC` is the improper specification for reference to a value from a SEQUENCE; i.e. coding `NOT DETERMINSTIC` is required. Although possibly there is an additional restriction, such that `DISALLOW PARALLEL` must also be specified for proper effect? — CRPence, Oct 08 '16 at 15:54
Just to add that functions used in the generated statements must be deterministic. (which is wrong in the case..). ...No way to get the key from shared sequence ? — JMX, Oct 08 '16 at 16:12
That's what I get for not first reading about the *generated column*, of which I was not even aware; I can see why DETERMINSTIC is a requirement, for what they [intend to] provide. I mistook the [unfamiliar] syntax, as a means to do exactly what was expressed as-desired; i.e. to provide for an alternative means to establish what would otherwise be DB-generated IDENTITY values. Anyhow, a BEFORE INSERT trigger to both add the NEXT VALUE to the parent and set that same value for the effective-identity column in the child TABLE [on which the TRIGGER is defined] should accomplish the goal.? — CRPence, Oct 09 '16 at 18:29
It is rather annoying that these "generated as" statements cannot be used either in update statements properly. E.g. in a case (see the link) I wanted to generate usestamp automatically for updated rows... no way to do that it seems. Only timestamps can be generated. — JMX, Oct 11 '16 at 15:29

score 1 · Answer 1 · answered Oct 09 '16 at 18:57

A script that gives an example of what might suffice for having one TABLE as a parent for multiple TABLEs [this shows two child tables, each with the identical INSERT TRIGGER], across which each will have the next sequence value assigned from just the one, thus shared SEQUENCE. The parent will of course get every sequence value, and each child will get only values that are not in any other child TABLE:

drop table AppFile01 ;
drop table AppFile02 ;
drop table Master_IDENTITY ;   
drop sequence      ID_SEQ ;    

create table Master_IDENTITY 
( Master_ID    BIGINT NOT NULL 
, constraint Master_IDENTITY_PK 
    PRIMARY KEY ( Master_ID )  
) 
; 
CREATE  SEQUENCE      ID_SEQ AS BIGINT
   START WITH 10000 INCREMENT BY 1    
   NO MAXVALUE NO CYCLE CACHE 100     
; 
create table AppFile01 
( sKeyAF      BIGINT     not null      
, ts          timestamp  default current timestamp
, constraint AppFile01_PK     
    PRIMARY KEY ( sKeyAF )    
, constraint AppFile01_FK     
    FOREIGN KEY ( sKeyAF )    
    REFERENCES  Master_IDENTITY ( Master_ID )
    ON DELETE CASCADE  ON UPDATE NO ACTION 
)  
;  
create trigger AppFile01_BI BEFORE INSERT  
 on AppFile01      
referencing new as N   
for each row mode db2sql      
begin     
 declare NxtSeq BIGINT ; 
 set NxtSeq = NEXT VALUE FOR    ID_SEQ ;   
 insert into Master_IDENTITY values ( NxtSeq ) ;  
 set N.sKeyAF = NxtSeq ; 
end       
;  
create table AppFile02 
( sKeyAF      BIGINT     not null      
, ts          timestamp  default current timestamp
, constraint AppFile02_PK     
    PRIMARY KEY ( sKeyAF )    
, constraint AppFile02_FK     
    FOREIGN KEY ( sKeyAF )    
    REFERENCES  Master_IDENTITY ( Master_ID )
    ON DELETE CASCADE  ON UPDATE NO ACTION 
)  
;  
create trigger AppFile02_BI BEFORE INSERT  
 on AppFile02      
referencing new as N   
for each row mode db2sql
begin     
 declare NxtSeq BIGINT ; 
 set NxtSeq = NEXT VALUE FOR    ID_SEQ ;   
 insert into Master_IDENTITY values ( NxtSeq ) ;  
 set N.sKeyAF = NxtSeq ; 
end       
;

And the following requests both test and show the effect; there is no particular reason I chose to use the literal value -1 over, for example, DEFAULT:

insert into  AppFile01 values( -1, DEFAULT ) ;
; -- gets value 10000     
insert into  AppFile02 values( -1, DEFAULT )  
       , ( -1, DEFAULT )  
; -- gets values 10001 and 10002   
insert into  AppFile01 values( -1, DEFAULT ) ;
; -- gets value 10003     
select * from master_id
; -- likeness of report from above query  
          MASTER_ID
             10,000
             10,001
             10,002
             10,003
select * from appfile01
; -- likeness of report from above query  
             SKEYAF   TS                        
             10,000   2016-10-09-18.38.28.347468
             10,003   2016-10-09-18.38.28.400702
select * from appfile02
; -- likeness of report from above query  
             SKEYAF   TS                        
             10,001   2016-10-09-18.38.28.372121
             10,002   2016-10-09-18.38.28.386622

Thanks @CRPence for the solution! I was hoping to do this without triggers but likely no other options... — JMX, Oct 11 '16 at 15:20
Ah. I saw that noted in the linked topic, but there was no mention for this topic, of wanting to avoid triggers. Clearly the attempted approach was one that was non-trigger :-) but that seems likely to be a dead-end.? — CRPence, Oct 11 '16 at 15:41

Esperento57 · Answer 2 · 2016-10-08T10:59:12.773

0

You can use autoincrement

 create table TEST.TEST  (
 id integer not null GENERATED ALWAYS AS IDENTITY (START WITH 10000 INCREMENT BY 1)
 ,TEST varchar(30)
 ,PRIMARY KEY (id)
 );

or use insert like this

 INSERT INTO ORDERS (ORDERNO, CUSTNO) VALUES (NEXT VALUE FOR ORDER_SEQ, 12)

edited Oct 08 '16 at 10:59

answered Oct 08 '16 at 09:12

Esperento57

16,521
3
39
45

Thanks for the reply. I would actually like to get all ids from same sequence so that primary keys are unique over the tables. No same pk exist in other table. – JMX Oct 08 '16 at 09:44
I agree MichaelTiefenbacher, that'is not a good idee. You say "Also in this way developers can never insert wrong foreign keys (other table's pk) into a tables since the keys exist only once", but if you create autoincrement on all your primary keys you will not have this problem, This is the system that will manage the key – Esperento57 Oct 08 '16 at 10:11
Yes they can because tables have same running numbers, starting from 10000 in this case .... Ids are unique of course within single table but not unique over the whole implementation (rare case, but rather easy to use pk taken from wrong table ). Also getting the id from cached sequence is rather fast operation. The hot spot is not an issue in my case. – JMX Oct 08 '16 at 10:33
I dont think you can do it like this :( you can only force your developpers to use storage procedure if you use grant access on your tables and storage procedure. Then you can control that. Else your developpers must use this syntax INSERT INTO ORDERS (ORDERNO, CUSTNO) VALUES (NEXT VALUE FOR ORDER_SEQ, 12). But i dont understamp why you want do it (sorry my English is bad) :) – Esperento57 Oct 08 '16 at 10:47
Thanks Esperento57. Your english is very understandable. Looks like your proposal is the way to go with the DB2... In Oracle "shared sequences" work fine. – JMX Oct 08 '16 at 10:56
I have modify my response for integrate that proposition – Esperento57 Oct 08 '16 at 11:00

Create primary keys from one shared sequence in DB2

2 Answers2