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
7  17375 
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
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
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
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
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
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
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"
|
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++;
|
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);
|
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
|
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%...
| | |
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
$$
|
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:
|
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,...
|
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?
|
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...
|
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,...
| | |
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...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
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...
|
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...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| | |
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...
| |
