Sybase Technical Library - Product Manuals Home
[Search Forms] [Previous Section with Hits] [Next Section with Hits] [Clear Search] Expand Search
Chapter 5: Writing Full-Text Search Queries [Table of Contents] Chapter 7: Performance and Tuning

Full-Text Search Specialty Data Store User's Guide

[-] Chapter 6: System Administration

Chapter 6

System Administration

This chapter describes system administration issues for both the Standard and Enhanced versions of the Full-Text Search engine. Topics include:

Starting the Full-Text Search Engine on UNIX

Use the startserver utility to start the Full-Text Search engine on UNIX. The startserver utility is included in the install directory of Adaptive Server. For example, to start a Full-Text Search engine named KRAZYKAT, enter:

startserver -f $SYBASE/install/RUN_KRAZYKAT

where the -f flag specifies the relative path to the runserver file. After you issue the command, the Full-Text Search engine issues a series of messages describing the settings of the configuration parameters.

Creating the Runserver File

The runserver file contains start-up commands for the Full-Text Search engine. The runserver file can include the flags shown in Table 6-1

Table 6-1: Definition of flags in the runserver file

Flag

Definition

-Sserver_name

Specifies the name of the Full-Text Search engine and is used to locate the configuration file and the network connection information in the interfaces file.

-t

Causes the Full-Text Search engine to write start-up messages to standard error.

-lerrorlog_path

Specifies the path to the error log file.

-iinterfaces_file_path

Specifies the path to the interfaces file.

A sample runserver file is copied to the $SYBASE/install directory during installation. Make a copy of this file, renaming it RUN_server_name, where server_name is the name of the Full-Text Search engine. You must include the correct path environment variable for your platform in the runserver file. Table 6-2 shows the path environment variable to use for each platform.

Table 6-2: Path environment variable for the runserver file

Platform

Environment Variable

RS/6000 AIX

LIBPATH

Sun Solaris

LD_LIBRARY_PATH

HP 9000(800)

SHLIB_PATH

Digital UNIX

LD_LIBRARY_PATH

For example, the runserver file on Sun Solaris for a Full-Text Search engine named KRAZYKAT would be RUN_KRAZYKAT and would be similar to:

#!/bin/sh
#
SYBASE=$SYBASE/sds/text
export SYBASE

LD_LIBRARY_PATH="$SYBASE/lib:$LD_LIBRARY_PATH"
export LD_LIBRARY_PATH

$SYBASE/bin/txtsvr -SKRAZYKAT

The start-up command in the runserver file must consist of a single line and cannot include a return. If you have to carry the contents of the file over to a second or third line, include a backslash (\) for a line break.

Starting the Full-Text Search Engine on Windows NT

You can start the Full-Text Search engine from Sybase Central(TM), as a service, or from the command line:

For example, to start a Full-Text Search engine named KRAZYKAT using the default sql.ini and error log files, and using -t to trace start-up messages, enter:

%SYBASE%\sds\text\bin\txtsvr.exe -SKRAZYKAT -t

The Full-Text Search engine is up and running when you see the start-up complete message.

Starting the Full-Text Search Engine As a Service

Use the instsvr utility in Sybase Central to add the Full-Text Search engine to the list of items you can start and stop with the Services utility. instsvr is located in the %SYBASE%\sds\text\bin directory.

The instsvr utility uses the following syntax:

instsvr.exe service_name %SYBASE%\sds\text\bin\txtsvr.exe "startup_parameters"

where:

For example, to install a Full-Text Search engine named KRAZYKAT_TS as a service, enter:

instsvr.exe KRAZYKAT_TS %SYBASE%\sds\text\bin\txtsvr.exe 
"-SKRAZYKAT_TS -t"
Note: If you need to include more than one parameter (for example, -i), you must include all the parameters in one set of double quotes.

To configure Sybase Central to start and stop your Full-Text Search engine, you must provide a service name that begins with "SYBTXT_server_name", where server_name is the name of the Full-Text Search engine listed in the interfaces file. For example, if the name in the interfaces file is KRAZYKAT_TS, run the following instsvr command to create a service that can be managed by Sybase Central:

instsvr SYBTXT_KRAZYKAT_TS %SYBASE%\sds\text\bin\txtsvr.exe
"-SKRAZYKAT_TS -t"

Shutting Down the Full-Text Search Engine

Use the following command to shut down the Full-Text Search engine from Adaptive Server:

server_name...sp_shutdown

where server_name is the name of the Full-Text Search engine you are shutting down.

For example, to shutdown a Full-Text Search engine named KRAZYKAT, enter:

KRAZYKAT...sp_shutdown

Modifying the Configuration Parameters

Each Full-Text Search engine has configuration parameters with default values, as shown in Table 6-3.

Table 6-3: Configuration parameters

Parameter

Description

Default Value

batch_size

Determines the size of the batches sent to the Full-Text Search engine.

500

max_indexes

The maximum number of text indexes that will be created in the Full-Text Search engine.

126

max_stacksize

Size (in kilobytes) of the stack allocated for client threads.

34,816

max_threads

Maximum number of threads available for the Full-Text Search engine.

50

max_packetsize

Packet size sent between the Full-Text Search engine and the Adaptive Server.

2048

max_sessions

Maximum number of sessions for the Full-Text Search engine.

100

min_sessions

Minimum number of sessions for the Full-Text Search engine.

10

language

Language used by the Full-Text Search engine.

us_english

charset

Character set used by the Full-Text Search engine.

iso_1

vdkCharset

Character set used by Verity Search '97.

850

vdkLanguage

Language used by Verity Search
'97.

english0

vdkHome

Verity directory.

UNIX:
$SYBASE/sds/text/verity
Windows NT:
%SYBASE%\sds\text\verity

collDir

Storage location of the Full-Text Search engine's collection.

UNIX:
$SYBASE/sds/text/collections
Windows NT:
%SYBASE%\sds\text\collections

default_Db

Name of the Full-Text Search engine database that stores text index metadata.

text_db

interfaces

Full path to the directory in which the interfaces file used by the Full-Text Search engine is located.

UNIX:
$SYBASE/interfaces
Windows NT:
%SYBASE%\ini\sql.ini

sort_order

Default sort order.

0

errorLog

Full path name to the error log file.

The directory in which you start Full-Text Search engine

traceflags

String containing numeric identifiers used to generate diagnostic information.

0

srv_traceflags

String containing numeric flag identifiers used to generate Open Server diagnostic information.

0

The Enhanced Full-Text Search engine has additional configuration parameters as shown in Table 6-4:

Table 6-4: Configuration parameters for Enhanced version only

Parameter

Description

Default Value

cluster_style

Clustering style to use.

Fixed

cluster_max

Maximum number of clusters to generate when cluster_style is set to Fixed.

0

cluster_effort

Amount of effort the Full-Text Search engine should expend on finding a good cluster.

Default

cluster_order

The order to return clusters and rows within a cluster.

0

auto_online

Specifies whether to bring indexes online automatically when the Full-Text Search engine is started. 0 indicates online is not automatic; 1 indicates automatic.

0

backDir

The default location for the placement of text index backup files.

UNIX:
$SYBASE/sds/text/backup
Windows NT:
%SYBASE%\sds\text\backup

knowledge_base

The location of a knowledge base map for implementing the Verity topics feature.

null

nocase

Sets the case-sensitivity of the Full-Text Search engine. If you are using a case-sensitive sort order in Adaptive Server, set to 0. If you are using a case-insensitive sort order, set to 1.

0

A sample configuration file that includes all of these parameters is copied to your installation directory during installation. The sample configuration file is named textsvr.cfg. The entire sample configuration file is listed in Appendix B, "Sample Files."

Modifying Values in the Standard Version

With Standard Full-Text Search Specialty Data Store, you use a configuration file to change the default values. The configuration file is named server_name.cfg and is in the $SYBASE directory. server_name is the name of the Full-Text Search engine.

To modify the default values, use a text editor to edit the configuration file. Uncomment the line that contains the configuration parameter you are modifying. You must restart the Full-Text Search engine for the new values to take effect.

Modifying Values in the Enhanced Version

With Enhanced Full-Text Search Specialty Data Store, you can use the sp_text_configure system procedure to change the value of a configuration parameter. The syntax is:

sp_text_configure server_name, config_name, config_value

where:

For more information, see "sp_text_configure".

Note: You can also modify the value of a configuration parameter by editing a configuration file as described in "Modifying Values in the Standard Version".

Setting the Default Language

The default language for Verity is set with the vdkLanguage configuration parameter. By default, vdkLanguage is set to "english0". You can configure Verity to use a different default language. Table 6-5 lists the locales supported by Sybase.

Table 6-5: vdkLanguage configuration parameters

Language

Default Locale Name

English

english0

German

german0

French

french0

Additional language adapters are available in the $SYBASE/sds/text/verity/common directory; however, the Full-Text Search engine displays messages only in the languages shown in Table 6-5.

The language parameter is the language the Full-Text Search engine displays its error messages and Open Server and Open Client error messages. Set the language parameter to the Adaptive Server language.

For example, with the Standard Full-Text Search engine, to change the Verity language to Spanish in a server named KRAZYKAT, include the following line in the configuration file:

vdkLanguage = spanish0

With the Enhanced Full-Text Search engine, run the following:

sp_text_configure KRAZYKAT, 'vdkLanguage', 'spanish0'

For more information about the Verity languages, see the Verity Web site:

http://www.verity.com

Setting the Default Character Set

The default character set for Verity is set with the vdkCharset parameter in the configuration file. The files used for the Verity character sets are in $SYBASE/sds/text/verity/common. Table 6-6 describes the character sets you can use with Verity.

Table 6-6: Verity character sets

Character Set

Description

850

Default

437

IBM PC character set

1252

Windows code page for Western European languages

mac1

Macintosh roman

The default character set for the Full-Text Search engine is set with the charset parameter. Set the charset parameter to the Adaptive Server character set.

For example, with the Standard Full-Text Search engine, to change the Verity character set to IBM PC in a server named KRAZYKAT, include the following line in the configuration file:

vdkCharset = 437

With the Enhanced Full-Text Search engine, run the following:

sp_text_configure KRAZYKAT, 'vdkCharset', '437'

For more information about the Verity character sets, see the Verity Web site:

http://www.verity.com

Setting the Default Sort Order

By default, the Full-Text Search engine sorts the result set by the score pseudo column in descending order (the higher scores appear first). To change the default sort order, set the sort_order configuration parameter to one of the values in Table 6-7.

Table 6-7: Sort order values for the configuration file

Value

Description

0

Returns result sets sorted by the score pseudo column in descending order. The default value.

1

Returns result sets sorted by the score pseudo column in ascending order.

2

Returns result sets sorted by a timestamp in descending order.

3

Returns result sets sorted by a timestamp in ascending order.

For example, with the Standard Full-Text Search engine, to change the default sort order to sort by descending timestamp in a server named KRAZYKAT, include the following line in the configuration file:

sort_order = 2

With the Enhanced Full-Text Search engine, enter:

sp_text_configure KRAZYKAT, 'sort_order', '2'

When you sort a result set by descending timestamp (value 2 in Table 6-7), the Full-Text Search engine returns the newest documents first. The newest documents are those that were inserted or updated most recently. When results are sorted by ascending timestamp (value 3 in Table 6-7), the Full-Text Search engine returns the oldest documents first.

Setting the default sort order is especially important if your query uses the max_docs pseudo column. The max_docs pseudo column limits the number of rows of the result set to the first n rows, ordered by the sort order. If you set max_docs to a number smaller than the size of the result set, the sort order you select could exclude the rows that contain the information for which you are searching.

For example, if you sort by ascending timestamp, the latest document added to the table appears last in the result set. If the entire result set consists of 11 documents, and you set max_docs to 10, the latest document does not appear in the result set. However, if you sort by descending timestamp, the latest document appears first in the result set.

Setting Trace Flags

The traceflags parameter enable the logging of certain events when they occur within the Full-Text Search engine. Each trace flag is uniquely identified by a number. Trace flags are described in Table 6-8.

Table 6-8: Full-Text Search engine trace flags

Trace Flag

Description

1

Traces connects, disconnects, and attention events from Adaptive Server.

2

Traces language events. Traces the SQL statement that Adaptive Server sent to the Full-Text Search engine.

3

Traces RPC events.

4

Traces cursor events. Traces the SQL statement sent to the Full-Text Search engine by Adaptive Server.

5

Writes the errors that display to the log.

6

Traces information about text indexes. Writes the search string being passed to Verity to the log, and writes the number of records that the search returns to the log.

7

Traces done packets.

8

Traces calls to the interface between the Full-Text Search engine and the Verity API.

9

Traces SQL parsing.

10

Traces Verity processing.

11

Disables Verity collection optimization.

12

Disables sp_statistics from returning information.

13

Traces backup operations. Available only with Enhanced Full-Text Search Specialty Data Store.

14

Logs Verity status and timing information.

15

Generates ngram index information for collections. ngrams increase the speed of wildcard searches. This trace flag is required for wildcard searches against data in unicode format.

You can enable and disable trace flags interactively, using the remote procedure calls (RPCs) sp_traceon and sp_traceoff in the Full-Text Search engine. For more information on these RPCs, see the Adaptive Server Reference Manual.

Setting Open Server Trace Flags

Use the srv_traceflags parameter to turn on trace flags to log Open Server diagnostic information. Open Server trace flags are described in Table 6-9.

Table 6-9: Open Server trace flags

Trace Flag

Description

1

Traces TDS headers.

2

Traces TDS data.

3

Traces attention events.

4

Traces message queues.

5

Traces TDS tokens.

6

Traces Open Server events.

7

Traces deferred event queues.

8

Traces network requests.

For example, with the Standard Full-Text Search engine, to trace attention events on the server named KRAZYKAT, include the following line in the configuration file:

srv_traceflags = 3

With the Enhanced Full-Text Search engine, run the following:

sp_text_configure KRAZYKAT, 'srv_traceflags', '3'

Setting Case Sensitivity

By default, the Full-Text Search engine is case sensitive. This means you must enter identifiers in the same case or they are not recognized. For example, if you have a table named blurbs (lowercase), you cannot issue an sp_create_text_index command that specifies the table name BLURBS. You must issue a command that uses the same case for the table name argument:

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

With Enhanced Full-Text Search engine, use the nocase parameter to set the case sensitivity of the Full-Text Search engine. 0 indicates case sensitive; 1 indicates case insensitive. Set the nocase parameter to the sort order case sensitivity in Adaptive Server.

For example:

sp_text_configure KRAZYKAT, 'nocase', '1'

changes the KRAZYKAT server to case insensitive.

Note: The nocase parameter does not affect the case sensitivity of the Verity query. For information on Verity case sensitivity, see "Considerations When Using Verity Operators".

Backup and Recovery for the Standard Full-Text Search Engine

The Adaptive Server user database and the Verity collections are physically separate. Backing up your user database does not back up the Verity collections, and restoring your database from a backup does not restore your Verity collections. The backup and recovery procedures described in Chapter 21, "Backing Up and Restoring User Databases," of the System Administration Guide apply only to the user database and text_db database in Adaptive Server.

Make sure you follow the recommended schedule for backing up your databases that is described in Chapter 20, "Developing a Backup and Recovery Plan," of the System Administration Guide. Sybase recommends that when you back up a user database with text indexes, you also back up:

A regular backup schedule ensures the integrity of the text indexes, the Adaptive Server data, and the text_events table, all of which are integral to recovering your text indexes without having to drop and re-create them.

Note: You do not have to back up the user database and text indexes at the same time to recover the text indexes. However, you must restore the user database before you restore the text index. Doing so restores the text_events table, which the sp_redo_text_events system procedure uses to bring the text indexes in sync with the user database.

If you have Enhanced Full-Text Search Specialty Data Store, use the automated process described in "Backup and Recovery for the Enhanced Full-Text Search Engine".

Backing Up Verity Collections

Follow these steps to back up your Verity collections:

  1. Shut down the Full-Text Search engine:

    server_name...sp_shutdown
  2. Back up the files. By default, the collections are located in:

    $SYBASE/sds/text/collections

    Each collection name consists of the database name, owner name, and index name in the format db.owner.index. For example, if you create a text index called i_blurbs on the pubs2 database, the full path to those files would be similar to:

    $SYBASE/sds/text/collections/pubs2.dbo.i_blurbs

  3. For future reference, make a note of the time of the backup in a permanent location.

  4. Back up the user database and the text_db database, using the dump database command. For more information on the dump database command, see the Adaptive Server Reference Manual.

  5. Restart the Full-Text Search engine. For instructions, see "Starting the Full-Text Search Engine on UNIX" or "Starting the Full-Text Search Engine on Windows NT".

Restoring Verity Collections and Text Indexes from Backup

As Database Administrator, follow these steps to restore your Verity collections:

  1. Restore the Adaptive Server user database and text_db database. This returns the source tables, metadata, and text_events table to a consistent and predictable state. See Chapter 21, "Backing Up and Restoring User Databases," in the System Administration Guide for more information.

  2. Shut down the Full-Text Search engine:

    server_name...sp_shutdown
  3. Restore your collections from the backup files created in step 2 in "Backing Up Verity Collections".

  4. Restart the Full-Text Search engine. For instructions, see "Starting the Full-Text Search Engine on UNIX" or "Starting the Full-Text Search Engine on Windows NT".

  5. Log in to Adaptive Server, and run the sp_redo_text_events system procedure in the restored database. For example, if you are restoring the pubs2 database, you have to be in that database to run the system procedure, sp_redo_text_events, as follows:

    sp_redo_text_events "from_date"
    where from_date is the date and time associated with the backup used to recover the collections.

    For example:

    sp_redo_text_events "10/31/97:16:45"
    restores the collections up to October 31, 1997 at 4:45 PM. For more information, see "sp_redo_text_events".

  6. Run the sp_text_notify system procedure to notify the Full-Text Search engine that changes need to be propagated to the Verity collections. The Full-Text Search engine connects to Adaptive Server, reads all the unprocessed entries in the text_events table and applies them to the text index. For more information, see "sp_text_notify".

Your text indexes and collections are now fully restored.

Backup and Recovery for the Enhanced Full-Text Search Engine

Backup and recovery for the Enhanced Full-Text Search Specialty Data Store is automated with the sp_text_dump_database and sp_text_load_index system procedures. These system procedures provide a seamless interface for maintaining data and text index integrity.

The Adaptive Server user database and the Verity collections are physically separate. Backing up your user database does not back up the Verity collections, and restoring your database from a backup does not restore your Verity collections. The backup and recovery procedures described in Chapter 21, "Backing Up and Restoring User Databases," of the System Administration Guide apply only to the user database and the text_db database in Adaptive Server.

Follow the recommended schedule for backing up your databases, as described in Chapter 20, "Developing a Backup and Recovery Plan," of the System Administration Guide. Sybase recommends that when you back up a user database with text indexes, you also back up:

A regular backup schedule ensures the integrity of the text indexes, the Adaptive Server data, and the text_events table, all of which are integral to recovering your text indexes without having to drop and re-create them.

If you have Standard Full-Text Search Specialty Data Store, use the process described in "Backup and Recovery for the Standard Full-Text Search Engine".

Backing Up Verity Collections

The sp_text_dump_database system procedure backs up collections and (optionally) the user and text_db databases. sp_text_dump_database also maintains the text_events table by deleting entries that are no longer needed for recovery. It is available only with the Enhanced Full-Text Search engine.

During a backup, the Full-Text Search engine processes queries, but defers any update requests until the backup is complete. This eliminates the need to shut down and restart the Full-Text Search engine.

Run sp_text_dump_database from the database containing the text indexes you are backing up. The backup of the text indexes is placed in the directory specified in the backDir configuration parameter. The output of the dump database command is written to the Full-Text Search error log. Sybase recommends dumping the current database and the text_db database at the time the text indexes are backed up. However, this is optional.

For example, to back up the text indexes, the sample_colors_db database to the /work2/sybase/colorsbackup directory, and the text_db database to the /work2/sybase/textdbbackup directory, enter:

sp_text_dump_database @backupdbs = INDEXES_AND_DATABASES, @current_to = "to '/work2/sybase/colorsbackup'", @textdb_to="to '/work2/sybase/textdbbackkup'"
Note: It is important to back up the text_db database whenever text indexes are backed up, since that database contains the metadata for all text indexes.

For more information, see "sp_text_dump_database".

Restoring Collections and Text Indexes from Backup

The sp_text_load_index system procedure restores text indexes that have been backed up with the sp_text_dump_database system procedure.

As Database Administrator, perform the following procedures to restore your Verity collections:

  1. Restore your Adaptive Server user database and text_db database. This returns the source tables, metadata, and text_events table to a consistent and predictable state. Follow the procedures described in Chapter 21, "Backing Up and Restoring User Databases," in the System Administration Guide, to restore user and text_db databases.

  2. Run sp_text_load_index to restore the Verity collection from the most recent index dump. The procedure resets the status of all text_events table entries made since the last index dump to "unprocessed" and notifies the Full-Text Search engine to process those events.

Example:

To restore the sample_colors_db database and all of its text indexes:

  1. Restore the text_db database:

    1> use master
    2> go
    1> load database text_db from '/work2/sybase/textdbbackkup'
    2> go
  2. Restore the sample_colors_db database:

    1> load database sample_colors_db from '/work2/sybase/colorsbackup'
    2> go
  3. Bring the text_db and sample_colors_db databases online:

    1> online database text_db
    2> online database sample_colors_db
    3> go
  4. Restore the text index:

    1> use sample_colors_db
    2> go
    1> sp_text_load_index
    2> go

For more information, see "sp_text_load_index".

Operator Modifiers [Table of Contents] Chapter 7: Performance and Tuning