By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,984 Members | 1,016 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,984 IT Pros & Developers. It's quick & easy.

sequences auto-increment

P: n/a
GB
Hi,

Is there a way to get the next sequence number which will be used ?
Where is this information stored ?

I know how to set it (ALTER TABLE xyz ALTER abc RESTART WITH 123), and I
know I could do an insert to see the value used but there should be some
other way to lookup this...

Thanks for your help, Greg
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
No can do - no peeking. It would violate the very idea of sequences.
The idea behind sequences is that there is no transactional locking.
If DB2 told you which sequence values you "would" get next, without
actually giving it to you, then a lock would be needed to keep this
promise (or else what good would it do to you). At that point we can
just as well go back to the good old look-up table.

However, your post implies that you may or may not be aware that getting
the next value does not mean you have to use it in an insert right away.

You can get the next value. Do your own business and use the value any
which way you please or throw it away alltogether.

CREATE SEQENCE s1;

BEGIN ATOMIC
DECLARE nv INT;
SET nv = NEXT VALUE FOR s1;
VALUES do_something_foo();
INSERT INTO T VALUES nv;
END

If what you need is a high watermark then NEXT VALUE will not be usefull
either. In a DB2 + DPF environment your app will break unless you turn
of the CACHE. Just teh same as you can not look into your checkbook to
find the latest check written if you share yoru account with your partner.
In these cases you can consult with SYSIBM.SYSSEQUENCES. There is one
column holding the next batch to be handed out.

Hope that helps
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.