One of the most common questions inquired when it comes to Search360 is indexing, so a blog on this is timely- One small caveat is that this is an informal document and is still pending an official IBM stamp. Hope this blog helps and feel free to comment and post any questions you might have.
Search 360 is a new feature introduced in iBase 8.0.1. This feature provides extended searching functionality such as matching synonyms and ‘sounds like’ matches.
In order to use Search 360 the iBase database must be indexed and in order to incorporate added or updated records the iBase database must be re-indexed. This document outlines the methods by which the database can be indexed and a scheduling mechanism set up for re-indexing.
Configuration 1: Installing and Configuring i2 iBase 8 Index Service Configuration Tool Locally on SQL Server Machine
Installing Index Configuration tool
In order to install the Index Service Configuration tool you must have at least .NET 2 SP1 installed.
To install the i2 iBase 8 Index Configuration tool run the iBase installation setup.exe from your chosen media (i.e. from the product CD or your network).
Note that the iBase installer will require that the Report Viewer is installed as a pre-requisite. However, the Report Viewer is not required if you wish to install just the iBase 8 Index Configuration tool. To override this pre-condition you must run the iBase installer with the command line switch REPORTVIEWER=1. So for example, if you have placed your installation file directly on your C drive you will run the following command:
“C:\i2 iBase 8.msi” REPORTVIEWER=1
Proceed with the installation as you would with any other installation but ensure that when you get to the section of the wizard where you select the setup type that you select ‘Custom’.
The installer will then provide you with the option of which components you would like to install. To install just the i2 iBase 8 Index Configuration tool you only need install the ‘Server’ component.
Common Language Runtime (otherwise known as CLR) must be enabled on your SQL Server in order for Search 360 to index. To enable CLR run the following script on the SQL Server instance that hosts your iBase database:
sp_configure 'show advanced options', 1;
sp_configure 'clr enabled', 1;
Note that if the account that iBase uses to access the SQL Server has sufficient permissions (such as the SYSADMIN role) to enable CLR then this is automatically set when a user enables Search 360 and as such the above script will not be required.
SQL Server Permissions
In order to configure the index service you will require a SQL Server account with the following permissions:
Once the index service has been configured you may use either a Windows account that has access to the SYSADMIN role on the SQL Server or a SQL Server account with the PUBLIC database role.
Setting up iFilters
Search 360 will automatically index all documents with the suffix RTF, ANB, LNB, TCV, XML, CSV and TXT. All other document types will require that an appropriate iFilter is installed on the machine on which indexing is performed.
Some iFilters you may wish to consider:
Office 2003 – Often comes installed with Office 2003 itself.
Office 2007 – Will need to download and install the MS Office iFilter Pack.
PDF – Comes with Adobe Reader 9.1 (or later).
Enabling Search 360
The next step is to enable Search 360 inside iBase. To do this open your database in iBase Designer and then go to Tools > Search > Search 360 Administration. This will present you with the Search 360 Administration as follows:
Fig. 2 – Search 360 Administration dialog
Once on this dialog you should tick the ‘Enable “Search 360”’ checkbox. Select all the entities and links that you would like to index and then press the ‘Build Index’ button. This should present you with the following confirmation:
Fig. 3 – Index request confirmation
Configuring Search Service Configuration tool
Once everything is set and ready to go you should open the i2 iBase 8 Configuration tool from the Windows Start menu. When you first open the tool you will be asked to enter login details for the SQL Server.
Fig. 4 – Configuring SQL Server login
If your SQL Server instance uses any name other than the default then you will need to enter this in to the ‘Instance name (optional):’ text box. For the login you should enter the credentials that you created in the earlier section where you set up SQL Server and CLR.
Once you have entered your server details then you should select the ‘Add’ button to bring up the ‘Configure Database’ dialog.
Fig. 5 – Configuring a schedule
You should enter the details of the iBase database (the name of the SQL Server database) that you wish to index and the SQL Server (plus instance name) on which it resides.
Once you have entered all of the scheduling details you may press OK to complete the index scheduling process. The index should run at the time you specified as long as the SQL Server Agent is running.
SQL Server Agent
To ensure that the SQL Server Agent is running you can check in the Windows services dialog or through SQL Server Management Studio.
To open the Windows services dialog go to the Control Panel and then go to Administrative Tools. In this dialog choose ‘Services’. This should open the Windows services dialog as follows:
Fig. 6 – Windows services dialog
To check that the SQL Server Agent is running make sure that the service with the name ‘SQL Server Agent (instance name)’ is started. You can start the service manually by right-clicking on it and selecting ‘Start’. If the service type is not set to automatic then you can enable automatic startup if you right-click the SQL Server Agent (instance name) service, go to properties and then choose the ‘Startup type:’ as ‘Automatic’.
Fig. 7 – SQL Server Agent properties
The other way of checking that the SQL Server Agent is running is by checking through SQL Server Management Studio.
To do this open SQL Server Management Studio and then connect to the database engine instance which your Index Configuration tool has been configured to use. If the SQL Server Agent is running it will be displayed with a green play arrow. If the Agent is not running then it will be displayed with a red down arrow.
Fig. 8 – SQL Server Agent running
You can start the SQL Server Agent manually by right-clicking on it and selecting ‘Start’.
To make the SQL Server Agent start automatically, open the SQL Server Surface Area Configuration tool. Choose ‘Surface Area Configuration for Services and Connections’. Go to the SQL Server Agent for your chosen instance and then change the ‘Startup type:’ to Automatic.
Fig. 9 – Surface Area Configuration
Configuration 2: Running Indexing directly using the Command Line and the Search360 executable.
You must start by retrieving the Search 360 executable. To do this install the Index Configuration tool on any machine as per (reference config tool install). Once this has been installed you can take the i2.iBase.SearchIndexerConfig.exe from the install location (which is C:\Program Files\i2 iBase 8 by default) and put it in your chosen location.
You must install .NET on whichever machine you decide to run the Search 360 executable from.
The executable has a number of command line parameters. These are:
/iBaseDBName “[Database Name]” – The name of the iBase database as it appears in SQL Server.
/ServerName “[Server Name\Instance Name]” – The name of the machine hosting the SQL Server and the instance name if it is not the default.
/Username “[Username]” – The username of a SQL Server account that has sufficient permissions to perform Search360 indexes.
/Password “[Password]” – The password of the account to be used for indexing Search360.
/FullReindex – All existing indexes will be cleared and it will start the index at the beginning regardless of its progress through a previous index.
/EditLogPreview – Shows the contents of the _FS_Edit_Log table which shows all updates that need to be made to the index. Warning: Including this argument means no indexing will go ahead.
/ShowSummary – Provides a summary of the record counts in various indexing tables. Warning: Including this argument can cause a significant increase in the time to index large databases.
/DisplayRecordId – Displays the ID of the record that is being processed during the index process.
/Pause – Causes the indexer to stay open once indexing is complete. This is useful for keeping the output open after using a batch file.
In order to run an index you must at least specify database name and server name. In most cases you will need to specify a username and password. The other arguments can be used to help you troubleshoot indexing problems.
As an example, if I have set up an iBase database called “User Guide” (which appears as User_Guide in SQL Server), I have set up a SQL Server user called “search360” with the password “pass”, the server is local and is on the default instance and I have put the executable directly on the C drive then the following is the command line that I would run:
"C:\i2.iBase.SearchIndexerExe.exe" /iBaseDBName "User_Guide" /ServerName "localhost" /Username "search360" /Password "pass"
The simplest way of running the executable from the command line multiple times is to create a batch file. To do this simply create a new plain text file and put the command that you wish to run in this file and save it. Change the file extension from txt to bat.
Adding a Scheduled Task to Windows
The first step in doing this is to open the Windows Scheduled Task tool. You can do this by going to Start > All Programs > Accessories > System Tools > Scheduled Tasks. You should now see the Scheduled Tasks window:
Fig. 10 – Windows scheduled tasks
To add a new task double-click on ‘Add Scheduled Task’. Once on the Scheduled Task Wizard click next and you should be brought to the stage where you select a program to run.
There are two ways of adding the indexer as a scheduled task. The first is to go to browse and then find the scheduler executable in the location that you placed it. Once you have done this go through the rest of the wizard to configure the schedule and the Windows account that will be used.
Once you have completed the wizard you should see that there is a new scheduled task named after the Search360 executable. Right-click on this task and go to Properties. In the properties window, in the run box, place all of the command line arguments after the entry for the executable.
Fig. 11 – Scheduled task properties
Your Search 360 index should now run on a regular basis as you scheduled it.
The second way of adding a Search 360 index task is to create a batch file that contains the command you wish to run. Once you have done this you select this batch file as the program you wish to run. Continue with the wizard to select a schedule and an account. This should add a new scheduled task which will run your Search 360 index.
Configuration 3: Running the Index Service Configuration tool from a remote location.
It is possible to run the Index Service Configuration tool on one machine such that it indexes the database on another machine. In order for this to work there must be an instance of SQL Server on the same machine as the Index Service Configuration tool.
In order to do this, start by installing the SQL Server Configuration tool as per (reference installation steps).
You will need an account on the local SQL Server instance that has dbcreator permissions and on the remote database it needs the other permissions as referenced (reference to SQL permissions).
When you open the Index Service Configuration tool then you must specify login details for the local SQL Server instance. This is where the IBaseIndexDB will be reside and where the SQL Server Agent jobs will be created.
When you add the scheduled index you must input the information for the server and database on the remote machine.
All of the iFilters must be installed on the local machine and not the remote machine. Follow (reference iFilters) for details.
The SQL Server Agent will need to be running on the local machine. Check (reference SQL Server Agent details) for more details.
CLR needs to be enabled on the remote machine. Details of doing this can be found in (reference CLR).
Configuration 4: Manually creating a job on the SQL Server Agent.
It is possible to create the jobs in SQL Server manually.
To do this open the SQL Server instance that has the SQL Server agent that you would like to use. Expand the SQL Server agent in the object explorer:
Fig. 12 – SQL Server Agent
Right-click on “Jobs” and select ‘New Job’. This should open the New Job dialog:
Fig. 13 – New Job menu
Choose any name and leave the owner. You may write a description if you wish.
Under ‘Select a page’ go to Steps. Select ‘New…’ at the bottom to add a new step.
Fig. 14 – Adding new job step.
Give it a name. Change the Type to Operating system (CmdExec). Add a command line argument as made in (reference creating command line argument). Press OK.
Go to schedule and select ‘New…’. This should open the scheduling dialog.
Fig. 15 – New Job Schedule.
Give the schedule a name and then and choose an appropriate schedule. Press OK. Press OK. This will have created a job that runs the Search 360 indexer on your specified database on the schedule that you have specified.