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

prevval and nextval for a sequence

P: n/a
Hi,

If i do a "Select nextval for <seq_namefrom sysibm.sysdummy1",
it increases the values of nextval by1.

How can i find out the nextval of a sequence without actually
increasing the value.

The statement "Select prevval for <seq_namefrom sysibm.sysdummy1"
gives the value of previous correctly generated value only if the
nextval was done in that session.
else it gives the error "SQL0845N A PREVVAL expression cannot be used
before the NEXTVAL expression generates a value in the current session
for sequence "SEQID = 449"

I tried to find out the value through the syscat.sequences.
However, it only gives the 'nextcachefirstvalue' , that means i can
know the value will be between 'nextcachefirstvalue' and
'nextcachefirstvalue - cache' for that sequence.

But it does not give me the exact current value.

Is there some way to find out the current value of the sequence from
the syscat tables or by making some simple query(even after creating a
new session) in a way that the sequence value is not increased.

Rahul

Sep 5 '07 #1
Share this Question
Share on Google+
7 Replies


P: n/a
Rahul B wrote:
Is there some way to find out the current value of the sequence from
the syscat tables or by making some simple query(even after creating a
new session) in a way that the sequence value is not increased.
No. Why would you want to do that? If you were to see the current value
it would be meaningless the moment you see it because there is no
locking of the value. Any other connection can chew up the next 1000
values before you have reacted to your obsolete information....

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Sep 5 '07 #2

P: n/a
If yoy use NO CACHE and look at the high watermark that would be that
number. I.e. th next one coming up.
Depending on your acceptable level of fuzziness you can dial cache in as
you wish.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Sep 6 '07 #3

P: n/a
On Sep 6, 10:23 pm, Serge Rielau <srie...@ca.ibm.comwrote:
If yoy use NO CACHE and look at the high watermark that would be that
number. I.e. th next one coming up.
Depending on your acceptable level of fuzziness you can dial cache in as
you wish.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Yes,

That could be done, however if i am not wrong, putting NO CACHE in
sequence is bound to decrease the performance.

Rahul

Sep 7 '07 #4

P: n/a
Rahul B wrote:
That could be done, however if i am not wrong, putting NO CACHE in
sequence is bound to decrease the performance.
"Wo gehobelt wird da fallen Spaene"
Or in English:
You can't have your cake and eat it to....

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Sep 7 '07 #5

P: n/a
On Sep 7, 4:30 pm, Serge Rielau <srie...@ca.ibm.comwrote:
Rahul B wrote:
That could be done, however if i am not wrong, putting NO CACHE in
sequence is bound to decrease the performance.

"Wo gehobelt wird da fallen Spaene"
Or in English:
You can't have your cake and eat it to....

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Serge,

I am sure sometimes "You can have your cake and eat that too".
I am perfectly fine if the records generated have gaps in ids.
Only thing is how does a sequence know which value it has to generate
next. It should be stored somewhere, right?

Rahul

Sep 7 '07 #6

P: n/a
On Sep 7, 5:12 pm, Rahul B <rahul.babb...@gmail.comwrote:
On Sep 7, 4:30 pm, Serge Rielau <srie...@ca.ibm.comwrote:
Rahul B wrote:
That could be done, however if i am not wrong, putting NO CACHE in
sequence is bound to decrease the performance.
"Wo gehobelt wird da fallen Spaene"
Or in English:
You can't have your cake and eat it to....
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Serge,

I am sure sometimes "You can have your cake and eat that too".
I am perfectly fine if the records generated have gaps in ids.
Only thing is how does a sequence know which value it has to generate
next. It should be stored somewhere, right?

Rahul
Anyways,

Thanks for such a good discussion.
I am sure i would have learnt a lot less in DB2 if i had not joined
this group.
This group ROCKS!

Thanks

Rahul

Sep 7 '07 #7

P: n/a
Rahul B wrote:
I am sure sometimes "You can have your cake and eat that too".
I am perfectly fine if the records generated have gaps in ids.
Only thing is how does a sequence know which value it has to generate
next. It should be stored somewhere, right
As I said. Take the high watermark from SYSCAT.SEQUENCES.NEXTCACHEFIRSTVALUE

If you can have gaps then why don't you just do a NEXT VALUE and be done
with it? It's not that numbers are in short supply.

You CANNOT today get the next value the system will issue. I know that
because half of the code and most of the spec was written by me.

You are free to raise a PMR on the topic with support, but unless there
is significant $$ on the table I don't expect you will get this function
added because it is counter productive for the usage of sequences.
You are trying to bend sequences to your idea of how they should work.
That is not how it works. They are an SQL Standard feature.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Sep 7 '07 #8

This discussion thread is closed

Replies have been disabled for this discussion.