473,902 Members | 3,601 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

prevval and nextval for a sequence

Hi,

If i do a "Select nextval for <seq_namefrom sysibm.sysdummy 1",
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.sysdummy 1"
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.sequence s.
However, it only gives the 'nextcachefirst value' , that means i can
know the value will be between 'nextcachefirst value' and
'nextcachefirst value - 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
7 17382
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
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
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
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
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
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
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.SEQUENCE S.NEXTCACHEFIRS TVALUE

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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
2336
by: Hemant Shah | last post by:
Folks, I am having problem running select query from a sequence. I get SQL0204N error. I am running UDB 8.2 on AIX 5.2. # oslevel 5.2.0.0 # db2level DB21085I Instance "dba2test" uses "32" bits and DB2 code release "SQL08021"
7
2199
by: pfa | last post by:
I have a udf which returns a table. I was hoping to access the NEXTVAL from a sequence to affect the logic flow and consequently the return result. I'm using a LANGUAGE C type function. Here's the fetch snippet case SQLUDF_TF_FETCH: /* fetch next row */ { char * nextid = myrecids++;
2
3017
by: dan | last post by:
I have 2 tables, tab1 ( integer incremented sequence , col2, col3 ) and tab2 ( integer from tab1, col4, col5 ). When I call this function to add a record to each table: LOOP select nextval('sequence') into id_car; // for looping INSERT INTO tab1 VALUES (default, col2, col3);
2
2783
by: Lee Harr | last post by:
I must have a fundamental misunderstanding about using schema. Before using schema, I usually have a file that has my database definition, and I can play that file back in to a new database to create a testing area or to create my production setup. I think I want to use schema the same way. My problem is using a sequence for a default value. I know that if I use a serial instead, all of this will go away, but my design
2
1682
by: Oscar Tuscon | last post by:
I'm looking at ways to get batches of sequence values s faster. I don't want to set cache or increment to a large number for a variety of reasons. I need to grab id's in batches of varying numbers at various times, from 1 to several thousand at once. Doing it 1 at a time works, but more time goes into it than I'd like. I tried setting cache_value high but the database roundtrips were eating more time that I wanted to see; I only saw a 25%...
4
4509
by: Sim Zacks | last post by:
I am in the process of converting an existing database to PostGreSQL and wrote a generic script to update all of the sequences as they default at 1. I thought it would be useful to other people who are converting their databases. If anyone can write this script in using plpythonu, I would love to see how it is done. create or replace function UpdateSequences() returns varchar(50) as $$
7
3621
by: urban.widmark | last post by:
Hello We are having some problems with triggers, sequences and union all in V8 on code that worked fine in V7. Was wondering if someone else has seen this and/or knows what to do. A trigger that runs after insert on, where the insert uses nextval on a sequence for the key and the trigger uses union all we get this message:
1
21079
by: filip1150 | last post by:
I'm trying to find if there is any performance diference between explicitly using a sequence in the insert statement to generate values for a column and doing this in an insert trigger. I noticed that th eaccess plan for the 2 situations is quite different. For the case where the trigger is in place, the optimizer applies 2 extra residual predicates. Can anybody explain where the differences come from? Also, for real life situations,...
0
1549
by: Kat Choi | last post by:
create sequence A insert into T1(a,b) values(nextval for A, "data"); insert into T2(a,b) values(prevval for A, "data); I am running a multiple threads java application. The first field of table T1 and T2 should be the same if nobody issue a NEXTVAL for A in between. My question is how can I ensure nobody(other threads, or other DB2 user) can issue a 'nextval for A' in between my two insert statements?
0
9997
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9845
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11277
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10866
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10497
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9672
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
6084
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4724
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3323
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.