IBM FileNet P8, Version 5.2.1            

Removing database columns for deleted properties (Oracle and DB2)

Procedure

To remove the database column that is associated with a deleted property (Oracle and DB2®):

  1. 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.
  2. Start of changeFor 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.End of change
    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.
  3. Start of changeRun a comparison of the list of column names from the data definition language (DDL) for each table of concern with the following selection results:End of change
    select ordinal, column_name from ColumnDefinition
    where lower(dbg_table_name) = lower('TableName') and is_system_owned = 0 order by ordinal

    Start of changeThe 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.End of change

  4. Start of changeDrop 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.
    End of change
  5. Start of changeFor 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.
    End of change
  6. Restart the Content Platform Engine application.


Last updated: October 2015
pr_delete_property_template003.htm

© Copyright IBM Corporation 2015.