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

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

UDBDBA wrote:[color=blue]
> 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?[/color]
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

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

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:[color=blue]
>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[/color]


--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200507/1

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

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

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

UDBDBA wrote:[color=blue]
> 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
>[/color]
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