Thanks 
Author Message
 Thanks

Thanks, I found it tucked away.  It isn't as straight forward as Oracle, though.  I have to do this to make it work.

DROP SEQUENCE dropme RESTRICT;
create sequence dropme start with 1 increment by 1;

DROP TABLE dropmetab;
create table dropmetab (seqid integer);

insert into dropmetab ((VALUES NEXTVAL for dropme));

select (values prevval for dropme) FROM address;

Quote:
-----Original Message-----

Posted At: Wednesday, February 27, 2002 5:24 PM
Posted To: ibm-db2
Conversation: Newbe question on sequences
Subject: Re: 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).


> 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:28:25 GMT
 Thanks

Hi,

insert into dropmetab values NEXTVAL for dropme;
VALUES prevval for dropme;

I'm not quite sure why this isn't straight forward.
You have to type "NEXTVAL FOR s1" instead of "s1.NEXTVAL" for good reason.

Try this:
CREATE SEQUENCE T;
CREATE TABLE T(NEXT INT);

Oracle:
SELECT T.NEXTVAL, T.NEXTVAL FROM T;

DB2:
SELECT NEXTVAL FOR T, T.NEXTVAL FROM T;

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



Mon, 16 Aug 2004 23:08:06 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. thank you, thank you!

2. Book Help--Thank You, Thank You!

3. Thank you Mr. Alcabano. That was too easy

4. is there a database solution for this ? thanks

5. Many Thanks :o)

6. BCP error, need help - Thanks

7. Thanks for Advice

8. Crosstab Query Question -- Thanks

9. Need an opinion - thanks in advance

10. thanks

11. Thank you!

12. Thanks for the update


 
Powered by phpBB® Forum Software