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
