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.
> 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).
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.
> Query on Versions
>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.