Configure full text search in Database-Based DMS solutions

To enable full text search in the OpenText PPM Database DMS or OpenText PPM External Database DMS solution,

  1. Connect to OpenText PPM database as SYS DBA, and grant CREATE JOB and CTXAPP privileges to OpenText PPM users by running the oracle_dms_sysdba_user_manual_script.sql script.

    Note: After installation of PPM Center, the SQL scripts shall be present in <PPM_Home>/utilities/database_dms/DatabaseDMS_FullTextSearch_Scripts.zip.

  2. Connect OpenText PPM database with PPM USER (&PPM_SCHEMA), and then create full text search indexes by running the oracle_dms_ppm_user_manual_script.sql script.

  3. Wait for the indexes to be created.

    This can take some time if you have a large number of documents.

  4. Set the value of server configuration parameter DMS_DB_ENABLE_FULLTEXT_SEARCH to true in OpenText PPM from the Administration Console.

    To do so,

    1. From the Administration Console Actions pane, click Administration Task > Application configuration.

    2. Locate the DMS_DB_ENABLE_FULLTEXT_SEARCH parameter, and set its value to true.

    3. Click Save. (No need to restart the PPM Server.)

  5. Verify that the Document Key Words search fields are available in OpenText PPM by going to Search > Projects/Programs/Requests, or any other entity that supports document management.

Caution: Enabling the full text search feature requires creation of all the indexes in the first place. Otherwise it might result in an error whenever an user runs a full text search.

Best Practices and Notes on Indexing

  • There is no perfect setting related to how often the indexes should be refreshed. Refreshing it too often might result in fragmented indexes, while a very long delay between refreshed would cause outdated indexes and might result in users not being able to search and retrieve documents recently added. It is up to DBAs to decide the optimum setting in accordance with your database administration policies and PPM User's expectations. Note that We strongly recommend you not using the SYNC ON COMMIT setting as it would result in significant performance overhead when documents are added to the system as well as severe index fragmentation.

  • The default LEXER used is WORLD_LEXER, which is especially adapted for a multi-lingual document base. If you are only storing documents in a language other than English, then you may use a different LEXER than the WORLD_LEXER used by default. You may use, for example, the CHINESE_LEXER, if all documents stored are in Chinese. You are free to use whatever LEXER that better fits your needs.

  • The multi-column index on metadata of both TIP and HISTORY tables is created on the FULL_TEXT_META column. A trigger is already created that updates this column whenever one of the indexed column is updated. This is required to correctly update the index.

  • You may want to configure a significant amount of indexing memory when you create the indexes in case you have a very large amount of documents to index. For more information, see Oracle documentation related to Oracle TEXT indexing performance.

  • Oracle TEXT indexes can be very large if there is a large amount of text intensive documents to index (text files, log files, XML, and so on). You may want to make sure that the tablespace hosting these TEXT indexes can accommodate such an amount of data.

  • For a list of all the file formats supported for indexing by Oracle TEXT, see http://download.oracle.com/docs/cd/E11882_01/text.112/e16593/afilsupt.htm.

  • OpenText PPM Database DMS Full Text Search feature relies on Oracle TEXT only. As a result, the performance and results of PPM documents full text search when using OpenText PPM Database DMS solely relies on the settings you used when creating these indexes.