Procedure
To remove the database column that is associated with
a deleted property (Oracle and DB2®):
- Stop the Content Platform Engine application
to ensure that there is no user activity on the object store. The
object store database is altered by this procedure and it is important
that no users are accessing the database now.
For object stores that were created before Content Platform Engine V5.0, use the following
query in the appropriate database tool to display all the database
columns that are no longer used by any properties. If the object stores
were created on Content Platform Engine V5.0
or later, skip this step.
select column_name from ColumnDefinition
where lower(dbg_table_name) = lower('TableName') and is_system_owned = 0
and object_id not in (select column_id from PropertyDefinition where column_id is not null)
Where
TableName is
the name of the related table, for example, DocVersion, Generic, Container,
Subscription.
Not all tables must be inspected. However, if
you decide to drop columns on a table, you must drop all unused columns.
The query shows you all unused columns on the table that is designated
by TableName.
Example:
select column_name from ColumnDefinition
where lower(dbg_table_name) = lower('Generic') and is_system_owned = 0
and object_id not in (select column_id from PropertyDefinition where column_id is not null)
Important: Each column reported must be of the form uxy_someproperty,
for example u5c_myString. Inspect each column that is returned.
Run a comparison of the list of column names
from the data definition language (DDL) for each table of concern
with the following selection results:
select ordinal, column_name from ColumnDefinition
where lower(dbg_table_name) = lower('TableName') and is_system_owned = 0 order by ordinal
The difference between the list of column names from
the DDL with the selection results and the results of step 2 (as needed)
identifies any unused columns. Starting with Content Platform Engine V5.0, when a property
definition is removed, the associated record in the ColumnDefinition
table is removed. In this case, the DLL must be examined as well.
Drop all of the unused columns for the tables
that were identified in steps 2 and 3. - Oracle procedure
- For each column on each table that is identified, run this DDL:
alter table TableName drop column ColumnName;
Important: Running this step can take a long time and even fail if the table has many
rows. Be sure to have a large amount of undo table space disk space available. For larger tables,
the following method is safer and improves performance.
- With several dropped columns or for a larger table, it is better to delete all of the unused
columns at one time during off-hours by using a checkpoint.
- First, issue this command for each column and table that is identified in step
2:
ALTER TABLE TableName SET UNUSED (Column1, Column2, ...);
- Then, run this
command:
ALTER TABLE TableName DROP UNUSED COLUMNS CHECKPOINT 250;
- This command still requires much time to complete this task, but
all of the columns can be dropped at one time.
Note: On large tables,
the CHECKPOINT option reduces the number of undo logs that are accumulated.
The CHECKPOINT option forces a checkpoint after the specified number
of rows is processed:
- DB2 procedure
- DB2 does not provide
direct programmatic drop column support. However, with the DB2 Studio, you can use the
GUI to alter the table. The Alter screen gives the Remove option
to choose the columns for removal. The Studio warns you that it must
rename the table, recreate the table, copy the data, and so on. Consult
the DB2 documentation
for details on the drop column support in the Studio. This procedure
can be time consuming if the table has many rows, and preparations
for logging and disk space must be made.

For object stores that were created before Content Platform Engine V5.0, delete any affected
rows in the ColumnDefinition table, for each table of concern. Affected rows are those rows that were found in the ColumnDefinition
table but do not exist in either the DDL or the PropertyDefinition
table as determined in step 2 of this topic.For each table in which
all of the unused columns were dropped in step 4, enter the following
command:
delete from ColumnDefinition where lower(dbg_table_name) = lower('TableName')
and is_system_owned = 0
and object_id not in (select column_id from PropertyDefinition where column_id is not null)
Important: The delete command deletes all of the rows in the
ColumnDefinition table that do not have properties that use them,
for the table that is specified in TableName. Thus
all the unused columns on a table must be dropped in step 4.

- Restart the Content Platform Engine application.