That same dumb datatype 'serial' question . . .

I have a table with a serial type column which I'd like to reset to begin at 1 again. I delete all 100 rows in the table and insert a new one. The new value in the serial column is 101, even if I had also executed "alter table <tablename> modify (column serial (1))." In order to "re-initialize" the serial column, I have to insert a row with serial value 2147483647, then delete it. The next row inserted will begin with 1. I know the integer type is wrapping at the 2Gb mark, but why didn't deleting all rows re-enable the serial type to begin at 1? And why didn't the alter work? What is the DBMS doing? I've RTFMed and sifted the IIUG archives. Using IDS 7.31.UC4 on Solaris/Intel (don't ask).

Red Valsen wrote: > > I have a table with a serial type column which I'd like to reset to > begin at 1 again. I delete all 100 rows in the table and insert a new > one. The new value in the serial column is 101, even if I had also > executed "alter table <tablename> modify (column serial (1))." In order > to "re-initialize" the serial column, I have to insert a row with serial > value 2147483647, then delete it. The next row inserted will begin with > 1. I know the integer type is wrapping at the 2Gb mark, but why didn't > deleting all rows re-enable the serial type to begin at 1? And why > didn't the alter work? What is the DBMS doing? > > I've RTFMed and sifted the IIUG archives. > > Using IDS 7.31.UC4 on Solaris/Intel (don't ask). Section 5.9 of the FAQ's at the IIUG web site seems applicable. -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"

Thank you, Jonathen, for pointing out that I've merely repeated one of your contributions to the Informix FAQ. But can you shed more light on the observed behavior? If I've deleted every row in a table with a column of type serial, why doesn't the "counter" restart at 1? Is the column's max value being stored somewhere? I thought that the DBMS always dynamically re-calculated the max for the next value. Any further explication would be greatly appreciated. Jonathan Leffler wrote: > Red Valsen wrote: > > > > I have a table with a serial type column which I'd like to reset to > > begin at 1 again. I delete all 100 rows in the table and insert a new > > one. The new value in the serial column is 101, even if I had also > > executed "alter table <tablename> modify (column serial (1))." In order > > to "re-initialize" the serial column, I have to insert a row with serial > > value 2147483647, then delete it. The next row inserted will begin with > > 1. I know the integer type is wrapping at the 2Gb mark, but why didn't > > deleting all rows re-enable the serial type to begin at 1? And why > > didn't the alter work? What is the DBMS doing? > > > > I've RTFMed and sifted the IIUG archives. > > > > Using IDS 7.31.UC4 on Solaris/Intel (don't ask). > > Section 5.9 of the FAQ's at the IIUG web site seems applicable. > > -- > Yours, > Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> > Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN > "I don't suffer from insanity; I enjoy every minute of it!"

Red Valsen wrote: > > Thank you, Jonathen, for pointing out that I've merely repeated one of your > contributions to the Informix FAQ. > > But can you shed more light on the observed behavior? If I've deleted every > row in a table with a column of type serial, why doesn't the "counter" restart > at 1? Is the column's max value being stored somewhere? Yes. It helps if you know C-ISAM. There's a method isuniqueid() and another method issetunique() which handle this in C-ISAM. The root node of the C-ISAM index file stores the current value of the unique number, which is monotonically increasing except for wraparound at 2^31-1 back to 1. The idea carries over to RSAM and the IDS/OnLine family of products. ? I thought that the > DBMS always dynamically re-calculated the max for the next value. Oh no; that would be a performance disaster. Besides, how would SERIAL(1000) work in that scheme? > Any further explication would be greatly appreciated. > > Jonathan Leffler wrote: > > Red Valsen wrote: > > > > > > I have a table with a serial type column which I'd like to reset to > > > begin at 1 again. I delete all 100 rows in the table and insert a new > > > one. The new value in the serial column is 101, even if I had also > > > executed "alter table <tablename> modify (column serial (1))." In order > > > to "re-initialize" the serial column, I have to insert a row with serial > > > value 2147483647, then delete it. The next row inserted will begin with > > > 1. I know the integer type is wrapping at the 2Gb mark, but why didn't > > > deleting all rows re-enable the serial type to begin at 1? And why > > > didn't the alter work? What is the DBMS doing? > > > > > > I've RTFMed and sifted the IIUG archives. > > > > > > Using IDS 7.31.UC4 on Solaris/Intel (don't ask). > > > > Section 5.9 of the FAQ's at the IIUG web site seems applicable. > > > > -- > > Yours, > > Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> > > Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN > > "I don't suffer from insanity; I enjoy every minute of it!" -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"

Just drop and recreate the table, it's faster than deleting all rows anyway, and it will absolutely reset the serial number! ;^) The answer to your question is: Because that's the way they designed it to work. We have to live with it. Art S. Kagel Red Valsen wrote: > > I have a table with a serial type column which I'd like to reset to > begin at 1 again. I delete all 100 rows in the table and insert a new > one. The new value in the serial column is 101, even if I had also > executed "alter table <tablename> modify (column serial (1))." In order > to "re-initialize" the serial column, I have to insert a row with serial > value 2147483647, then delete it. The next row inserted will begin with > 1. I know the integer type is wrapping at the 2Gb mark, but why didn't > deleting all rows re-enable the serial type to begin at 1? And why > didn't the alter work? What is the DBMS doing? > > I've RTFMed and sifted the IIUG archives. > > Using IDS 7.31.UC4 on Solaris/Intel (don't ask).

Hi, With your current alter table statement you are not actually changing the data type. I think you will have to alter the serial field to an integer data type and then alter it back to serial(1). Paul McLaughlin Red Valsen wrote in message <3A3FD3AE.F34F251A@yahoo.com>... >I have a table with a serial type column which I'd like to reset to >begin at 1 again. I delete all 100 rows in the table and insert a new >one. The new value in the serial column is 101, even if I had also >executed "alter table <tablename> modify (column serial (1))." In order >to "re-initialize" the serial column, I have to insert a row with serial >value 2147483647, then delete it. The next row inserted will begin with >1. I know the integer type is wrapping at the 2Gb mark, but why didn't >deleting all rows re-enable the serial type to begin at 1? And why >didn't the alter work? What is the DBMS doing? > >I've RTFMed and sifted the IIUG archives. > >Using IDS 7.31.UC4 on Solaris/Intel (don't ask). >