Re: Serial field value resets (continued)

I sent another mail in response to one of the comments on the original mail. Treat this as an addendum to my previous mail. >Subject: Serial field value resets (continued) >Date: Wed, 21 Apr 93 9:02:14 EDT >From: uunet!cdin-1.compu.com!mark >X-Informix-List-Id: <list.2183> >The original problem was instantiated due to a condition in which 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. > If so, shouldn't dropping the index after all the records are > deleted and recreating it resolve the condition? No. >I didn't want to "drop" and recreate the table - the table definition is >fairly long and not setup in an SQL. Plus, I was doing development on >1 machine and testing on another machine and drop/recreate causes the >table_id to be changed and when I installed the new software/table on the >test machine I ended up w/ multiple copies of the same data file (the "old" >version(s) were inaccessable) which tied up lots of disk space. >E.G. table151.dat, table151.idx > table181.dat, table181.idx > Were actually the same table. ...181 was the "current" version, > ...151 was the "old" version. Well copy table181.dat over table151.dat, and table181.idx over table151.idx, and then drop table181.dat and table181.idx. Unless you updated Systables to point to table181, it would have continued using table151 anyway! And making those sorts of changes is unsupported, and so is the copying, so you are just adding to your list of not officially supported actions. BTW: As long as the table does not use floats, you can copy tables between different machine architectures. If it does use floats (or smallfloats), you can't. > Just for additional yucks.... > > 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. See my other e-mail for how to resolve your problem. Yours, Jonathan Leffler (johnl@obelix.informix.com) #include <disclaimer.h>