Very Computer

From ihor.j.kinal:

* > Is there a way to reset a serial field to zero, without dropping the
* > table.  Assuming there is no data in the table.  I am using Informix
* > online 5 on a NCR unix box.
* >
*
* I've seen some suggestions to alter the field to integer. While that
* might work, if the table is REALLY large, it would take a LONG time
* [and the alter might fail, if you don't have the spare space].
*
* Much easier is to insert a value one less than the max value for a long,
* then add another record [or maybe two].  Informix will then roll the
* serial key around to zero.  BUT, note that it WILL REASSIGN keys
* in sequence, so you might get duplicate keys [I think], if you
* have any old records.
*
* [Note, I haven't really tried it, but that's what I was taught in the
* Admin class a couple of years ago, so all disclaimers apply].

Every time I tried to insert MAX-1 into the serial field, then the next
insert is not 1 unless all values up to MAX-1 have been used.  For example:

    CREATE TABLE ser_table ( ser_fld SERIAL NOT NULL );
    INSERT INTO ser_table VALUES ( 0 );
    INSERT INTO ser_table VALUES ( 0 );
    INSERT INTO ser_table VALUES ( 0 );
    INSERT INTO ser_table VALUES ( 0 );
    INSERT INTO ser_table VALUES ( MAX - 1 );  -- I think it's 2147483647

    INSERT INTO ser_table VALUES ( 0 ); -- This has a value of 5

         Robert Minter             |Data Systems Support|         \\\_///
Programmer, Software Development   |     Orange, CA     |         ( _ _ )

bangpath:  uunet.uu.net!dssmktg!rob| Fax: 714.771.3028  |          \`-'/
#include <disclaimer.h>                                  SURF'S UP  \_/

Yes, the old serial numbers will be reused so you may get failures to
insert because of duplicate keys.  But not in the original question which
stipulated an empty table.

Quote:>Every time I tried to insert MAX-1 into the serial field, then the next
>insert is not 1 unless all values up to MAX-1 have been used.

Inserting MAX - 1 does not work; however, inserting MAX - 2 and then an
ordinary 0 value does work.  No, I don't have a satisfactory explanation of
this, but I'm reasonably sure it has something to do with NULLs which are
represented as -2147483648.

I attach my standard spiel on the subject.

Yours,

******************************************************************************

I haven't got a good explanation for why it happens the way it does, but this
is what does happen.  I used this SQL script to create a database called junk
and a table called junk with a serial column.  I then tried loading the
database with certain values.

---------------------
create database junk;

create table junk (col00 serial(1000) not null, col01 char(10) not null);
create unique index pk_junk on junk(col00);

insert into junk values (0, "A");
select max(col00), min(col00) from junk; select * from junk;
insert into junk values (2147483647, "a");
select max(col00), min(col00) from junk; select * from junk;
insert into junk values (0, "A");
select max(col00), min(col00) from junk; select * from junk;
drop table junk;

create table junk (col00 serial(1000) not null, col01 char(10) not null);
create unique index pk_junk on junk(col00);

insert into junk values (0, "A");
select max(col00), min(col00) from junk; select * from junk;
insert into junk values (2147483646, "a");
select max(col00), min(col00) from junk; select * from junk;
insert into junk values (0, "A");
select max(col00), min(col00) from junk; select * from junk;
insert into junk values (0, "A");
select max(col00), min(col00) from junk; select * from junk;
insert into junk values (0, "A");
select max(col00), min(col00) from junk; select * from junk;

close database;
drop database junk;
---------------------

The transcript of the session showed that when the value 2147483647 was
inserted, the unique ID was not updated to the next value (2147483648) because
in 2's-complement arithmetic, that number cannot be represented.  When the
value 2147483646 was inserted, inserting another row loaded 2147483647, and
inserting yet another row recycled the numbers round to 1.  If the row one
had already existed, there would have been an error -- cannot insert duplicate
value in column with unique index.

******************************************************************************

Some internals information...

Quote:>The original problem arose because all the rows in a table containing a
>SERIAL field were DELETED, then new records were INSERTED.  The problem
>was that the SERIAL field just picked up from the last value used.
>E.G.   100 records exist w/ values 1 THRU 100.
>       DELETE ALL FROM <table>
>       INSERT INTO <table> VALUE (0) <----- into serial field.
>       Resulting value = 101.
>If there are in fact no records, how can the "next" value be anything
>OTHER than 1?? i.e.  WHERE does Informix keep the "max" value for a SERIAL
>field - in the index?

Yes.  In C-ISAM, there are two calls, isuniqueid and issetunique.
isuniqueid returns the next available number from a field in the index
header information, and issetunique allows you to change the value which
will be returned by isuniqueid, provided that your new value is greater
than the old value which would have been returned.  The only exception is
when the value overflows (or wraps around), when the value changes from
2**31-1 to 1.  SE uses these calls to implement SERIAL columns; OnLine
provides analogous facilities.

Quote:>If so, shouldn't dropping the index after all the records are deleted and
>recreating it resolve the condition?

No.

Quote:>If there ARE records in the file and you ALTER it replacing the SERIAL
>with INTEGER then you global update all the values to 0, then ALTER the
>table back to SERIAL again - will Informix re- sequence the records, or
>will it blow off with a duplicate value error?

No resequencing: once the record is inserted, the value is pretty much an
ordinary INTEGER field (except it can't be updated).  Unless you dropped
the unique index on the serial column (or failed to put one there in the
first place), the engine will complain about duplicate values.