Hi All,
I created one sequence in DB2 udb. I want to know in which system tables, this information stores. your early information is appreciated.

Thanks in advance.

mary.

SYSIBM.SYSSEQUENCES wil show you a list of sequences and contains the LASTASSIGNEDVAL. However this is really just highest value that has been cached, and not necessarily the highest value actually assigned. So unless teh sequence is defined as NO CACHE, it is not really the last assigned value. Also, the LASTASSIGNEDVAL may not be accurate in a DPF configuration (data partitioning feature) accross multiple nodes.

Note that all SYSIBM tables are undocumented in DB2 LUW and are subject to change at the whim of IBM. That is why only the SYSCAT views (such as SYSCAT.SEQUENCES) are documented (but the view does not have LASTASSIGNEDVAL).

To use the next value for a sequence for an insert statement:

CREATE SEQUENCE EMPSERIAL
AS INTEGER
START WITH 1
INCREMENT BY 1;

INSERT INTO EMPLOYEE ( SERIALNUMBER, FIRSTNAME, LASTNAME,
SALARY) VALUES(NEXTVAL FOR EMPSERIAL, 'John', 'Smith', 75000);


To get the last value for a sequence:
VALUES PREVVAL FOR <seq-name>

However, the PREVVAL command won't give you the correct answer unless you have already accessed (and thus incremented) the sequence in your current session using the NEXTVAL command.

Hi Marcus,
Thanks fo posting the reply.It was really helpful to me.Thank you so much.Great work.