Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2007
    Posts
    3

    Unanswered: sequence table.-urgent please respond.

    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.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jan 2007
    Posts
    3

    Thumbs up Thanks!

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •