Add or drop specialized columns

You can add or drop specialized columns when you alter a table. Most specialized columns are shadow columns that support Enterprise Replication or high availability.

This syntax fragment is part of the ALTER TABLE statement.

Adding or dropping specialized columns

1 
2.1 ADD
2.1 DROP
2.1 CRCOLS
2.1 ERKEY
2.1 REPLCHECK
2.1 ROWIDS
2.1 VERCOLS

Usage

The following keywords control one or more specialized columns.

CRCOLS
The ADD CRCOLS keywords create shadow columns, cdrserver and cdrtime, that Enterprise Replication uses for conflict resolution. If Enterprise Replication is active while you are altering the table with the ADD CRCOLS clause, you must first put the table in alter mode with the cdr alter command. Altering a table to add the CRCOLS shadow columns can be a slow alter operation, if any of the table columns have data types that require a slow alter. Slow alter operations require disk space at least twice the size of the original table plus log space. For information on the performance implications of ALTER TABLE statements, see Altering a table definition.

Use the DROP CRCOLS keywords to drop the cdrserver and cdrtime shadow columns. You must stop replication before you drop the cdrserver and cdrtime shadow columns.

For more information, refer to Using the WITH CRCOLS Option.

ERKEY
The ADD ERKEY keywords create shadow columns, ifx_erkey_1, ifx_erkey_2, and ifx_erkey_3, that Enterprise Replication uses in place of a primary key. If Enterprise Replication is active while you are altering the table with the ADD ERKEY clause, you must first put the table in alter mode with the cdr alter command.Altering a table to add the ERKEY shadow columns is a slow alter operation.

Use the DROP ERKEY keywords to drop the ifx_erkey_1, ifx_erkey_2, and ifx_erkey_3 shadow columns.

For more information, see Using the WITH ERKEY Keywords.

REPLCHECK
The ADD REPLCHECK keywords create the shadow column, ifx_replcheck, that you can create an index on, along with your primary key, to speed the processing of consistency checking with Enterprise Replication. If Enterprise Replication is active while you are altering the table with the ADD REPLCHECK clause, you must first put the table in alter mode with the cdr alter command.Altering a table to add the ifx_replcheck shadow column is a slow alter operation, which requires disk space at least twice the size of the original table plus log space.

Use the DROP REPLCHECK keywords to drop the ifx_replcheck shadow column.

For more information, see Using the WITH REPLCHECK Keywords.

ROWIDS
Important: This is a deprecated feature. The query optimizer might not use an index scan when explicit rowid shadow columns are defined on fragmented tables. When you create new applications, use primary keys as a method of row identification instead of using rowid values.
Use the ADD ROWIDS keywords to add a column called rowid to a fragmented table. (Fragmented tables do not contain the hidden rowid column by default.) When you add a rowid column, the database server assigns a unique number to each row that remains stable for the life of the row. The database server creates an index that it uses to find the physical location of the row. After you add the rowid column, each row of the table contains an additional 4 bytes to store the rowid value. Use the ADD ROWIDS clause only on fragmented tables. In nonfragmented tables, the rowid column remains unchanged.

The DROP ROWIDS keywords can drop a rowid column that you added (with either the ALTER TABLE or ALTER FRAGMENT statement) to a fragmented table.

VERCOLS
The ADD VERCOLS keywords create shadow columns, ifx_insert_checksum and ifx_row_version, that are used to support secondary server updates. Altering a table to add row versioning columns is a fast alter operation.

Use the DROP VERCOLS keywords to drop the ifx_insert_checksum and ifx_row_version shadow columns.

For more information, see Using the WITH VERCOLS Option and Row versioning.

Example: ADD CRCOLS

In the following example, the cdrserver and cdrtime shadow columns are added to the customer table: 

ALTER TABLE customer ADD CRCOLS;

Example: ADD ERKEY

The following example adds the ifx_erkey_1, ifx_erkey_2, and ifx_erkey_3 columns to the customer table: 

ALTER TABLE customer ADD ERKEY;

Example: ADD REPLCHECK and DROP REPLCHECK

In the following example, the ifx_replcheck shadow column is added to the customer table: 

ALTER TABLE customer ADD REPLCHECK;

The following example drops the ifx_replcheck column from the customer table: 

ALTER TABLE customer DROP REPLCHECK;

Example: DROP ROWIDS

The following example drops the rowid column from the frag1 table: 
ALTER TABLE frag1 DROP RWIDS;

Example: ADD VERCOLS

In the following example, the ifx_insert_checksum and ifx_row_version, shadow columns are added to the customer table: 
ALTER TABLE customer ADD VERCOLS;