Using Real-Time Indexing

PeopleSoft Search Framework uses data that is indexed in the search engine for search and analytics, so it is recommended that the data is indexed in real time to provide current search and analytics. This would eliminate stale data in the search engine indexes and ensure that discrepancies do not exist between the data in the PeopleSoft database and the indexed data in the search engine.

Any search definition with the source type of query or connected query can be configured for real-time indexing. Refer to the PeopleSoft Application Fundamentals documentation <for your product line> for a list of delivered search definitions for which real-time indexing can be configured.

Search Framework supports real-time indexing through SQL triggers and uses set-based processing.

Important! Oracle does not deliver search definitions enabled for real-time indexing automatically. Therefore, you should enable search definitions for real-time indexing based on your business requirements. In addition, some search definitions need to be certified before being turned on. See the Application Fundamentals Online Help <for your product> for more information.

Oracle delivers two search definitions, PTSF_RECENTMENU and PTSF_RECENT_KW, for providing suggestions in search that are configured for real-time indexing, but you have to enable real-time indexing for these two search definitions.

See Working with Suggestions Search.

This topic discusses the following:

  • Set processing in real-time indexing.

  • Prerequisites for real-time indexing.

  • Steps to enable real-time indexing.

  • Use the Configure Real Time Indexing page.

  • Remove a trigger.

  • View error details.

  • Migrate real-time indexing data using ADS definition.

  • Troubleshooting.

  • Use an Application Engine maintenance program.

Set-based processing refers to processing groups, or sets of rows, at one time rather than processing each row individually.

The real-time indexing process ensures that data is updated on the search server as soon as an application transaction is saved. Based on the volume of data under processing in the real-time indexing queue, the transaction update may appear to be real time or near real time.

Search Framework implements set-based processing in real-time indexing as shown in the following diagram.

The following diagram illustrates the process flow of the real-time indexing process.

 Real time indexing process flow

Real-time indexing uses database triggers as the initializing point for the communication to the search server. As the data is inserted, updated, or deleted, the database trigger associated with the application record inserts a row in the real time-indexing staging table. The staging table acts as an interim data holder by storing the keys of the transaction.

Note: When batch processing is enabled for a search definition, updated data is not pushed to the real-time indexing staging table for the search definition. For more information, see Turning Off Real-Time Indexing During Batch Processing.

A dedicated Process Scheduler process for real-time indexing polls the staging table at regular intervals for any data to process. You can configure the number of processes in the Process Scheduler configuration properties file. By default, only one process is initiated. You may scale up the number of processes by changing the maximum number of instances. However, the current design is limited to enable real-time indexing on one domain only. Enabling real-time indexing on more than one domain causes the same dataset to be picked by both processes resulting in duplicate processing.

Real-time indexing processes the transactions stored in the staging table as a set. The size of the set is a configurable parameter in the Search Options page, which can be specified as per the available resources in the Process Scheduler server. A set can contain just one transaction or it can contain the maximum number of transactions as specified in the Real Time Indexing Set Size property based on the available data at any point in time.

Real-time indexing again processes each set for a specific search definition and begins the data retrieval process for each search definition. After the data is collected and formatted to a JSON structure, it is transferred to the search server using Direct Transfer.

Note: When an application batch program updates a large volume of data in an insert or update action, you can expect delay in updating the data to the search server due to the relatively large volume of data as compared to the online transactions with low volume. Therefore, PeopleSoft recommends that you use the real-time indexing batch switch for search definitions with batch programs having large volume indexing.

In most of the search definitions enabled for real-time indexing, you may not need to schedule incremental indexing. However, indexes that have data with effective date changes may require the incremental indexing schedules for the actual data synchronization to take place. A change on the effective dated rows does not initiate a trigger, therefore such search definitions should be indexed using the existing indexing methods. While real-time indexing ensures data synchronization based on transaction updates, incremental indexing updates the indexed data (from prior indexing schedules) to the current date.

Note: You should periodically run the AE maintenance program (PTRTI_TRUNC) of real-time indexing to ensure that the real-time indexing staging tables are de-fragmented after running large volume batch update for better performance. For a description of the AE maintenance program, see Using an Application Engine Maintenance Program.

Before you can begin configure real-time indexing for a search definition, you should configure the following elements or verify that these elements are set correctly:

  • Set the Real Time Indexing property in Quick Configure Menu.

  • Configure PSRTISRV server process.

  • Create an application class for any preprocessing or post processing.

    Application class may be provided by PeopleSoft for any delivered indexes.

Setting the Real Time Indexing Parameter in PSADMIN Quick Configure Menu

You should set the Real Time Indexing parameter on the Process Scheduler domain.

To set the Real Time Indexing parameter:

  1. Run PSADMIN and choose the Process Scheduler domain.

  2. Select 4) Configure this domain.

    To configure the domain, the domain needs to be shut down.

  3. Enter y to the question: Do you want to continue (y/n).

  4. Toggle 6) Real Time Indexing to set it to Yes.

  5. Select 7) Load config as shown.

  6. Restart the domain.

Configuring the PSRTISRV Server Process

The real-time indexing server process, PSRTISRV, starts when the Process Scheduler domain is booted up. The number of RTI server processes can be configured in the Process Scheduler section of the configuration file (psprcs.cfg). The default is set to 1 instance.

See PSRTISRV Section

You should configure the following RTI server process parameters on the Search Options page (select PeopleTools > Search Framework > Search Admin Activity Guide, from the left panel choose Administration, and then choose Search Options):

  • Index Refresh Interval

  • Heartbeat Interval

  • Real Time Indexing Set Size

  • Real Time Indexing Threshold

For description of these parameters, see Managing General Search Options.

Creating an Application Class

For full or incremental indexing of a search definition, any preprocessing is performed by an Application Engine program. In the real-time indexing process, you should define an application class to perform preprocessing and post processing. The application class should extend the PTRTIPKG:PTRTI application class. The PTRTI class contains the Process and PostProcess methods, and you should extend these methods with your application class to perform preprocessing and post processing tasks respectively.

See PTRTI Class.

To ensure that the real-time indexing process runs smoothly, PeopleSoft suggests the following sequence of steps.

  1. On the Configure Real Time Indexing page,

    1. Select the Enable Real Time Indexing check box.

    2. Verify that the metadata is populated correctly and ensure that you specify the required trigger records.

    3. Save the real-time indexing configuration.

    4. Create the trigger.

  2. Run incremental index for the search index for data synchronization. Perform this step to index records that were added before real-time indexing was initialized.

The selected search definition is now configured for real-time indexing. When data is inserted, modified, or deleted in the database for the search index, a SQL trigger runs to initiate real-time indexing of data in the search engine.

Note: Search definitions with queries using set operators like UNION, INTERSECTION etc. are not supported for real-time indexing. Real-time indexing requires equivalent queries without set operators.

Use the Configure Real Time Indexing page to enable real-time indexing for a specific search definition and to specify any additional information required for real-time indexing of the search definition.

Access the Configure Real Time Indexing page by selecting PeopleTools > Search Framework > Real Time Indexing > Configure Real Time Indexing.

The search page displays search definitions with the source type as query and connected query from the default search instance. The search page also provides a comprehensive view of the deploy status retrieved from the Deploy Search Definition page, and the real-time indexing configuration status of the search definitions, enabling you to take the necessary action on a search definition.

Select a search definition to display the real-time indexing information. The search definition metadata is imported including security and attachment configurations.

This example illustrates the fields and controls on the Configure Real Time Indexing page. You can find definitions for the fields and controls later on this page.

 Configure Real Time Indexing page

Field or Control

Description

Enable Real Time Indexing

Select the check box to enable real-time indexing for the search definition.

Configure Batch

Select the check box to configure batch programs, with a specific Process Type and Process Name, from an available list of batch programs.

See Turning Off Real-Time Indexing During Batch Processing.

Trigger SQL

After reviewing or modifying the search definition, you should save the search index. The Trigger SQL link appears only when you select Save on the page. Then, you should create a trigger. A trigger record is generated at runtime. Contact your database administrator to implement triggers on the database.

Note: PeopleSoft RTI trigger names have the prefix SFA and suffix TR. For example, SFA_PT_PORTAL_MRU_TR.

Any of the following ways can be used to create a trigger:

  • Select the Trigger SQL link to download the .sql files for the supported database platforms. Run the downloaded trigger SQL in a SQL tool to create triggers.

  • Open the trigger record in Application Designer, and build the record using the Create Trigger option.

  • Run the Datamover command: CREATE_TRIGGER <recordname>, where recordname is the trigger record.

For more information on trigger records, see the section on Trigger Records later in this topic.

Reload

The Reload link appears only when you select Save on the page.

If a search definition is updated after configuring it for real-time indexing, you should use the Reload link to import the updates to real-time indexing.

Note: After completing the reload action, ensure that you save the real-time indexing configuration, and then restart the Process Scheduler domain.

Warning! The reload operation may clear the existing configurations for application class and trigger records. You may have to redo these configurations after clicking the Reload link.

Data Source

Field or Control

Description

Query/Connected Query

The system automatically selects Query/Connected Query as data source for those search definitions that are based on query or connected query.

Application Class

The system automatically selects Query/Connected Query and Application Class as data sources for those search definitions that contain preprocessing or post processing Application Engine program.

When both Query/Connected Query and Application Class are auto populated as data sources, the real-time indexing process runs the application class method first and then runs SQL query. If query uses a temporary table, the application class method should insert the data to the temporary table.

You can choose to use only the application class as the data source rather than Query/Connected Query. In this case, the application class methods perform the necessary preprocessing or post processing and also set the values for all search attributes of each search document to index. The real-time indexing process then creates the JSON document and uses the Direct Transfer technology to index the data.

See PTRTI Class.

Activity Guide

Reserved for future use.

Data Generator Application Class

The Data Generator Application Class section is displayed when application class is selected as the data source.

Field or Control

Description

Package Name

Specifies the root package where the application class is created.

You should ensure that the extended application class exists in the database.

Path

Species the path, represented by a colon (:).

Class ID

Choose the relevant application class.

Process

Set the Process option to Yes if you want to perform any validation or preprocessing and to defer or discard processing.

When you set this option to Yes, you should extend the Process method of the delivered application package PTRTIPKG:PTRTI.

PTRTI Class Methods

Post Process

Set the Post Process option to Yes if you want to perform any validation or post processing.

When you set this option to Yes, you should extend the PostProcess method of the delivered application package PTRTIPKG:PTRTI.

PTRTI Class Methods

General

Search definition metadata is imported and displayed in the Title, Summary, URL, and Last Update Date Time fields.

You cannot edit these fields.

Trigger Records

A trigger record is necessary for real-time indexing. Therefore, you must ensure that the Record field contains a trigger record. When a search definition is based on Query or Connected Query, the system checks the query to try and determine the best trigger record and pre-populates the value for you.

The keys of the trigger record (transaction record) should exist in the primary record of the parent query. If trigger records are pre populated, you should retain these trigger records. You should add a trigger record only if no trigger record is given.

Optionally, you can add more trigger records but doing so will affect the performance. Also, keeping triggers on child records may cause undesired data insertions to the real-time indexing staging table, which may increase the processing time.

Field or Control

Description

Record

The Record field is pre populated based on the parent record used in Query/Connected Query of the search definition where the Last Modified Date Time is configured. If it is a view, the first record used in that view is auto selected as transaction record to create a trigger. However, you can change it to any other record in that view.

The real-time indexing process recognizes a transaction only when the trigger record inserts a row for the transaction. Hence, the trigger record should be decided based on this condition. Adding multiple trigger records can cause the same data to be processed multiple times to the search engine.

Temp Table

Select this check box if the search definition’s Last Modified Date Time field is using a temporary table.

Note: Temp Table and Transaction Record or Record fields are displayed for search definitions that have data source as Application Class.

Transaction Record

Transaction Record field is used only when the Record field contains a view or a temporary table.

Attachments

If a search definition contains attachments, the Include Attachments check box is displayed.

Field or Control

Description

Include Attachments

Use this check box to either include attachments or exclude attachments from real-time indexing.

If a search definition contains attachments, it is ideal to include attachments in real-time indexing. However, if you observe that attachments are causing delay in real-time indexing, you can choose to exclude attachments. In this case, you can use incremental indexing because in incremental indexing attachments are also indexed.

If an index does not contain attachments, the Include Attachments check box is not displayed.

Search Attributes

The Search Attributes section displays metadata imported from the search definition, and it cannot be edited here.

Application developers can use this mapping from the search definition page to know what attributes should be loaded in the application class.

While processing high volume batch updates for search definitions, real-time indexing can cause a sudden increase in the number of entries to process in the real-time indexing staging table. This can, in turn, cause the database table fragmentation to increase and eventually slow down the performance. Therefore, for large volume batch updates, it is recommended that real-time indexing be temporarily suspended and changes to the search index be updated through incremental indexing.

Note: When real time-indexing is off, the staging table will not receive any data for both online and batch programs.

After the batch programs are completed, incremental indexing for a search definition will take place at the scheduled time. When incremental indexing starts, real-time indexing is automatically enabled for the search definition.

Enabling Batch on Real Time Indexing

Use the Configure Batch option on the Configure Real Time Indexing page to enable a batch program.

When you select the Configure Batch option for a search definition, you can access a list of batch programs, delivered by Applications for the search definition, that can cause high volume updates to the trigger records. You can choose additional batch programs from the drop-down, but these must be coded to use the batch switch. Oracle does not recommend adding batches other than the ones delivered for the search definition.

Select batch programs by process type and process name to turn off real-time indexing for them.

Oracle delivers functions that application developers can use to programmatically turn off real-time indexing and set the document count value. However, these functions can only be used with Application Engine programs. See Built-in Functions.

Programs using SQL or COBOL need to perform a direct SQL execution to turn off real-time indexing.

Following is an example to turn off real-time indexing in COBOL and SQR:

MERGE INTO PS_PTSF_RTI_STATUS d 
USING(SELECT :1 PTSF_INDEX_NAME from dual) 
ON(d.PTSF_INDEX_NAME = s.PTSF_INDEX_NAME) 
WHEN MATCHED THEN UPDATE SET d.PTRTISTATUS = 0 
WHEN NOT MATCHED THEN INSERT VALUES(:1, 0)));

This example illustrates the fields and controls on the Configure Real Time Indexing page with the Configure Batch option selected:

 Configure Real Time Indexing with Configure Batch

Field or Control

Description

Document Count

The document count is a threshold beyond which the real-time indexing process should be disabled during batch runs. Large batch jobs with records beyond this count should have real-time indexing disabled.

The default is set to 0. 0 means real-time indexing is not turned off.

Note: This count will be used by application developers as a deciding factor to invoke the TurnOffRTI function. Applications will look for a number greater than 0 to compare to the record count for batch jobs to determine whether or not to disable real-time indexing.

Process Type

Select the process type.

Process Name

Select the process name.

Honor Batch Switch

Set the Honor Batch Switch option to Yes if you want the Real Time Indexing Switch to honor the process (batch program). When the Honor Batch Switch option is set to No, the request to turn off real-time indexing for the batch program will be ignored.

Note: Applications will deliver the configuration with the Honor Batch Switch turned off. You must turn it to Yes if you have large batch runs and you must also set the Document Count.

Using the Real Time Indexing Switch Page

The Real Time indexing Switch page displays the current real-time indexing status of the listed search definitions. Administrators can use this page to check the real-time indexing status and turn it on or off only in scenarios where real-time indexing was not automatically turned on or off. Oracle recommends that administrators exercise caution before turning real-time indexing on or off on this page.

This example illustrates the fields and controls on the Real Time Indexing Switch page:

 Real Time Indexing Switch Page

Use the Excluded Search Definitions page to hide a search definition in the Real Time Indexing Configuration page.

Note: Applications will deliver indexes to be excluded if the index is normally updated externally and thus is not eligible for real-time indexing.

This example illustrates the fields and controls on the Excluded Search Definitions page:

 Excluded Search Definitions page

You may need to remove a trigger if you plan to remove real-time indexing trigger from a specific transaction record. To remove an existing trigger, you should drop the trigger. However, if you drop a trigger, real-time indexing can no longer update the associated search index.

Contact your database administrator to drop a trigger.

Complete these steps to drop a trigger:

  1. Open an SQL tool.

  2. Run the command: DROP TRIGGER <trigger_name>;

    where <trigger_name> is the trigger that you want to drop.

    For example, DROP TRIGGER SFA_PT_PORTAL_MRU_TR;

    PeopleSoft RTI trigger names have the prefix SFA and suffix TR.

Use the View Error Details page to check if any of the indexes configured for real-time indexing encounter an error during the real-time indexing process and to take required action.

Access the View Error Details page by selecting PeopleTools > Search Framework > Real Time Indexing > View Error Details.

This example illustrates the fields and controls on the View Error Details page. You can find definitions for the fields and controls later on this page.

 View Error Details page

Field or Control

Description

Index Name

Specifies the search index in which an error has occurred.

Request

Select the View Request link to view the real-time indexing request information. Selecting the View Request link downloads a .json file.

Response

Select the View Response link to view the response from the search server. You can use the response information to identify the error and take an action to rectify the error. Selecting the View Response link downloads a .json file.

Submit

After reviewing the response, you may choose to run the real-time indexing process again.

Choose the search indexes that you want to run the real-time indexing process again, and then select the Submit button. The Submit action recreates the .json file before sending to the search engine.

Oracle delivers an ADS definition to copy or migrate real-time indexing metadata from one environment to another.

For information on using ADS, see Understanding ADS Projects.

ADS Definition for Real-Time Indexing

ADS definition for real-time indexing —PTSF_REALTIMEINDEXING.

PTSF_INDEX_GROUP

Group

Details

Group Name

Group for PTSF_INDEX

Group Description

Merge group for Search Index RTI metada

Group Member

  • PTSF_INDEX.PTSF_ATTACHMENT

  • PTSF_INDEX.PTSF_RTI

Group Properties

Merge configurations

Tables for PTSF_REALTIMEINDEXING

Table

Description

PTSF_INDEX

Stores search definition details, such as connected query, query, application class, and so on.

PTSF_INDEXATT

Stores attachment information.

PTSF_INDEXATT_S

Stores SQL for attachments.

PTSF_INDEXCQRY

Stores parent child mapping information.

PTSF_INDEXDSEC

Stores document security.

PTSF_INDEXQRY

Stores queries in the index.

PTSF_INDEXRCFLD

Stores attribute information.

PTSF_RELLANGQRY

Stores related language information.

PTSF_INDEXTRREC

Stores trigger records for real-time indexing.

PTSF_INDEXTRFLD

Stores trigger non-key fields for real-time indexing.

PTSF_INDEX_BODY

Stores summary information.

PTSF_INDEX_DQ

Stores delete query information.

PTSF_INDEX_LUD

Stores last updated datetime details.

PTSF_INDEX_URL

Stores drilling URL information.

To troubleshoot any errors that you may encounter while real-time indexing, you should ensure that you set the logging parameter, the Trace PC parameter, and the Trace SQL parameter.

Real-Time Indexing Logs

In the Process Scheduler configuration file (psprcs.cfg), locate the PSRTISRV section, and enter the parameter for logging and set it to 5, for example:

LogFence = 5

Note: LogFence in the PSRTISRV section is applicable to the PSRTISRV server process only; not applicable to other servers.

Trace PC

In the Application Server configuration file (psappsrv.cfg), locate Trace PC and set the value to 2048, for example:

Trace PC = 2048

Trace SQL

In the Application Server configuration file (psappsrv.cfg), locate Trace SQL and set the value to 1, for example:

Trace SQL = 1

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.

Oracle delivers the following built-in functions:

  • GetRTISwitchThreshold

  • TurnOffRTI

Syntax

GetRTISwitchThreshold(search definition)

Description

Use the GetRTISwitchThreshold function to return the document count specified as the threshold for a search definition on the Configure Real Time Indexing page.

Parameters

Parameter

Description

search definition

Specify the search definition for which you want to obtain the document count as a String value.

Returns

The document count as an integer. Returns -1 for failure.

Example

Local integer &x2 = GetRTISwitchThreshold("EP_AP_VENDOR");
WinMessage(&x2, 0);

Syntax

TurnOffRTI(search definition)

Description

Use the TurnOffRTI function to disable real-time indexing for a specified search definition when the document count exceeds the threshold specified on the Configure Real Time Indexing page. Additionally, this function disables real-time indexing for other search definitions that have common trigger records.

Parameters

Parameter

Description

search definition

Specify the search definition for which you want to disable real-time indexing as a String value.

Returns

A return code as an integer.

Return Code

Description

1

Real-time indexing is successfully disabled for the specified search definition and other search definitions that have common trigger records.

2

Batch jobs are not configured for the specified search definition.

3

SQL execution failed.

4

Real-time indexing is not enabled for the search definition or the search definition is not deployed.

5

Illegal arguments are passed to API call.

6

Trigger records are not found for the specified search definition.

8

Batch job is not configured for real-time indexing switch.

Example

Local integer &x = TurnOffRTI("EP_AP_VOUCHERS");
WinMessage(&x, 0);