We are having an issue wherein our Informix DB serial number column, which is of type SERIAL is reaching the max limit. Ink bleed footage. We are re-setting it manually to '1' every time when we have this issue. This makes long int useful for storing values of the SMALLINT, INTEGER, INT, and SERIAL data types of Informix®. Do not, however, attempt to use a long int data type to store the 8-byte Informix integer data types INT8, BIGINT, SERIAL8, or BIGSERIAL.

You can recycle a SERIAL column by inserting a row with an explicit number that is 2,147,483,647 (2 31-1), and then inserting another row with the number 0. This will reset the counter back to 1, but subsequent attempts to insert a new serial number (with 0 as the inserted value) may collide with a pre-existing row, and the INSERT will then fail (but the next attempt will use the next serial number). I have a customer using an isql perform screen to add rows and he would like for the serial column to wraparound to 1000 after reaching 99999, but he will need to keep about the last 1,000 rows in the table to be able to query and update rows as customers will be making interest payments, redeeming their pawned items, or forfeit the pawns. The problem is, if we keep rows with serial id's 98000 to 99999, then the serial column will want to insert 100000 when the next row is added, so it looks I wont be able to grant customers wish unless I update serial id's 98000 to 99999 with negative values? – Apr 19 '17 at 2:00 •.

Avoid SERIAL8; use BIGSERIAL instead. One reason is that BIGSERIAL uses 8 bytes of storage where SERIAL8 uses 10 — I don't want to discuss why this is the case (hint: I've been annoyed about it for 20 years).

Also, this doesn't quite fit with the 'no changes to the application' requirement. The main issue is that to retrieve the 'just created serial number', you have to make a function call with BIGSERIAL or SERIAL8, whereas you access part of the SQLCA with SERIAL — at least, in ESQL/C.

The rules may be different for ODBC, JSBC — but at least the issue would have to be considered. – Mar 10 '17 at 20:31 •.

I agree that a switch to 64-byte serial numbers is probably best — repeatedly dealing with recycling, especially given that insert operations will fail (unless there's some process that ensures that there are no really old record numbers in the table, so when the number recycles, there won't be collisions) is silly. I also agree that without a lot more information about application (starting with 'language it is written in' and including 'API used to connect to the database') it is hard to know what the impact of the change might be — it might be a zero-change operation, it might not. – Mar 10 '17 at 20:43 •. @JonathanLeffler I filed for divorce from informix many years back when a server update changed the behaviour of adding a number of days to a date (I forget the which function/syntax) from returning a date to returning a timestamp!