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------------------------------
Original Message:
Sent: Sat August 06, 2022 10:16 AM
From: Mike Walker
Subject: quirk with sequences
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
Original Message:
Sent: Fri August 05, 2022 05:59 PM
From: Mark Collins
Subject: quirk with sequences
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