Content Platform Engine, FileNet P8 Content Search Engine, Version 5.2.1 Database: Oracle

Converting the Oracle DATE data type to the TIMESTAMP data type (upgrading from version 4.5.1)

If you are upgrading Oracle-based Content Engine data as part of an upgrade of FileNet® P8 from version 4.5.1, the auto-upgrade converts the Content Engine DateTime property columns from the Oracle DATE data type to the Oracle TIMESTAMP data type. However, if there is a function-based index that involves the DATE data type, then auto-upgrade will not be able to perform this conversion. In this case, you must drop the index, manually convert the DATE data type to the TIMESTAMP data type, and then re-create the original index in the Oracle database.

About this task

Versions of Oracle-based Content Engine data prior to 5.0.0 use the Oracle DATE property for the Content Engine DateTime property. As of version 5.0.0, the Oracle TIMESTAMP property replaces the DATE property.

As part of the auto-upgrade of Content Engine data, table columns defined by the DATE property are automatically converted to the TIMESTAMP property, even if there is an index on the DATE property. However, the conversion fails on function-based indexes, such as the descending (DESC) keyword, that involve the DATE property.

Because it is not feasible to resolve this conversion failure within the auto-upgrade framework, only a manual procedure can convert DATE columns with function-based indexes to TIMESTAMP columns.

If any of the following conditions apply, your database administrator needs to complete the manual procedure:

You use the Content Engine auditing feature, and the Event table contains more than 500,000 rows.
If you do not complete the manual procedure, the system index I_EVENT48 on the create_date column in the Event table continues to work; however, your configuration is unsupported until you manually convert the column.
The following example shows a message from the P8 system error log that indicates the Event table overflow:
```
2012-05-31T01:49:43.005Z 7F227F22 ENG FNRCE0000E - ERROR 
ObjectStoreUpgrade(My451ObjectStore) DBA should manually drop
indices I_Event48 and I_Event83, convert date column to timestamp
and re-create them on Event table.
```
Although the error message refers to both I_EVENT48 and I_EVENT83, only I_EVENT48 must be dropped and re-created; a procedure is provided below.
You have a customer-defined DATE property that is part of a function-based index.
If you do not complete the manual procedure, the upgraded system is fully functional. Date properties work as before, and queries operate as before, with one exception: A function-based, customer-defined index for which the Date column is the first key (for example, a DBA-created index on uxy_shipmentDate DESC) is unusable, and an index lookup in a query plan is not retained until you manually convert the customer-defined property.

A check of the P8 system error logs for data type conversion errors will confirm the need to complete the manual procedure. In all conditions, it is best practice to review the logs after the auto-upgrade completes. The following example shows a message from the P8 system error log that indicates the need to complete the manual procedure:

"2010-07-13T00:29:43.787Z 68416841 ENG  FNRCE0000E - ERROR ObjectStoreUpgrade
(DaphneStore) Date to Timestamp column conversion failed for column modify_date on 
table DocVersion. The most likely reason is oracle error 30556: functional index is 
defined on the column to be modified, in which case DBA should manually drop index, 
convert date column to timestamp and re-create index."

Procedure

To manually convert the DATE data type to the TIMESTAMP data type:

If you have not already done so, remove the V8Compatibility JVM switch workaround. If your system uses Oracle 11g and you had to roll back to the Oracle 10g JDBC driver for the workaround, also remove the JDBC driver downgrade procedure workaround. Use the procedures in the techdoc Enabling Oracle Date Index Use in the FileNet Content Engine (Techdoc 1397282).
To convert the DATE data type to the TIMESTAMP data type in the Event table, run the following SQL commands. Note that creating an index might take a long time if there are millions of entries in the Event table. As mentioned above, only I_EVENT48 must be dropped and re-created.
```
DROP INDEX I_Event48
ALTER TABLE Event MODIFY create_date TIMESTAMP
CREATE INDEX I_Event48 ON Event (source_object_id, create_date DESC) 
```
To convert customer-defined DATE table columns to the TIMESTAMP data type, run the following SQL commands for each function-based index. Creating an index might take a long time if there are millions of entries in the table containing the DATE column.
```
DROP INDEX <index name>
ALTER TABLE <table name> MODIFY <column name> TIMESTAMP
CREATE INDEX <index name> ON <table name>(column names> <modifiers>)
```