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

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

P: n/a
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
Share this Question
Share on Google+
4 Replies


P: n/a
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_local();

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.SYNCIDENTITY(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.SYSSEQUENCES.LASTASSIGNEDVAL
The follwing view may lead you how to correlate the table to the sequence.
create view syscat.colidentattributes (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.syscolumns as c,
sysibm.sysdependenci
es as d, sysibm.syssequences 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

P: n/a

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

P: n/a
Hi Serge:

With CACHE = 20, if we query SYSIBM.SYSSEQUENCES, 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

P: n/a
UDBDBA wrote:
Hi Serge:

With CACHE = 20, if we query SYSIBM.SYSSEQUENCES, 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 discussion thread is closed

Replies have been disabled for this discussion.