Newbe question on sequences 
Author Message
 Newbe question on sequences

Can anybody please give me the low-down on using sequences to generate primary keys?  I am able to create a sequence using a command like:

create sequence dropme start with 1 increment by 1;

but that is about it.  I cannot drop the sequence, get current value, or get next value.  I have a requirement to allow a program to get a unique key value upon insert, then use that same key value to do a number of other operations.  This rules out identity columns unless you know an good way to determine current value after an insert from an identity column given that the table can be updated from multiple sources.



Mon, 16 Aug 2004 05:49:32 GMT
 Newbe question on sequences

Rob,

This is covered in the PDF versions of the manuals, as well as the
RELEASE.TXT file for Fixpak 3.  Unfortunately, the HTML files haven't
been updated yet.  Also, this has been covered multiple times in this
newsgroup; a google search will turn up a lot of postings about both.

Basically - there are two ways to get the sequence values:

   NEXTVAL FOR sequence-name
     (returns the next value for the sequence)
   PREVVAL FOR sequence-name
     (returns most recently generated value within the current
application).

To use an identity column, use the function

   IDENTITY_VAL_LOCAL()

This rerns the latest value for an INSERT in this application (before
Fixpak 4 this was reset by a COMMIT).

Quote:

> Can anybody please give me the low-down on using sequences to generate primary keys?  I am able to create a sequence using a command like:

> create sequence dropme start with 1 increment by 1;

> but that is about it.  I cannot drop the sequence, get current value, or get next value.  I have a requirement to allow a program to get a unique key value upon insert, then use that same key value to do a number of other operations.  This rules out identity columns unless you know an good way to determine current value after an insert from an identity column given that the table can be updated from multiple sources.

--
====================================
To reply, delete the 'x' from my email

Jerry Stuckle
JDS Computer Training Corp.

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



Mon, 16 Aug 2004 06:23:41 GMT
 Newbe question on sequences
To drop a sequence use:
drop sequence <seq> RESTRICT

I remember there was an issue with the RESTRICT not properly documented in the V7.2 docs...

To reset use:
ALTER SEQUENCE <seq> RESTART [WITH <somenumber>]

Please note that PREVVAL FOR <seq> is different from Oracle <seq>.CURRVAL.
It ALWAYS refers to the last sequencenumber generated in a previous statement, independent of the presence of NEXTVAL.
NEXTVAL FOR <seq> matches Oracle <seq>.NEXTVAL behaviour.

Cheers
Serge
--
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Canada



Mon, 16 Aug 2004 22:59:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Question regarding single sequence vs multiple sequences for surrogate key population

2. Question regarding single sequence vs multiple sequences for surrogate key population

3. Newbe question about database designer

4. Newbe Question: Connecting to SQL With Access 2000...

5. Newbe.. Split question

6. SQL Satement and Unicode (Newbe question)

7. Newbe Strored Procedure Question

8. Newbe Question

9. Newbe Question

10. Newbe Question 2

11. Newbe question

12. Give this newbe easy answers on easy questions!


 
Powered by phpBB® Forum Software