Informix

 View Only
  • 1.  question on sequences

    Posted Fri January 07, 2022 01:27 PM
    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
    ------------------------------


  • 2.  RE: question on sequences

    Posted Fri January 07, 2022 01:47 PM
    Hi Mark.

    See system catalog table "syssequences" in each database:

    https://www.ibm.com/docs/en/informix-servers/14.10?topic=tables-syssequences

    ------------------------------
    Doug Lawry
    Oninit Consulting
    ------------------------------



  • 3.  RE: question on sequences

    Posted Fri January 07, 2022 03:40 PM
    Doug,

    That's akin to the systables table, in that it describes the sequence.  I was asking where the actual data for the sequence resides.



    Mark

    ------------------------------
    Mark Collins
    ------------------------------



  • 4.  RE: question on sequences

    IBM Champion
    Posted Fri January 07, 2022 02:08 PM
    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
    ------------------------------



  • 5.  RE: question on sequences

    Posted Fri January 07, 2022 03:42 PM
    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
    ------------------------------



  • 6.  RE: question on sequences

    IBM Champion
    Posted Fri January 07, 2022 04:06 PM
    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
    ------------------------------



  • 7.  RE: question on sequences

    Posted Fri January 07, 2022 05:56 PM
    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
    ------------------------------