You can delete a property template only if the property is not assigned to a class. If the property is assigned, you must remove the property from the class before you can delete the property template. See Remove properties from a class for more information.
To delete a property template
To remove the database column associated with a deleted property (Oracle and DB2)
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 have to 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 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 returned.
For each column on each table identified in Step 2, issue this DDL:
alter table TableName drop column ColumnName;
This step can be time consuming if the table has many rows. Be sure to have a large amount of undo table space disk space available. The following notes might improve performance.
NOTE With several dropped columns it can 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 command still requires much time to complete this task, but all columns can be dropped at once.
NOTE On large tables you can reduce the amount of undo logs accumulated by using the CHECKPOINT option. The CHECKPOINT option forces a checkpoint after the specified number of rows has been processed:
ALTER TABLE TableName DROP UNUSED COLUMNS CHECKPOINT 250;
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 allows you to choose the columns for removal. The Control Center warns you that it must rename the table, re-create the table, copy the data, and so on. Consult the DB2 documentation for details on the drop column support in the Control Center. This procedure can be time consuming if the table has many rows, and preparations for logging and disk space must 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 deletes 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.