Shadow column
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
------------------------------
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 1Near 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
------------------------------
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