NOTE You can delete a property template only if it is not assigned to a class. If it is assigned, you must remove the property from the class before you can delete it as a property template. See Remove properties from a class for more information.
Delete a property template
Removing a deleted property's database column (Oracle and DB2)
This is accomplished by the following query, because the ColumnDefinition table holds a record of the columns created on the tables, and the PropertyDefinition table holds a record of the classes that have a property that uses the given column definition. When a property is deleted from the base and subclasses (from which the property was originally added) so that it is no longer used as a column on a table, the rowcount in PropertyDefinition will be zero and thus the query will display the orphaned columns.
Issue the following query after substituting for the <TableName> placeholder (i.e., DocVersion, Generic, Container, Subscription, etc.) for those tables which you would like to see unused columns:
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)
Not all tables have to be inspected. However, if you decide to drop columns on a table, you must drop all unused columns. The query will show you all unused columns on the table 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 should be of the form uxy_someproperty, for example “u5c_myString”. Inspect each column returned.
Oracle procedure
For each column on each table identified in Step 2, issue this DDL:
alter table <TableName> drop column <ColumnName>;
This step can be very time consuming if the table has a lot of rows. Be sure to have a very large amount of undo tablespace disk space available. The following Notes may improve performance.
Note 1: With several dropped columns it may help to delete all the unused columns at once during off-hours.
First issue this command for each column and table identified in Step 2)
ALTER TABLE <TableName> SET UNUSED (Column1, Column2, ...);
Then issue this command:
ALTER TABLE <TableName> DROP UNUSED COLUMNS;
This still requires a lot of time to complete this task, but all columns can be dropped at once.
Note 2: On large tables you can reduce the amount of undo logs accumulated by using the CHECKPOINT option which forces a checkpoint after the specified number of rows has been processed:
ALTER TABLE <TableName> DROP UNUSED COLUMNS CHECKPOINT 250;
DB2 procedure
DB2 does not provide direct programmatic drop column support. However, with the DB2 Control Center, you can use the GUI to alter the table. The Alter screen gives the "Remove" option, which will allow you to choose the columns for removal. The Control Center will warn you that it will need to rename the table, recreate the table, copy the data, etc. Please consult the DB2 documentation for details on the drop column support in the Control Center. This procedure can be very time consuming if the table has a lot of rows, and preparations for logging and disk space should be made.
For each for which all unused columns were dropped in Step 3:
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 will delete all rows in the ColumnDefinition table that do not have properties that use them, for the table specified in <tablename>. Thus all the unused columns on a table must be dropped in Step 3.