Sequences (ORACLE) - Identities (SQLServer) 
Author Message
 Sequences (ORACLE) - Identities (SQLServer)
We've got sequences in Oracle and Identity columns in SQLServer,  is there
a functional equivalent in Db2 Version 6.1?

Thanks,

-----------------
GWA
-----------------



Sat, 07 Sep 2002 03:00:00 GMT
 Sequences (ORACLE) - Identities (SQLServer)

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) ...);

Quote:

> We've got sequences in Oracle and Identity columns in SQLServer,  is there
> a functional equivalent in Db2 Version 6.1?

> Thanks,

> -----------------
> GWA
> -----------------



Sat, 07 Sep 2002 03:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. MSSQL IDENTITY and ORACLE SEQUENCE

2. SQLServer equivalent to Oracle Sequence Object

3. SQLServer equivalent of Oracle Sequence

4. Identity and sequence

5. Identity & Sequence

6. Auto generation of sequence (Identity) numbers

7. Sequences(Identity) with dual keys

8. Identity and sequence

9. Sequence Number (Identity Won't Work) And Locking

10. Identity and sequence

11. SQL Identity out of sequence

12. Sequence generation in sqlserver


 
Powered by phpBB® Forum Software