By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,984 Members | 1,011 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,984 IT Pros & Developers. It's quick & easy.

List sequences?

P: n/a

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
Share this Question
Share on Google+
4 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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.