Art,
Just for grins, check the properties of your sequence, particularly the CACHE/NOCACHE option. In my case, it is CACHE 20. As a result, the cur_serial8 column in sysptnhdr increments by 20, then stays there until I have called for sequence.nextval enough times to get to that value, and then it increments again. I just retrieved sequence.nextval (143), but the cur_serial8 shows 160. Previously, the nextval was at 137 and cur_serial8 was at 140, and I kept retrieving sequence.nextval and checking cur_serial8 one at a time, watching to see. It was when sequence.nextval returned 141 that cur_serial8 incremented to 160.
You also can see these values with 'oncheck -pt partnum':
[informix@test_server ~]$ oncheck -pt 10485990
TBLspace Report for dev_db:developer1.test_sequence
Physical Address 10:26580
Creation date 01/07/2022 11:16:01
TBLspace Flags 80801 Page Locking
TBLspace use 4 bit bit-maps
Maximum row size 1
Number of special columns 0
Number of keys 0
Number of extents 0
Current serial value 1
Current SERIAL8 value 160
Current BIGSERIAL value 1
Current REFID value 1
Pagesize (k) 2
First extent size 8
Next extent size 8
Number of pages allocated 0
Number of pages used 0
Number of data pages 0
Number of rows 0
Partition partnum 10485990
Partition lockid 10485990
Last DML time No Record
Extents
Logical Page Physical Page Size Physical Pages
This leads me to believe that the actual updates are being logged to the logical log, and only written when the CACHE causes it to jump by whatever increment, and probably when the database shuts down. It does not seem to be updating when a checkpoint occurs, as it was a few hours between when I stopped at nextval = 137 and when I tried testing again. My CKPTINTVL is set to 300, so I had several checkpoints during that interval. Not sure why it didn't update to reflect the actual value during the checkpoints, rather than retaining the larger value set by the CACHE. I mean, at some point you'd expect it to update to reflect what was in memory, in case of a crash.
------------------------------
Mark Collins
------------------------------
Original Message:
Sent: Fri January 07, 2022 04:05 PM
From: Art Kagel
Subject: question on sequences
Correct. Oh, I was wrong, the next serial number is recorded in the cur_serial8 column.
Here's an example from my test database:
tabname some_sequence_sequence
owner art
partnum 4194437
tabid 104
rowsize 10
ncols 1
nindexes 0
nrows 0.00
created 06/29/2020
version 6881286
tabtype Q
locklevel P
npused 0.00
fextsize 16
nextsize 16
flags 0
site
dbname
type_xid 0
am_id 0
pagesize 2048
ustlowts
secpolicyid 0
protgranularity
statchange 0
statlevel
5 row(s) retrieved.
> select * from sysmaster:sysptnhdr where partnum = 4194437;
partnum 4194437
flags 526337
rowsize 1
ncols 0
nkeys 0
nextns 0
pagesize 2048
created 1593482658
serialv 1
fextsiz 8
nextsiz 8
nptotal 0
npused 0
npdata 0
octptnm -1
lockid 4194437
nrows 0
ninserts 0
nupdates 0
ndeletes 0
cur_serial8 1281
cur_bigserial 1
dbsnum 4
pta_oldvers 0
pta_newvers 0
pta_bmpagenum 0
pta_totpgs 0
pta_opems_allocd 0
pta_opems_filled 0
glscollname en_US.819
flags2 0
acc_or_mod_tm 0
sid 0
1 row(s) retrieved.
>
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
Original Message:
Sent: Fri January 07, 2022 03:42 PM
From: Mark Collins
Subject: question on sequences
Art,
Thanks, that confirms what I suspected to be the case. So other than the one partition page, there is no storage allocated to the sequence.
Mark
------------------------------
Mark Collins
Original Message:
Sent: Fri January 07, 2022 02:08 PM
From: Art Kagel
Subject: question on sequences
Tricksy tricksy developers put the sequence into the table's partition page as the BIGSERIAL value. So a sequence internally is treated just like a table with a single BIGSERIAL type column.
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
Original Message:
Sent: Fri January 07, 2022 01:27 PM
From: Mark Collins
Subject: question on sequences
Just out of curiosity, where are sequences stored? I know that they get a partnum assigned, and from that I see that they are in a particular dbspace, but I do not think that a table is created anywhere, but the sequence value has to be stored somewhere so that it is persistent. So does the tblspace tblespace page for this partnum? Or is there some other place where the value is stored?
------------------------------
Mark Collins
------------------------------
#Informix