IT30874: INFORMIX SENSOR MON_TABLE_PROFILE RETURNS INCORRECT SERIAL VALUEWHEN COLUMN TYPE IS MODIFIED FROM SERIAL TO BIGSERIAL/SERIAL8

APAR status

• Closed as program error.

Error description

• Informix sensor mon_table_profile would have to be corrected
  because it returns the wrong serial value when column type is
  modified from serial to bigserial/serial8 datatype
  
  When column type is getting modified from serial to
  seril8/bigserial the code is still calculating the value based
  on serial data type instead of calculating the value based on
  modified column datatype serial8/bigserial.
  
  Consider this example :
  
  decode(cur_serial8, 1, cur_serial4::int8, cur_serial8) as value
  for the column serialval of the sensor result table.
  
  Insert 18 rows into the table
  
  cur_serial4         cur_serial8       cur_bigserial
  
          18                   1                   1
  
  then  alter the serial4 to bigserial and then:
  
  cur_serial4         cur_serial8       cur_bigserial
  
          18                   1                   18
  
  then insert 2 new rows and then:
  
  cur_serial4         cur_serial8       cur_bigserial
  
          18                   1                   20
  
  When the sensor run with the decode(cur_serial8, 1,
  cur_serial4::int8, cur_serial8)
  
  18 is inserted as serialval in the result table
  mon_table_profile, but the correct value is 20.
  
  
  The assumption in that decode() expressions seems to be that an
  unused serial counter always is 1, so you could tell `the used
  one` from its value being different from 1, yet
   - this only is true if a serial counter never was used on a
  given partition
     - it wouldn`t be reset upon an ALTER TABLE modifying such
  column (to e.g. a different serial type)
   - it is possible to have two serial types in use on a given
  table (serial + serial8, or serial + bigserial), posing yet
  another complication for this query
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Users of Informix Server prior to 14.10.xC7.                 *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to Informix Server 14.10.xC7.                        *
  ****************************************************************
  

Problem conclusion

• Fixed in Informix Server 14.10.xC7.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  INFORMIX SERVER

• Fixed component ID

  5725A3900

Applicable component levels