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 

Answer 1

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

Answer 2

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)