New RFE: Improvement in the check for high serial values

Hi, I have raised new RFE: https://ibm-data-and-ai.ideas.aha.io/ideas/INFX-I-383 --> updated link It's hard to check if you reach the upper limit of your serials. This improvement would make it much easier and is certainly easy to implement. Please vote for this RFE to be realized. Thanks a lot Andreas Seifert ------------------------------ Andreas Seifert ------------------------------ #Informix

Should this RFE be in the Informix area? https://ibm-data-and-ai.ideas.aha.io/ideas?project=INFX Currently it is in "Information Server - Platform" https://ibm-data-and-ai.ideas.aha.io/ideas?project=ISP ------------------------------ ~~~~~~~~~ Eric Rowell ------------------------------

You're right. It's good to read properly. I must have been reading "Informix server" here instead of "Information server". Unfortunately the platform cannot be changed anymore, so I had to create a new RFE and also changed the link in my first post. https://ibm-data-and-ai.ideas.aha.io/ideas/INFX-I-383 To all who have already left a vote, please also vote for the new one. Thank you Andreas Seifert ------------------------------ Andreas Seifert CURSOR Software AG ------------------------------

Hi! Some questions to this (because we also reach in near future the serial-end): a) What happens, when you reach the end of the positive Integer (2,147,483,647). Is the next serial value negative (-2,147,483,647)? Or b) Conversion from serial to serial8 Is this an in-place-alter (fast) or is it a slow-alter (and needs hours to completion). ------------------------------ Kind Regards Stefan ------------------------------

Hi, a) When you reach the maximum serial value, Informix starts at 1 again when you insert with 0 as value. If you have a unique index on the field, you will get an error on the first existing value: -239 Could not insert new row - duplicate value in a UNIQUE INDEX column otherwise there are duplicate values. b) The alter table from serial to serial8 or bigserial is an fast alter (inplace). But the problem is usually not changing the table but changing the application, because you need different data types for a Serial8 or BigSerial. Best Regards Andreas ------------------------------ Andreas Seifert CURSOR Software AG Gießen ------------------------------

Hi Andreas, Yes, it appears to be true that the old next serial values remain in sysmaster:sysptnhdr. We check for serials approaching the limit (and also via syscolumns.colmax for ordinary int columns approaching the limit, although this only works for indexed columns) and ignore columns converted to bigserial or serial8 simply by joining to syscolumns and filtering on column type. Something like this: SELECT TRIM(st.owner) AS owner, TRIM(st.tabname) AS tabname, TRIM(sc.colname) AS colname, 2147483647 - sh.serialv AS remaining FROM sysfragments sf, sysmaster:sysptnhdr sh, systables st, syscolumns sc WHERE sf.tabid=st.tabid AND sf.partn=sh.partnum AND sc.tabid=st.tabid AND sc.coltype in (6, 262) AND 2147483647 - sh.serialv < ? UNION SELECT TRIM(st.owner), TRIM(st.tabname), TRIM(sc.colname), 2147483647 - sh.serialv FROM sysmaster:sysptnhdr sh, systables st, syscolumns sc WHERE sh.partnum=st.partnum AND sc.tabid=st.tabid AND sc.coltype in (6, 262) AND 2147483647 - sh.serialv < ? Replace '?' with your threshold for your check. The first statement covers partitioned tables and the second unpartitioned. One last thought, unless you have a reason to use serial8 (which is usually compatibility with 32-bit systems), you will benefit from using bigserial, which is a native 64-bit type taking up less space and is more efficient. Ben. ------------------------------ Benjamin Thompson ------------------------------

Hello Ben, Thanks for the tip. Testing the syscolumns seems to be the only way to solve the problem at the moment. The check is for our Informix administration tool Admin-Scout. There the check runs in a sensor in the sysadmin database and generate alarms when the limits are exceeded. Unfortunately you can't access a user database from there if it doesn't have en_us.819 as DB_LOCALE. If the sysptnhdr/sysactptnhdr is reset correctly, this would be much easier for me and everyone else. Thanks Andreas ------------------------------ Andreas Seifert CURSOR Software AG Gießen ------------------------------

Andreas: The workaround for now is to reset the next SERIAL value of the column to something small before altering the column type to BIGSERIAL: ALTER TABLE mytable MODIFY ( my_ser_col SERIAL(2) ); ALTER TABLE mytable MODIFY ( my_ser_col BIGSERIAL(2147483645 ) ); Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

Thanks Art, this is the easiest way to reset the values. But our Admin-Scout is a tool for other companies to monitor their Informix instances. This solution requires the necessary discipline from our customers to make these statements in addition. Unfortunately this cannot be done afterwards. I will pass on this way to the customers if they ask for it. The best way would be, of course, if Infomix would solve this cleanly. Therefore the RFE. I hope that as many as possible vote for the RFE and that HCL realizes it. Thanks a lot Andreas ------------------------------ Andreas Seifert CURSOR Software AG Gießen ------------------------------

Hi Eric, Probably a good candidate to be deleted by IBM because they don't know what Informix is

Agreed. The RFE details a needed fix! Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.