persistance for sequence's current values

Hello World, I was hoping that 'syssequences' table will hold these values (in case I have more sequences) ... but not. Cant find these in any sysmaster tables too. But they have to be written somewhere waiting for IDS to awake... Regards, E.Ionescu

I had to make to subject shorten ... and I didn't done it properly (aside the 'persistence' mistake): The question was 'Where are all sequence's current values going when IDS stops?'

> select some_sequence_sequence.currval from systables where tabid = 1; currval 7 1 row(s) retrieved. > The value returned by a sequence's currval method is the last sequence value that was returned by a call to the sequence's nextval method. Sequences are implemented as a special table type ('Q') with a single column named seqserial8 of type SERIAL8. The table has no storage allocated - npused and nrows are always zero. The last returned value for the sequence is stored in the sequence's TABLESPACE TABLESPACE or partition page in the cur_serial8 column. You can see this in the sysmaster table sysactptnhdr: $ dbaccess sysmaster - Database selected. > select * from systabnames where tabname = 'some_sequence_sequence'; partnum 2099527 dbsname art owner art tabname some_sequence_sequence collate en_US.819 1 row(s) retrieved. > select * from sysactptnhdr where partnum = 2099527; address 1633933208 partnum 2099527 pn_flags 0 rowsize 0 ncols 0 nkeys 0 nextns 1 pagesize 2048 created 1269427124 cur_serial4 1 cur_bigserial 1 cur_serial8 7 <<=== Current sequence value fextsiz 8 nextsiz 8 nptotal 8 npused 1 npdata 0 lockid 2099527 nrows 0 flags 0 ucount 0 chunk 2 offset 53680 lastidxpn 0 nextns1 1 nextns2 0 badkeys 0 altstmp 450291582 ocount 0 skstamp 0 pta_oldvers 0 pta_newvers 0 pta_bmpagenum 0 pta_totpgs 0 pta_opems_allocd 0 pta_opems_filled 0 glscollname en_US.819 pf_rqlock 1 pf_wtlock 0 pf_deadlk 0 pf_dltouts 0 pf_isread 0 pf_iswrite 0 pf_isrwrite 0 pf_isdelete 0 pf_bfcread 1 pf_bfcwrite 1 pf_seqscans 0 pf_dskreads 0 pf_dskwrites 0 1 row(s) retrieved. > Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Mar 24, 2010 at 4:52 AM, EMANUEL IONESCU <elionescu@yahoo.com>wrote: > Hello World, > > I was hoping that 'syssequences' table will hold these values (in case I > have > more sequences) ... but not. > Cant find these in any sysmaster tables too. > But they have to be written somewhere waiting for IDS to awake... > > Regards, > E.Ionescu > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00235400e1ea728300048289ac9f

Hi everyone, A correction: currval returns the last value for the session using the method. If multiple sessions are using the same sequence, then each may have a different current value. nextval always gets the next value over all the sessions, but each session only sees it's own current value. So the only way to find the overall current value is to query the partition header as previously posted. Cheers, Dick Snoke Executive IT Specialist IBM Software Group - ChannelWorks Tel: (404) 487-1595 Email: dsnoke@us.ibm.com From: "Art Kagel" <art.kagel@gmail.com> To: ids@iiug.org Date: 03/24/10 06:49 AM Subject: Re: persistance for sequence's current values [19404] Sent by: ids-bounces@iiug.org > select some_sequence_sequence.currval from systables where tabid = 1; currval 7 1 row(s) retrieved. > The value returned by a sequence's currval method is the last sequence value that was returned by a call to the sequence's nextval method. Sequences are implemented as a special table type ('Q') with a single column named seqserial8 of type SERIAL8. The table has no storage allocated - npused and nrows are always zero. The last returned value for the sequence is stored in the sequence's TABLESPACE TABLESPACE or partition page in the cur_serial8 column. You can see this in the sysmaster table sysactptnhdr: $ dbaccess sysmaster - Database selected. > select * from systabnames where tabname = 'some_sequence_sequence'; partnum 2099527 dbsname art owner art tabname some_sequence_sequence collate en_US.819 1 row(s) retrieved. > select * from sysactptnhdr where partnum = 2099527; address 1633933208 partnum 2099527 pn_flags 0 rowsize 0 ncols 0 nkeys 0 nextns 1 pagesize 2048 created 1269427124 cur_serial4 1 cur_bigserial 1 cur_serial8 7 <<=== Current sequence value fextsiz 8 nextsiz 8 nptotal 8 npused 1 npdata 0 lockid 2099527 nrows 0 flags 0 ucount 0 chunk 2 offset 53680 lastidxpn 0 nextns1 1 nextns2 0 badkeys 0 altstmp 450291582 ocount 0 skstamp 0 pta_oldvers 0 pta_newvers 0 pta_bmpagenum 0 pta_totpgs 0 pta_opems_allocd 0 pta_opems_filled 0 glscollname en_US.819 pf_rqlock 1 pf_wtlock 0 pf_deadlk 0 pf_dltouts 0 pf_isread 0 pf_iswrite 0 pf_isrwrite 0 pf_isdelete 0 pf_bfcread 1 pf_bfcwrite 1 pf_seqscans 0 pf_dskreads 0 pf_dskwrites 0 1 row(s) retrieved. > Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Mar 24, 2010 at 4:52 AM, EMANUEL IONESCU <elionescu@yahoo.com>wrote: > Hello World, > > I was hoping that 'syssequences' table will hold these values (in case I > have > more sequences) ... but not. > Cant find these in any sysmaster tables too. > But they have to be written somewhere waiting for IDS to awake... > > Regards, > E.Ionescu > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00235400e1ea728300048289ac9f ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.