IC72796: ANY ATTEMPT TO ACCESS A TABLE AFTER RENAMING ITS COLUMN THAT HAS A CHECK CONSTRAINT WITH CAST (::) OPERATOR GIVES THE ERROR -676

APAR status

• Closed as program error.

Error description

• If you run the 'RENAME COLUMN table.old_column_name TO
  new_column_name' command for a column which has
  a check constraint with cast operator ('::') - like
  "oldcol::smallint < 100" - the rename operation is
  successfull, however any subsequent attempt to access this table
  (SELECT, UPDATE, DELETE, INSERT, ALTER, DROP)
  returns error -676: "Invalid check constraint column"
  

Local fix

• Delete the appropriate records from 'sysconstraints' and
  'syschecks' system catalogs as in following example:
  
  Table definition:
    create table testtab (
      oldcol char(2) check (oldcol::smallint < 100)
    );
  
  Rename column command:
    rename column testtab.oldcol to newcol;
  
  Identifying the records in 'sysconstraints' and 'syschecks'
  catalogs:
  
  select * from sysconstraints where tabid = ((
    select tabid from systables where tabname="testtab" ))
  
  constrid    1
  constrname  c100_1
  owner       informix
  tabid       100
  constrtype  C
  idxname
  collation   en_US.819
  
  select * from syschecks where constrid=1;
  
     constrid type  seqno checktext
  
            1 T         0 (newcol ::smallint < 100 )
            1 B         0 OS41NEMxAAAAAAEAOQASgAAAAAAAAAAA
            1 B         1 AAAAAAAAAAAAAAIAKwBWgAAAAAAAAAAA
            1 B         2 AAAAAAAAAAArAAAAAQApAFiAAAAAAAAA
            1 B         3 gAAAAAEAAAAAACkAAAAAAAAAAAAAAAEA
            1 B         4 KAABdAAAAAAAAAAAAAAAAAAAAAABAAEA
            1 B         5 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
            1 B         6 AAAAAAAAAAAAAAAAAAAAADYAKIAAAAAA
            1 B         7 AAAAAAAAAAAAAAAANgACAAAAAAAAAGQl
            1 B         8 bS8lZC8laVkAUgIAAAABAAAAAwAAAAAA
            1 B         9 njcAAAAA
  
  Deleting the records:
    delete from syschecks where constrid=1;
    delete from sysconstraints  where constrid=1;
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Costumers who rename table columns which have a constraint   *
  * with a cast                                                  *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * The costumer has a table with a column that has a            *
  * constraint. The constraint expression itself contains a      *
  * cast. When the costumer tries to re-name the column, any     *
  * further operations on the table return error -676.           *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to IDS.11.50.xC9N037 or later.                       *
  ****************************************************************
  

Problem conclusion

• The case where a cast is included in the column constraint was
  not handled correctly during column rename. This has now been
  fixed. Costumer should upgrade to IDS.11.50.xC9N037 or later.
  

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

  IBM IDS ENTRP E

• Fixed component ID

  5724L2304

Applicable component levels

• RB15 PSY

     UP