Does DB2 have an equivalent to Oracle sequence?

[jw...@yahoo.com]

Hi,

We are looking for an equivalent to Oracle Sequence. Oracle
sequence is a database-maintained number, it keeps current
value of a number. When you try to get next value, it will
advance current value by 1 (increment value could be specified
in sequence definition). This is very useful for column like
UserId, every time a new row inserted, you want a brand-new
Id (probably with higher value than the largest existing User Id).

MS SQL server has a concept called "identity" which does the
similar thing.

Does DB2 have something like that?

thanks a lot,
Dennis

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.

[Michel MADINIER]

If you are talking about DB2 for OS/390 (MVS), then the answer is no.

What you may do is build a Table that will store the current value for each
of your counters and have your programs increment this number when needed.
Unfortunately, this solution is quite tricky to tune. If these Updates are
very frequent, you will have heavy locking problems, so you might want to
investigate other solutions.

Some questions you might ask are : do you tolerate holes within the sequence
number (if yes you may stick to the above mentionned solution and ask the
program to Commit as soon as the increment Update is performed, in order to
release Locks as promptly as possible), if the idea is to provide a unique
Key value, you might want to consider using a Timestamp column.

DB2 (UDB for OS/390) Version 6 will provide a ROWID data type that will give
your programs a completely random value, you might want to investigate this
also.

Regards,

Michel.

<jw...@yahoo.com> a écrit dans le message : 7nvujo$rok$1...@nnrp1.deja.com...

[jw...@yahoo.com]

IBM folks gave me a white paper on how to deal with sequence.
The solution they recommended is to have a trigger before
insert which increments the maximum existing values and
insert into the ID field. If a transaction inserts
multiple rows, a UTF using scapepad need to be defined
to support the trigger.

Also, IBM folks said DB2 6.1 for UNIX and NT do not
have ROWID feature.

Regards,
Dennis

In article <u5Vo3.90$8k6.4...@nnrp2.proxad.net>,

[Blair Kenneth Adamache]

Not yet. There are ways to do it with triggers and functions:
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).

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

b. DB2 Universal Database Version 5 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.