Informix

nested-group-icon.png

DB2

Expand all | Collapse all

New RFE: Improvement in the check for high serial values

  • 1.  New RFE: Improvement in the check for high serial values

    Posted Fri August 14, 2020 10:26 AM
    Edited by Andreas Seifert Mon August 17, 2020 05:16 AM
    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
    ------------------------------


  • 2.  RE: New RFE: Improvement in the check for high serial values

    Posted Mon August 17, 2020 02:49 AM
    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
    ------------------------------



  • 3.  RE: New RFE: Improvement in the check for high serial values

    Posted Mon August 17, 2020 05:14 AM
    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
    ------------------------------



  • 4.  RE: New RFE: Improvement in the check for high serial values

    Posted Wed August 19, 2020 05:08 AM

    Hi Eric,

     

     

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

     






  • 5.  RE: New RFE: Improvement in the check for high serial values

    Posted Tue August 18, 2020 02:03 AM
    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
    ------------------------------



  • 6.  RE: New RFE: Improvement in the check for high serial values

    Posted Tue August 18, 2020 02:42 AM
    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
    ------------------------------



  • 7.  RE: New RFE: Improvement in the check for high serial values

    Posted Tue August 18, 2020 04:53 AM
    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
    ------------------------------



  • 8.  RE: New RFE: Improvement in the check for high serial values

    Posted Tue August 18, 2020 06:09 AM
    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
    ------------------------------



  • 9.  RE: New RFE: Improvement in the check for high serial values

    Posted Tue August 18, 2020 09:28 AM
    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


    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.








  • 10.  RE: New RFE: Improvement in the check for high serial values

    Posted Wed August 19, 2020 01:47 AM
    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
    ------------------------------



  • 11.  RE: New RFE: Improvement in the check for high serial values

    Posted Wed August 19, 2020 06:36 AM
    Agreed. The RFE details a needed fix!

    Art

    Art S. Kagel, President and Principal Consultant
    ASK Database Management


    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.