Hi,

Can anyone tell me where I can get all sequence's current value?


Allian

Identity_val_local()

hi.

I think you can get the same also by querying SYSIBM.SYSSEQUENCES table.

You can query the lastassignedval for the seqname.

IDENTITY != SEQUENCE

My bad. I guess I should read ALL of the words.

Nitin ,
The lastassignedval in the SYSIBM.SYSSEQUENCES gives the last cached value and not the last assigned one ..
Cheers
Sathyaram

C:\>db2 "create sequence seq2"
DB20000I The SQL command completed successfully.


C:\>db2 "select lastassignedval from sysibm.syssequences where seqname='SEQ2'"

LASTASSIGNEDVAL
---------------------------------
-

1 record(s) selected.


C:\>db2 "values(nextval for seq2)"

1
-----------
1

1 record(s) selected.


C:\>db2 "select lastassignedval from sysibm.syssequences where seqname='SEQ2'"

LASTASSIGNEDVAL
---------------------------------
20.

1 record(s) selected.


C:\>db2 "values(nextval for seq2)"

1
-----------
2

1 record(s) selected.


C:\>db2 "select lastassignedval from sysibm.syssequences where seqname='SEQ2'"

LASTASSIGNEDVAL
---------------------------------
20.

1 record(s) selected.


C:\>

To answer the original question
PREVVAL FOR <sequence-name>

Thanks Sathyaram for the wonderful explanation.

You have cleared a lot of cobwebs for many of us with your wonderful example.

Appreciate that!