471,579 Members | 1,580 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,579 software developers and data experts.

List sequences?


Folks,

I have created bunch of sequences in DB2 7.2.

How do I list all the defined sequences and if possible their current values?

Thanks.
--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.
Nov 12 '05 #1
4 49474
Hi Hemant,
How do I list all the defined sequences and if possible their current values?


select *
from syscat.sequences;

Should give you everything you need to know about the sequences.

Christian.
Nov 12 '05 #2
You can get the list of sequences from syscat.sequences however
There is no way to get the current value of the sequence unless you
have already updated it in the current session.

To get the next value for a sequence :
VALUES NEXTVAL FOR <seq-name>

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

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.
Thanks
Amit
singleton at canada.com
ch**************@techie.com (Christian Maslen) wrote in message news:<b9**************************@posting.google. com>...
Hi Hemant,
How do I list all the defined sequences and if possible their current values?


select *
from syscat.sequences;

Should give you everything you need to know about the sequences.

Christian.

Nov 12 '05 #3
If you dig deeper into SYSIBM.SYSSEQUENCES there is a field containing
the next batch to be allocated for the cache.
Two cave-at:
1. SYSIBM.SYS* is undocumented on purpose. These tables may change
without warning on any full release upgrade.
2. This value is really just an upper boundary. While you know the
highest generated values so far was in the preceeding CACHE range, In an
MPP system may thow soem really old values at you from a node that works
of an old cache.

Note that sequences are a performance feature. The only way to get more
acurate is to use NO CACHE. But you will feel the impact.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Nov 12 '05 #4
While stranded on information super highway Christian Maslen wrote:
Hi Hemant,
How do I list all the defined sequences and if possible their current values?


select *
from syscat.sequences;

Should give you everything you need to know about the sequences.

Christian.


Thanks. I will try that.
--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

13 posts views Thread by Santanu Chatterjee | last post: by
34 posts views Thread by jblazi | last post: by
6 posts views Thread by kartik | last post: by
4 posts views Thread by temp | last post: by
32 posts views Thread by Robin Becker | last post: by
72 posts views Thread by Gregory Petrosyan | last post: by
35 posts views Thread by erikwickstrom | last post: by
4 posts views Thread by dustin.getz | last post: by
reply views Thread by XIAOLAOHU | last post: by
reply views Thread by lumer26 | last post: by
reply views Thread by Vinnie | last post: by
reply views Thread by lumer26 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.