Full-Text Search Specialty Data Store User's Guide

Home

Chapter 2: Understanding the Full-Text Search Engine

Full-Text Search Specialty Data Store User's Guide

Chapter 3: Configuring Adaptive Server for Full-Text Searches

Chapter 3

Configuring Adaptive Server for Full-Text Searches

This chapter describes how to configure Adaptive Server to perform full-text searches. Topics include:

Configuring Adaptive Server for a Full-Text Search Engine

The Full-Text Search engine is a remote server that Adaptive Server connects to through Component Integration Services (CIS). Before you can use the Full-Text Search engine, configure Adaptive Server for a Full-Text Search engine as follows:

Enable the enable cis and rpc configuration parameters if you have not done so
Run the installtextserver script to define one or more Full-Text Search engines
Run the installmessages script to install messages for the Full-Text Search engine's system procedures
Run the installevent script to create the text_events table in the user database

Enabling Configuration Parameters

To connect to the Full-Text Search engine, Adaptive Server must be running with the enable cis and rpc configuration parameters enabled. If those parameters are not enabled, log in to Adaptive Server using isql and use sp_configure to enable them. For example:

exec sp_configure "enable cis", 1
exec sp_configure "rpc", 1

Adaptive Server displays a series of messages stating that you have altered a configuration parameter and that Adaptive Server must be rebooted for the new configuration parameters to take effect.

Running the installtextserver Script

The installtextserver script:

Defines the Full-Text Search engine as a remote server of server class sds to Adaptive Server.
Creates a database for storing text index metadata. For more information about this database, see "The text_db Database".
Installs the system procedures required by the Full-Text Search engine.

Run the installtextserver script only once (see "Running the installtextserver Script"). To add Full-Text Search engines at a later time, use sp_addserver. See "Configuring Multiple Full-Text Search Engines" for more information about sp_addserver.

All Full-Text Search engines use the same database for storing text index metadata. This database is referred to in this book as the text_db database, the default name.

For a list and description of the system procedures added with the installtextserver script, see Appendix A, "System Procedures."

Editing the installtextserver Script

The installtextserver script is located in the $SYBASE/sds/text/scripts directory. Use a text editor (such as vi or emacs) to open the script, and make your edits. The edits you can make are as follows:

Changing the name of the text_db database. If you use a different name, replace all occurrences of text_db with the appropriate name.
Note: If you change the name of the text_db database, you must change the name in the defaultDb configuration parameter (see "Modifying the Configuration Parameters").
Changing the name of the Full-Text Search engine. By default, the installtextserver script defines a Full-Text Search engine named "textsvr." If your Full-Text Search engine is named differently, edit this script so that it defines the correct server name.
Adding multiple Full-Text Search engines (for information on how this can enhance performance, see "Configuring Multiple Full-Text Search Engines"). If you are initially defining more than one Full-Text Search engine, edit the installtextserver script so that it includes all the Full-Text Search engine definitions. installtextserver includes the following section for naming the Full-Text Search engine you are configuring ("textsvr" by default):
```
/*
** Add the text server
*/
exec sp_addserver textsvr,sds,textsvr
go
```
Add an entry for each Full-Text Search engine you are configuring. For example, if you are configuring three Full-Text Search engines named KRAZYKAT, OFFICAPUP, and MOUSE, replace the default "textsvr" line with the following lines:
```
exec sp_addserver KRAZYKAT, sds, KRAZYKAT
exec sp_addserver OFFICAPUP, sds, OFFICAPUP
exec sp_addserver MOUSE, sds, MOUSE
go
```
If you use OmniConnect to communicate with the Full-Text Search engine, change the server name specification in the sp_addobjectdef calls for the vesaux and vesauxcol tables to a valid remote server. For example, if your remote server is named REMOTE, change the lines:
```
exec sp_addobjectdef "vesaux","SYBASE.master.dbo.vesaux","table"
exec sp_addobjectdef "vesauxcol","SYBASE.master.dbo.vesauxcol", "table"
```
to something similar to:
```
exec sp_addobjectdef "vesaux","REMOTE.master.dbo.vesaux","table"
exec sp_addobjectdef "vesauxcol","REMOTE.master.dbo.vesauxcol", "table"
```

Running the installtextserver Script

Use isql to run the installtextserver script. For example, to run the installtextserver script in an Adaptive Server named MYSVR, enter:

isql -Usa -P -SMYSVR -i $SYBASE/sds/text/scripts/installtextserver

Running the installmessages Script

The Full-Text Search engine has its own set of system procedure messages that you must install in Adaptive Server. Use the installmessages script to install the messages. You run the installmessages script only once, even if you have multiple Full-Text Search engines.

For example, to run the installmessages script in a server named MYSVR, enter:

isql -Usa -P -SMYSVR -i $SYBASE/sds/text/scripts/installmessages

Running the installevent Script

Each database containing tables referenced by a text index must contain a text_events table, which logs inserts, updates, and deletes to indexed columns. It is used to propagate updated data to the Verity collections.

Run the installevent script, as described below, to create the text_events table and associated system procedures in a database. Use the installevent script as follows:

If all databases require text indexes, run the installevent script to create a text_events table in the model database. Each newly created database will then have a text_events table. To add a text_events table to existing databases, edit the script as described below to create the text_events table in the existing user database.
If not all databases have text indexes, use the installevent script as a sample. For each existing database and each new database that includes tables that require text indexing, run the installevent script. You must edit the script as described below, to create the text_events table in the correct user database.
Note: If a text_events table does not exist in a database that includes source tables that require text indexing, changes to the source table will not be propagated to the Verity collections.

Editing the installevent Script

The installevent script is located in the $SYBASE/sds/text/scripts directory. Use a text editor (such as vi or emacs) to open the script, and make the edits. The edits you can make are:

Changing the user database name. The installevent script creates an events table (named text_events) and associated system procedures in the model database. The model database is the default database. To install the text_events table in an existing user database, edit the script and replace all references to model with the user database name.
Changing the text_db database name. If your database for storing text index metadata is named something other than text_db, replace all references to text_db with the appropriate name.
Note: The name of the text_db database must be the same as the name in the defaultDb configuration parameter (see "Modifying the Configuration Parameters").

Running the installevent Script

Note: The text_db database must exist before you run the installevent script. If it does not exist, run the installtextserver script first.

Using isql, run the installevent script to install the text_events table and related system procedures in Adaptive Server. For example, to run the installevent script in a server named MYSVR, enter:

isql -Usa -P -SMYSVR -i $SYBASE/sds/text/scripts/installevent

Creating and Maintaining the Text Indexes

Before the Full-Text Search engine can process full-text searches, you must create text indexes for the source tables in the user database. After the text indexes are created, you must update them when the source data changes to keep the text indexes current. To create and maintain the text indexes:

Set up the source table for indexing (see "Setting Up Source Tables for Indexing").
Create the text indexes and index tables (see "Creating the Text Index and Index Table").
Bring the databases online for full-text searches (see "Bringing the Database Online for Full-Text Searches").
Propagate changes in the user data to the text indexes (see "Propagating Changes to the Text Index").
If you are replicating text indexes, set up text indexing in the destination database (see "Replicating Text Indexes").

For an example of setting up a text index, see the sample script sample_text_main.sql in the $SYBASE/sds/text/sample/scripts directory.

Setting Up Source Tables for Indexing

The source table contains the data on which you perform searches (for example, the blurbs table in the pubs2 database). For more information on source tables, see "The Source Table".

Before you can create text indexes on a source table, you must:

Verify that the source table has an IDENTITY column
Create a unique index on the IDENTITY column (optional)

Adding an IDENTITY Column to a Source Table

Every source table must contain an IDENTITY column to uniquely identify each row and provide a means of joining the index table and the source table. When you create a text index, the IDENTITY column is passed with the indexed columns to the Full-Text Search engine. The IDENTITY column value is stored in the text index and is mapped to the id column in the index table.

The IDENTITY column must have sufficient precision and scale to guarantee a unique IDENTITY for each row. Sybase recommends a precision of 10 and a scale of 0. You can use an existing IDENTITY column, if it is defined with sufficient precision and scale to identify each row uniquely.

For example, to create an IDENTITY column in a table named composers, define the table as follows:

create table composers (
    id            numeric(10,0)                identity,
    comp_fname            char(30)                not null,
    comp_lname            char(30)                not null,
    text_col            text
)

To add an IDENTITY column to an existing table, enter:

alter table table_name add id numeric(10,0) identity

Adding a Unique Index to an IDENTITY Column

For optimum performance, Sybase recommends creating a unique index on the IDENTITY column. For example, to create a unique index named comp_id on the IDENTITY column created above, enter:

create unique index comp_id 
on composers(id)

For more information about creating a unique index, see Chapter 11, "Creating Indexes on Tables," of the Transact-SQL User's Guide.

Creating the Text Index and Index Table

Use the sp_create_text_index system procedure to create the text indexes. sp_create_text_index does the following:

Updates the vesaux and vesauxcol tables in the text_db database
Creates the text index (Verity collections)
Populates the Verity collections
Creates the index table in the user database where the source table is located

The text index can contain up to 16 columns. Columns of the following datatypes can be indexed:

Standard Version Datatypes

char, varchar, nchar, nvarchar, text, image, datetime, smalldatetime

Enhanced Version Datatypes

All Standard version datatypes, plus:

int, smallint, and tinyint

For example, to create a text index and an index table named i_blurbs for the copy column in the blurbs table in pubs2 on KRAZYKAT, enter:

sp_create_text_index "KRAZYKAT", "i_blurbs", "blurbs", " ", "copy"

where:

KRAZYKAT is the name of the Full-Text Search engine
i_blurbs is the name of the index table and text index you are creating
blurbs is the source table on which you are creating the text indexes
" " is a placeholder for text index creation options
copy is the column in the blurbs table that you are indexing

See "sp_create_text_index" for more information.

Note: Make sure the text_db database name in the configuration file (listed after the defaultDb parameter) matches the database name in Adaptive Server. If they do not match, the text index cannot be created. Also, verify that the text_events table exists in the user database. If it does not exist, run the installevent script for that database (refer to "Running the installevent Script").

Populating the Verity collections can take a few minutes or several hours, depending on the amount of data you are indexing. You may want to perform this step when the server is not being heavily used. Increasing the batch_size configuration parameter will also expedite the process. See "batch_size" for more information.

Specifying Multiple Columns When Creating a Text Index

When you create a text index on two or more columns, each column in the text index is placed into its own document zone. The name of the zone is the name of the column. For example, to create a text index and an index table named i_blurbs for both the copy column and the au_id column in the blurbs table in pubs2 on KRAZYKAT, enter:

sp_create_text_index "KRAZYKAT", "i_blurbs", "blurbs", " ", "copy", "au_id"

sp_create_text_index creates two zones in the text index named "copy" and "au_id." When you issue a query against the i_blurbs text index, the search includes the copy and au_id columns. However, you can limit your search to a particular column by using the in operator to specify a document zone (for more information, see "in").

Bringing the Database Online for Full-Text Searches

With the Standard version of Full-Text Search engine, you must manually bring a database online before issuing full-text queries on a source table in the database. When you bring a database online, the Full-Text Search engine initializes the internal Verity structures and confirms that the Verity collections exist.

Note: With the Enhanced Full-Text Search engine, the database is automatically brought online when the auto_online configuration parameter is set to 1.

Use the sp_text_online system procedure to bring a database online for full-text searches if it is not automatically brought online. For example, to bring the pubs2 database online before issuing full-text searches on the blurbs table in a Full-Text Search engine named KRAZYKAT, enter:

sp_text_online KRAZYKAT, pubs2

This message appears:

Database 'pubs2' is now online

The pubs2 database is now available for performing full-text searches.

See "sp_text_online" for more information.

Propagating Changes to the Text Index

When you insert, update, or delete data in your source table, the text indexes are not updated automatically. After you update data, run the sp_refresh_text_index system procedure to log the changes to the text_events table. Then, run the sp_text_notify system procedure to notify the Full-Text Search engine that changes need to be processed. The Full-Text Search engine then connects to Adaptive Server, reads the entries in the text_events table, determines which indexes, tables, and rows are affected, and updates the appropriate collections.

See "sp_refresh_text_index" and "sp_text_notify" for more information on these system procedures.

To have sp_refresh_text_index run automatically after each insert, update, or delete, you can create triggers on your source tables, as follows:

Create a trigger that runs sp_refresh_text_index after a delete operation.
Create a trigger that runs sp_refresh_text_index after an insert operation.
Create a trigger that runs sp_refresh_text_index after an update operation to an indexed column.

Triggers are not fired when you use writetext to update a text column. To have sp_refresh_text_index automatically run after a writetext:

Set up a non-text column and update that column after each writetext.
Create a trigger on the non-text column to run sp_refresh_text_index. Since the Full-Text Search engine reinserts the entire row when you issue sp_text_notify, the update to the text column gets propagated to the text index.

For examples of each of these triggers, see the sample script sample_text_main.sql in the $SYBASE/sds/text/sample/scripts directory.

Replicating Text Indexes

To replicate tables that have text indexes, follow these guidelines:

Create the table definition in the destination database.
Run the installevent script to create the text_events table in the destination database, if the text_events table does not already exist (see "Running the installevent Script").
Run sp_create_text_index to create the text index on the empty table in the destination database (see "Creating the Text Index and Index Table").
Create triggers for running sp_refresh_text_index to insert entries into the text_events table whenever you insert, update, or delete data into the table (see "Propagating Changes to the Text Index").
Create the replication definition in the Replication Server. This replicates all the data in the source table to the destination table.
Run sp_text_notify to update the text index; run sp_text_notify periodically to process changes to the destination table (see "Propagating Changes to the Text Index").
Note: You must issue an update against a non-text column whenever a writetext command is performed. This ensures that the trigger that inserts data into the text_events table is fired.

Example: Enabling a New Database for Text Searches

This example describes the steps for creating a text index on the plot column of the reviews table in the movies database. This process assumes that:

You have created a reviews table in a new database named movies on the MYSVR server
The reviews table has a column named plot that you are going to index
Adaptive Server and the Full-Text Search engine named MYTXTSVR have been configured to connect to each other

Step 1. Verify That the text_events Table Exists

Each database containing tables referenced by a text index must contain a text_events table, which logs inserts, updates, and deletes to indexed columns.

If a text_events table is in your model database, it will be in all new databases. If a text_events table is not in your model database, run the installevent script to install the text_events table in the new database. For example, to install the text_events table in the movies database:

Save the installevent script as installeventmovies.
Edit the script to replace all references to the word model with the word movies.

Run the script as follows:

isql -Usa -P -SMYSVR -i $SYBASE/sds/text/scripts/installeventmovies

See "Running the installevent Script" for information on installing the text_events table.

Step 2. Check for an IDENTITY Column

Every source table must contain an IDENTITY column, which uniquely identifies each row and provides a means of joining the index table and the source table.

For example, to add an IDENTITY column to the reviews table, enter:

alter table reviews add id numeric(10,0) identity

See "Adding an IDENTITY Column to a Source Table" for more information on creating an IDENTITY column.

Step 3. Create a Unique Index on the IDENTITY Column

This step is optional. To enhance performance, Sybase recommends creating a unique index that contains only the IDENTITY column. For example, to create a unique index named reviews_id on the IDENTITY column created in step 2, issue the command:

create unique index reviews_id on reviews(id)

For more information about creating a unique index, see Chapter 11, "Creating Indexes on Tables," of the Transact-SQL User's Guide.

Step 4. Create the Text Index and Index Table

The source tables in the user database need to be indexed so that you can perform full-text searches. For example, to create a text index and an index table named reviews_idx for the plot column in the reviews table, enter:

sp_create_text_index "MYTXTSVR", "reviews_idx", "reviews", " ", "plot"

The reviews table is now available for running full-text searches.

See "sp_create_text_index" for more information.

Step 5. Bring the Database Online for a Full-Text Search

To bring the movies database online for the Full-Text Search engine named MYTXTSVR, enter:

sp_text_online MYTXTSVR, movies

Note: Omit this step if you have Enhanced Full-Text Search engine and your auto_online configuration parameter is set to "1".

See "sp_text_online" for more information.


	Expand Search