[Bob Elliot]

The Alter Table .. modify .. (.. serial(?)) statement allows the next value of a serial to be changed.

This cannot be moved below the maximum value it has been set to on a table - even if the higher serials have been deleted.

We have rather a large table where the serial has been set to a ridiculously large value (suspect an insert with corrupt value from inside an esql/c program).

Is there any way to set the next value to a lower value - other than by unloading, dropping and reloading the table ?

How is the constraint which forces the serial number upwards implemented ?

[Roelwe]

You could change the column to an integer datatype.
Set the values as you wish an then alter the table to reset the column to a serial datatype. If you have an index on the serial column, you might consider to drop it first and recreate it afterwards.

[Roelwe]

> Nice One,
> It's not the first time I've met this problem.

> We tried on a development box - 170K rows - took about a minute.

> Few questions
> Didn't drop the index - any risk there ?
> The conversion to integer was virtually instantaneous - does this
> simply involve changing a system table ?
> The conversion to serial was more time consuming - was it recreating > the maximum serial ?
> Any need to lock the table or stop logging ?

> The target table has around 14 Million rows and as we would like to
> keep any outage to a minimum we would rather avoid dropping and
> recreating the index if that is safe.

You don't have to drop/recreate the index.
It is faster to drop and recreate it.

The conversion is very easy. serial is an int8 datatype, so no conversion needed. Informix just creates a new version of the table to have the least downtime as possible. Once you have lots of time you could reset the table to version 1, but that might take some time.

The conversion to serial was more time consuming because he had to check for any duplicates. So once you converted to integer, recreate the index and than convert back to serial.
Just make sure you don't create a long transaction. That might be a disaster. make sure you have enough log space, or just tur off logging (not really the best solution).

Anyway, goodluck!

[Bob Elliot]

Is there any implication to not setting the version of the table back to 1 ?

[Roelwe]

Just a little performance.
And when you do a migration, that's the first thing it does. So your migration might take some more time. This is actually one of the biggest time-consuming issues of a migration.
Hope it helps.

[Roelwe]

> Query on Versions
>Hi Roelwe,
>I date back to earlier versions of Informix (4) where the table was >converted (I think) when you changed the type of a column.

>I have also (briefly) worked with Ingres.
>In Ingres 2 (if I remember correctly) a table has versions.
>Each page on the database carries the version which applies to that >page.
>If you change the table structure (options were limited) this only >changed the header of the table.
>Each page was then converted between versions as it was updated.

>Have Informix implemented this - if so from what version and where >can I read up on it ?

>Sorry to be a pain - but I'm curious.


No problem in beiing curious.
informix and Ingres merged their technology and it resulted in (I think it was ) version 5.
The table versioning of Informix is implemented since then. (More than 10 years) Oracle did it 2 years ago ...
I think you should find it somewhere in the documentation. It is available on the web.