Using an Application Engine Maintenance Program

Real-time indexing uses a staging table to store the keys of the application transactions to be indexed in the search engine. The transaction data is removed from the staging table as soon as the transaction is indexed. Based on the number of search definitions enabled for real-time indexing and the volume of transactions (online or batch), frequent data inserts and deletes take place in the staging table. This can eventually cause table fragmentation and over a period of time the performance of real-time indexing may slow down due to the fragmentation as compared to the initial performance. Periodic maintenance of the staging table will restore the performance of the real-time indexing process.

PeopleSoft provides an AE program, named PTRTI_TRUNC, which performs the maintenance activities on the staging table based on the value you set for the Real Time Indexing Threshold property on the Search Options page. The program obtains a lock on the staging table, backs up the data, and then truncates the table. After a successful truncate action, the backed-up data is copied to the staging table. The successful run of the program depends on the activities on the staging table, that is, while locking and truncating the table, data insert should not take place on the table. If data insert takes place on the table after the lock on the table is obtained,the table is automatically unlocked to avoid any data loss. Therefore, the AE maintenance program should be run when no transactions are being inserted, deleted, or modified on the table.

For example, an administrator can plan to run the AE maintenance program after the successful completion of the application batch update. An administrator can schedule the AE program as a recurring job at regular intervals and the job, on each iteration, performs the maintenance activity based on the availability of the table lock. However, you should note that the AE program only performs the table truncate activity if the table crosses the Real Time Indexing Threshold value specified on the Search options page. The real-time indexing process keeps track of the maximum count of the staging table rows and if the row count crosses the threshold value, the AE maintenance program is run. If the row count does not cross the threshold value, the AE program exits from the current run without proceeding for lock and truncate actions.