Informix

 View Only
  • 1.  quirk with sequences

    Posted Fri August 05, 2022 05:59 PM
    I noticed a weird quirk today with sequences, and wondered whether anyone else had seen it or knew if it was expected behavior.

    When running 'dbschema -d database_name -seq sequence_name', I get output similar to:

    create sequence "informix".my_test_sequence increment by 1 start with 1 maxvalue 9223372036854775807 minvalue 1 cache 20 order;
    alter sequence "informix".my_test_sequence restart with 18;
    revoke all on "informix".my_test_sequence from "public" as "informix";

    If I run it again, I get:

    create sequence "informix".my_test_sequence increment by 1 start with 1 maxvalue 9223372036854775807 minvalue 1 cache 20 order;
    alter sequence "informix".my_test_sequence restart with 19;
    revoke all on "informix".my_test_sequence from "public" as "informix";

    Every time I run dbschema, the sequence increments.

    I suspect that this is because there is no way to get the currval of a sequence without first having retrieved the nextval, but the fact that a simple administrative utility alters the value is a bit disconcerting.  It means that we can't use a sequence to assign sequential values for things like check numbers, etc.  And, while I haven't tried it yet, I would expect that dbexport would also behave the same way.

    So, is this "expected" behavior?  Is it documented somewhere?  Or can we submit an RFE to change that behavior.

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

    #Informix


  • 2.  RE: quirk with sequences

    Posted Fri August 05, 2022 06:21 PM
    Just tested dbexport, and confirmed that it also causes the sequence to be incremented each time it is run.

    Not what I wanted to see, for either dbschema or dbexport.

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



  • 3.  RE: quirk with sequences

    IBM Champion
    Posted Sat August 06, 2022 10:17 AM
    Hi Mark - yes, I have seen the same behavior, when I was performing a migration.  That was with Informix 12.10.FC6 on Solaris.  What was strange is that we had 3 sequences, but only one of them would increment after running dbschema.  I didn't have time to do more investigation at the time and see what was different.  I'll have to see if I can get some more information.


    ------------------------------
    Mike Walker
    xDB Systems, Inc
    www.xdbsystems.com
    ------------------------------



  • 4.  RE: quirk with sequences

    IBM Champion
    Posted Mon August 08, 2022 11:49 AM
    I looked at this some more, because I have seen a case where the sequence did NOT increment when using dbschema -seq

    When creating the sequence, if a restart value is specified with "alter sequence" then dbschema will NOT increment the sequence value.  That is until the restart value goes away as soon as you get the nextval of the sequence.  At that point dbschema WILL increment the sequence value.

    This behavior is documented under the restart option of ALTER SEQUENCE:

    When you modify a sequence using the RESTART option, the restart value is stored in the syssequences system catalog table only until the first use of the sequence object in a NEXTVAL expression. After that, the value is reset in the system catalog. Use of the dbschema utility can increment sequence objects in the database, creating gaps in the generated numbers that might not be expected in applications that require serialized integers.

    We can test this with 3 sequences.  The first and second have a restart value specified, the third does not:

    create sequence "informix".testseq1 increment by 1;
    alter sequence "informix".testseq1 restart with 10;
    create sequence "informix".testseq2 increment by 1;
    alter sequence "informix".testseq2 restart with 20;
    create sequence testseq3 increment by 1;

    Looking at syssequences, the restart value is shown for the first 2 sequences:

    tabname restart_val

    testseq1 10
    testseq2 20
    testseq3

    dbschema shows all sequences and their restarting value:

    dbschema -d stores_demo -seq all

    create sequence "informix".testseq1 increment by 1 maxvalue 9223372036854775807 minvalue 1 cache 20 order;
    alter sequence "informix".testseq1 restart with 10;
    revoke all on "informix".testseq1 from "public" as "informix";
    create sequence "informix".testseq2 increment by 1 maxvalue 9223372036854775807 minvalue 1 cache 20 order;
    alter sequence "informix".testseq2 restart with 20;
    revoke all on "informix".testseq2 from "public" as "informix";
    create sequence "informix".testseq3 increment by 1 maxvalue 9223372036854775807 minvalue 1 cache 20 order;
    alter sequence "informix".testseq3 restart with 1;
    revoke all on "informix".testseq3 from "public" as "informix";

    Running dbschema again shows that testseq3 (no restart value) has been incremented:

    create sequence "informix".testseq1 increment by 1 maxvalue 9223372036854775807 minvalue 1 cache 20 order;
    alter sequence "informix".testseq1 restart with 10;
    revoke all on "informix".testseq1 from "public" as "informix";
    create sequence "informix".testseq2 increment by 1 maxvalue 9223372036854775807 minvalue 1 cache 20 order;
    alter sequence "informix".testseq2 restart with 20;
    revoke all on "informix".testseq2 from "public" as "informix";
    create sequence "informix".testseq3 increment by 1 maxvalue 9223372036854775807 minvalue 1 cache 20 order;
    alter sequence "informix".testseq3 restart with 2;
    revoke all on "informix".testseq3 from "public" as "informix";

    Now let's get the nextval for testseq2:

    select testseq2.nextval from sysmaster:sysdual;

    nextval
    20

    Running dbschema again shows that testseq2 & testseq3 have been incremented:

    create sequence "informix".testseq1 increment by 1 maxvalue 9223372036854775807 minvalue 1 cache 20 order;
    alter sequence "informix".testseq1 restart with 10;
    revoke all on "informix".testseq1 from "public" as "informix";
    create sequence "informix".testseq2 increment by 1 maxvalue 9223372036854775807 minvalue 1 cache 20 order;
    alter sequence "informix".testseq2 restart with 21;
    revoke all on "informix".testseq2 from "public" as "informix";
    create sequence "informix".testseq3 increment by 1 maxvalue 9223372036854775807 minvalue 1 cache 20 order;
    alter sequence "informix".testseq3 restart with 3;
    revoke all on "informix".testseq3 from "public" as "informix";

    syssequences shows that testseq2 and testseq3 have no restart value.  testseq1 does have a restart value, so it will not be incremented when using dbschema.

    tabname restart_val

    testseq1 10
    testseq2
    testseq3

    Using currval does not remove the restart value.

    So this does work as documented, however I'm not sure I am particularly happy with this behavior as it doesn't seem very intuitive.


    ------------------------------
    Mike Walker
    xDB Systems, Inc
    www.xdbsystems.com
    ------------------------------



  • 5.  RE: quirk with sequences

    Posted Tue August 09, 2022 10:01 AM
    Mike,

    Thanks for the very detailed description of the sequence behavior.  I had not spotted that section of the ALTER SEQUENCE documentation.  No point in submitting an RFE to "fix" it, since it's working as documented.  I may submit an RFE to add an option to dbschema to suppress the ALTER SEQUENCE, since that appears to be the mechanism that increments the sequence.  After all, dbaccess does not create an ALTER TABLE statement to reset the value of a SERIAL column.  


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



  • 6.  RE: quirk with sequences

    IBM Champion
    Posted Sun August 07, 2022 09:26 AM
    Mark:
    I had the same problem writing SEQUENCE support into myschema. The is indeed no good way to get the curval of a sequence object. So, yes, in dbschema, myschema, and even dbexport reporting on a sequence will increment it.

    You can certainly put an RFE in, but I would note that as far as I have been able to determine Oracle, SQL Server, and PostgreSQL have the same issue. So you may get "works as designed" as a response. But count me vote in.

    Art

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------