473,543 Members | 3,906 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

UDb V8.FP6 - Finding NEXTVAL for IDENTITY COLUMN (not SEQUENCE)

Hi:

we have column with GENERATED ALWAYS AS DEFAULT. So, we can insert into
this column manually and also let db2 generate a value for this column.

Given a scenario, how can i find the NEXTVAL for this identity column?

We can do "VALUED NEXTVAL FOR SEQUENCE", but for identity how can it be
done?

Thanks.

Vijay

Nov 12 '05 #1
4 9572
UDBDBA wrote:
we have column with GENERATED ALWAYS AS DEFAULT. So, we can insert into
this column manually and also let db2 generate a value for this column.

Given a scenario, how can i find the NEXTVAL for this identity column?

We can do "VALUED NEXTVAL FOR SEQUENCE", but for identity how can it be
done?

Well, when you do NEXT VALUE FOR s1 you are more than doing a peek, you
actually consume the value. So the nextvalue will be a value after that ...
The only way I can think of to get similar function is this:
INSERT INTO T(id) VALUES(DEFAULT) ;
ROLLBACK;
VALUES identity_val_lo cal();

or (FP4 and higher):
SELECT id INTO :myid FROM NEW TABLE(INSERT INTO T(id) VALUES(DEFAULT) );
ROLLBACK;

If all you want to know is where the high water mark is (e.g. to then do
a RESTART) take a look at the proc I posted not too long ago here:

CREATE PROCEDURE GOODSTUFF.SYNCI DENTITY(IN schemaname VARCHAR(128),
IN tablename VARCHAR(128))
BEGIN
DECLARE sqlcode INTEGER;
DECLARE maxid BIGINT;
DECLARE idcolname VARCHAR(128);
DECLARE stmttxt VARCHAR(1000);
DECLARE s STATEMENT;
DECLARE cur CURSOR FOR s;

SELECT colname INTO idcolname
FROM SYSCAT.COLUMNS
WHERE tabname = tablename
AND tabschema = schemaname
AND identity = 'Y';
IF SQLCODE = 100 THEN
SIGNAL SQLSTATE '78000'
SET MESSAGE_TEXT = 'can''t find identity column';
END IF;
SET stmttxt = 'SELECT MAX("' || idcolname || '") FROM "' ||
schemaname || '"."' || tablename || '"';
PREPARE s FROM stmttxt;
SET maxid = 0;
OPEN cur;
FETCH cur INTO maxid;
CLOSE cur;
SET stmttxt = 'ALTER TABLE "' || schemaname || '"."'
|| tablename || '" ALTER COLUMN "' || idcolname ||
'" RESTART WITH ' || CHAR(maxid + 1);
EXECUTE IMMEDIATE stmttxt;
COMMIT;
END
$
If you want to know what the system will start assigning soon (I.e. the
last cached value) you need SYSIBM.SYSSEQUE NCES.LASTASSIGN EDVAL
The follwing view may lead you how to correlate the table to the sequence.
create view syscat.colident attributes (tabschema, tabname, colname,
start,
increment, minvalue, maxvalue, cycle, cache, seqid) as select
c.tbc
reator, c.tbname, c.name, s.start, s.increment, s.minvalue,
s.maxvalue, s.
cycle, s.cache, s.seqid from sysibm.syscolum ns as c,
sysibm.sysdepen denci
es as d, sysibm.sysseque nces as s where c.tbcreator = d.dschema and
c.tbname = d.dname and d.bname = s.seqname and
d.bschema = s.seq
schema and c.identity = 'Y' and d.dtype = 'T' and
d.btype =
'Q' and s.seqtype = 'I'
Note that the SYSIBM.* tables are not docuemented. Their definitions may
change on release boundaries.

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

Well,

just to know the value after inserting I think IBM introduced the
Select over Insert, Update, Delete
statement in V8 (at least 8.2) if I remember correctly.

Then you can use the inserted value for other purposes as well.
This avoids using a sequence just because you need to know which value your
ID has to insert it somewhere else as well. Furthermore, even with sequences
it avoids some locking.

Juliane

UDBDBA wrote:
Hi:

we have column with GENERATED ALWAYS AS DEFAULT. So, we can insert into
this column manually and also let db2 generate a value for this column.

Given a scenario, how can i find the NEXTVAL for this identity column?

We can do "VALUED NEXTVAL FOR SEQUENCE", but for identity how can it be
done?

Thanks.

Vijay

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200507/1
Nov 12 '05 #3
Hi Serge:

With CACHE = 20, if we query SYSIBM.SYSSEQUE NCES, it will show 20 till
you cross 20, then it would show 40.

So, i cannot use this column to find the next value to assign for the
identity column.

The column name is misleading, instead of LASTASSIGNEDVAL it should be
LASTCACHEDVAL.. .

So, to get the functionality CACHE should be turned OFF.

Vijay

Nov 12 '05 #4
UDBDBA wrote:
Hi Serge:

With CACHE = 20, if we query SYSIBM.SYSSEQUE NCES, it will show 20 till
you cross 20, then it would show 40.

So, i cannot use this column to find the next value to assign for the
identity column.

The column name is misleading, instead of LASTASSIGNEDVAL it should be
LASTCACHEDVAL.. .

So, to get the functionality CACHE should be turned OFF.

Vijay

Vijay,

Have you considered switching to using a sequence instead?
A simple BEFORE INSERT TRIGGER will preserve the semantics for your apps.

Cheers
Serge

PS: You'd have to watch out for LOAD since load doesn't fire triggers.

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
2035
by: Aaron W. West | last post by:
Timings... sometimes there are almost too many ways to do the same thing. The only significant findings I see from all the below timings is: 1) Integer math is generally fastest, naturally. Bigint math isn't much slower, for integers that all fit within an integer. 2) Converting float to varchar is relatively slow, and should be avoided...
0
1285
by: Lee Harr | last post by:
I am used to creating sequences separately and using them for default values instead of using the serial type. I am not sure why... but that's the way I have been doing it. Maybe I don't trust the generated names. I should probably just get over it, but anyhow... Let's say I am writing a file for creating a database. Say ... -- schematest...
2
1459
by: edurne murgiondo via DBMonster.com | last post by:
I have a table with a column as identity. I don't know how can migrate a identity column to a normal column using sequence. Thnaks -- Message posted via http://www.dbmonster.com
7
2165
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++;
4
7260
by: shorti | last post by:
Can anyone explain in greater (and more comprehensive) detail what the RESTART option does in the ALTER TABLE table ALTER COLUMN statement. This is the description in Info Center: RESTART or RESTART WITH numeric-constant Resets the state of the sequence associated with the identity column. If WITH numeric-constant is not specified, the...
1
21031
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...
7
17348
by: Rahul B | last post by:
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
0
3011
by: Frank Swarbrick | last post by:
So we're trying to decide if it's better to use IDENTITY columns or sequences to create a surrogate key as the primary key for our tables. I kind of like the identity column, because it's more 'tightly integrated' in to the table. With a sequence you have to make sure that each application that inserts records uses the same sequence. ...
8
11218
by: arachno | last post by:
My Oracle sequences seem to be auto-incrementing themselves "over time". My row ID's are sequenced like this: 1, 4, 5, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 41, etc I'm using "sequencename.nextval" when inserting new rows into my tables, so one would think they'd come in a straight sequence? Example: INSERT INTO hprequests...
0
7402
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...
0
7590
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. ...
0
7733
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...
0
5883
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...
1
5264
isladogs
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...
0
4890
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3391
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1814
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
1
963
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.