Informix

Informix

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

  • 1.  Shadow column

    Posted Sun March 22, 2026 11:19 PM
    Edited by ke chen Sun March 22, 2026 11:22 PM

    May I ask how to retrieve a table list for all the user tables that have shadow columns?  (going to retire CDR).

    If after we stop using CDR, would it safe to drop shadow columns (by:  ALTER TABLE table_name DROP CRCOLS;)  from all user tables?

    After drop shadow columns, shall we run update statistics on all those tables?

    thank you for your advice.



    ------------------------------
    ke chen
    ------------------------------



  • 2.  RE: Shadow column

    Posted Mon March 23, 2026 06:15 AM

    Ke:

    There is no harm to running drop crcols or any of the others if the table does not have them, you just get an error. Witness:

     
    > alter table extents drop crcols;
     
    19814: Cannot drop CRCOLS when table does not have replication shadow columns.
    Error in line 1
    Near character position 30

    As far as identifying which tables have shadow columns, most are only indicated by a flag in the systables record for the table. The easiest way would be to run dbschema -ss or myschema and search for the "with ..." clauses (upper case in myschema output, lower case from dbschema). I do not think that dropping shadow columns is an in-place alter so it would be best to drop all that a table has in a single ALTER statement.

    Yes, you should rerun your UPDATE STATISTICS after the alters.

    Art



    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------

    Original Message


  • 3.  RE: Shadow column

    Posted Tue March 24, 2026 03:56 AM

    I think an easy way to find the tables with CRCOLS is to check for the flags:


    select trim(tabname), hex(flags), bitand(flags,8) from systables where bitand(flags,8) = 8;


    Mit freundlichen Grüßen / Kind regards

     

    Gerd Kaluzinski

    Delivery Consultant Data

     

    IBM Technology Expert Labs

    Phone: +49 175 228 1983                         IBM Deutschland GmbH

    Email: gerd.kaluzinski@de.ibm.com               Mies-van-der-Rohe-Straße 6, 80807 München

     

    IBM Deutschland GmbH

    Vorsitzender des Aufsichtsrats: Ivo Körner

    Geschäftsführung: Wolfgang Wendt (Vorsitzender), Dr. Andreas Buchelt, Dr. Frank Kohls, Christine Rupp

    Sitz der Gesellschaft: Ehningen / Registergericht: Amtsgericht Stuttgart, HRB 14562

     




    Original Message


Related Content