FULLTEXT Index Validation

Overview

BBj version 25.03 and higher offers a feature called FULLTEXT Index Validation, which helps maintain the integrity of your FULLTEXT indexes. FULLTEXT indexes, built with Lucene, are used to provide fast, character-based searches on your database tables. However, because they are separate from the primary data files, these indexes can occasionally become out of sync with the underlying data due to events like unexpected system shutdowns or uncaught exceptions.

Generally, an out-of-sync index required a complete rebuild, a process that could be time-consuming. The new validation feature allow users to schedule a process to check and correct any discrepancies between the data and the index without needing a full rebuild. It checks each record in the database against its corresponding entry in the FULLTEXT index to ensure they are synchronized. While this process can run during live database usage, we recommend scheduling it during off-peak hours to minimize any potential impact on performance.

Setting up a Scheduled Validation Job

You can configure and schedule a FULLTEXT Index Validation task using the Enterprise Manager.

  • Open Enterprise Manager and navigate to BBjServices → Scheduling.

  • Click New Job to open the scheduling wizard.

  • On the first page, provide a descriptive name for the Task Group and select the Scheduled radio button, then click Next.

  • On the next page, set a Start Date and Time for the first run.

    • Select the Repeat Type. You can choose from options like daily, weekly, or monthly.

    • If you choose Weekly, you can also select the specific days of the week on which the job should run (e.g., every Monday, Wednesday, and Friday).

    • Click Next.

  • On the Task List page, click the Add button.

  • In the dialog that appears, select FULLTEXT Index Validation as the Task Type.

  • Choose the database you want to validate from the dropdown list and click Ok.

Note: You can add multiple validation tasks to a single job, but for better performance, we recommend creating a separate scheduled job for each database you need to validate.

  • Click Finish to create and activate the scheduled job.

The validation will only run on tables that have an active FULLTEXT index. The process will check each record to ensure it is correctly indexed, correcting any issues it finds.

See Also

Scheduling

Task Scheduling

SQL FULLTEXT Indices