Primary Key/Sequence Generation 
Author Message
 Primary Key/Sequence Generation
Does DB2 contain a built-in equivalent to Oracle's 'sequence generator'
used in generating primary keys for database tables.  For example, if I
had a table 'X' in Oracle I would set up a 'sequence' called 'seq_X' and
call seq_X.nextval each time I was inserting into table X to obtain a
primary key for the record.  Thanks in advance.

Mark Wood



Fri, 23 Aug 2002 03:00:00 GMT
 Primary Key/Sequence Generation

We have nothing like this until v7, when we introduce an IDENTITY data type.
Quote:

> Does DB2 contain a built-in equivalent to Oracle's 'sequence generator'
> used in generating primary keys for database tables.  For example, if I
> had a table 'X' in Oracle I would set up a 'sequence' called 'seq_X' and
> call seq_X.nextval each time I was inserting into table X to obtain a
> primary key for the record.  Thanks in advance.

> Mark Wood




Fri, 23 Aug 2002 03:00:00 GMT
 Primary Key/Sequence Generation
this question takes the prize for the most frequently asked!! It pops up
about every week. Here is a previous response I have pasted in...

here you go. this is from Blair. you can find previous posts by searching
archives in deja.com

=====================

The IDENTITY data type is being added in Version 7.
Until then, here are four workarounds:

First, if the column is a primary key, define it as not null with
default (the trigger will always overwrite the default value, but this
will allow rows to be inserted without a dummy value for the column).

Thanks to Joe Celko for suggestions to improve the examples below.

a. CREATE TRIGGER AutoIncrement NO CASCADE BEFORE
       INSERT ON Foobar
       REFERENCING NEW AS n
       FOR EACH ROW MODE DB2SQL
       SET (n.col1) = (SELECT COALESCE(MAX(col1),0) + 1 FROM Foobar)

b. DB2 Universal Database also has a function called
   GENERATE_UNIQUE:

Quote:
>>-GENERATE_UNIQUE--(--)---------------------------------------><

The GENERATE_UNIQUE function returns a bit data character string 13
bytes long (CHAR(13) FOR BIT DATA) that is unique compared to any
other execution of the same function.

c. for a trigger that handle multi-row inserts:

Look in the samples section  for the sample function called db2Udf.
In it you will find a method call ctr.

Load the function as follows:

CREATE FUNCTION NULLID.COUNTER() RETURNS INT EXTERNAL NAME
'DB2Udf!ctr' LANGUAGE JAVA PARAMETER STYLE DB2GENERAL NO SQL NOT
DETERMINISTIC NOT FENCED SCRATCHPAD NO EXTERNAL ACTION
;

Then do your insert with a trigger of (this is mine for my ADDRESSES
Tabl
(adjust to fit)):

CREATE TRIGGER NULLID.ADDRESSAUTOINC NO CASCADE BEFORE INSERT ON
NULLID.ADDRESSES REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL BEGIN
ATOMIC
SET (n.ADDR_UID ) = (SELECT value(MAX(ADDR_UID),0) +NULLID.COUNTER()
FROM
NULLID.ADDRESSES);  END

Then do your insert.

d. Use a scalar subquery expression:

INSERT INTO Foobar (key_col, ...)
VALUES (COALESCE((SELECT MAX(key_col) FROM Foobar) +1, 0) ...);

Phil Castle


Quote:
> Does DB2 contain a built-in equivalent to Oracle's 'sequence generator'
> used in generating primary keys for database tables.  For example, if I
> had a table 'X' in Oracle I would set up a 'sequence' called 'seq_X' and
> call seq_X.nextval each time I was inserting into table X to obtain a
> primary key for the record.  Thanks in advance.

> Mark Wood




Sat, 24 Aug 2002 03:00:00 GMT
 Primary Key/Sequence Generation
I'm affraid once  DB2 V7 GAs this newgroup will be a lot less active,
no more identity issues *sniff**S*

Cheers
Serge

Quote:

> this question takes the prize for the most frequently asked!! It pops up
> about every week. Here is a previous response I have pasted in...

> here you go. this is from Blair. you can find previous posts by searching
> archives in deja.com

> =====================

> The IDENTITY data type is being added in Version 7.
> Until then, here are four workarounds:

> First, if the column is a primary key, define it as not null with
> default (the trigger will always overwrite the default value, but this
> will allow rows to be inserted without a dummy value for the column).

> Thanks to Joe Celko for suggestions to improve the examples below.

> a. CREATE TRIGGER AutoIncrement NO CASCADE BEFORE
>        INSERT ON Foobar
>        REFERENCING NEW AS n
>        FOR EACH ROW MODE DB2SQL
>        SET (n.col1) = (SELECT COALESCE(MAX(col1),0) + 1 FROM Foobar)

> b. DB2 Universal Database also has a function called
>    GENERATE_UNIQUE:
> >>-GENERATE_UNIQUE--(--)---------------------------------------><

> The GENERATE_UNIQUE function returns a bit data character string 13
> bytes long (CHAR(13) FOR BIT DATA) that is unique compared to any
> other execution of the same function.

> c. for a trigger that handle multi-row inserts:

> Look in the samples section  for the sample function called db2Udf.
> In it you will find a method call ctr.

> Load the function as follows:

> CREATE FUNCTION NULLID.COUNTER() RETURNS INT EXTERNAL NAME
> 'DB2Udf!ctr' LANGUAGE JAVA PARAMETER STYLE DB2GENERAL NO SQL NOT
> DETERMINISTIC NOT FENCED SCRATCHPAD NO EXTERNAL ACTION
> ;

> Then do your insert with a trigger of (this is mine for my ADDRESSES
> Tabl
> (adjust to fit)):

> CREATE TRIGGER NULLID.ADDRESSAUTOINC NO CASCADE BEFORE INSERT ON
> NULLID.ADDRESSES REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL BEGIN
> ATOMIC
> SET (n.ADDR_UID ) = (SELECT value(MAX(ADDR_UID),0) +NULLID.COUNTER()
> FROM
> NULLID.ADDRESSES);  END

> Then do your insert.

> d. Use a scalar subquery expression:

> INSERT INTO Foobar (key_col, ...)
> VALUES (COALESCE((SELECT MAX(key_col) FROM Foobar) +1, 0) ...);

> Phil Castle



> > Does DB2 contain a built-in equivalent to Oracle's 'sequence generator'
> > used in generating primary keys for database tables.  For example, if I
> > had a table 'X' in Oracle I would set up a 'sequence' called 'seq_X' and
> > call seq_X.nextval each time I was inserting into table X to obtain a
> > primary key for the record.  Thanks in advance.

> > Mark Wood




Sat, 24 Aug 2002 03:00:00 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Primary key generation using a TRIGGER

2. Primary KEY ID Generation Algorithm

3. Primary key generation

4. Primary key generation

5. HOWTO - Random character generation for primary key

6. HOWTO - Random character generation for primary key

7. HOWTO - Random character generation for primary key

8. primary key generation in EJB bean-managed persistence

9. primary key generation and locking

10. Surrogate Primary Key Generation

11. Automatic generation of primary keys ?

12. How can I automate generation of Primary Keys


 
Powered by phpBB® Forum Software