Simple example with the SQL scalar search function

You can use the SQL scalar search functions, such as CONTAINS, NUMBEROFMATCHES, and SCORE, to search text indexes using AND, OR, and boolean operators.

Example

Use the following steps in the Db2® Net Search Extender example:

Creating a database
Enabling a database for text search
Creating a table
Creating a full-text index
Loading the sample data
Synchronizing the text index
Searching with the text index

You can issue the sample commands on the command line of the operating system by using an existing database. For the following examples, the database name is sample.

Creating a database

You can create a database in Db2 by using the following command:

db2 create database sample

Enabling a database for text search

You can issue Db2 Net Search Extender commands in the same way as Db2 commands on the command line of the operating system. For example, use the following command to start Net Search Extender Instance Services:

db2text START

Then prepare the database for use with Db2 Net Search Extender:

db2text ENABLE DATABASE FOR TEXT CONNECT TO sample

You need to do this step only once for each database.

Creating a table

db2 "CREATE TABLE books (isbn VARCHAR(18) not null PRIMARY KEY,
      author VARCHAR(30), story CLOB(100k), year INTEGER)"

This Db2 command creates a table called books. It contains columns for the author, story, isbn number, and the year the book was published. Note that the table must have a primary key.

Creating a full-text index

db2text "CREATE INDEX db2ext.myTextIndex FOR TEXT ON books (story)
         CONNECT TO sample"

This command creates a full-text index for the column story. The name of the text index is db2ext.myTextIndex

Loading sample data

db2 "INSERT INTO books VALUES ('0-13-086755-1','John', 'A man was 
     running down the street.',2001)"
db2 "INSERT INTO books VALUES ('0-13-086755-2','Mike', 'The cat hunts
     some mice.', 2000)"
db2 "INSERT INTO books VALUES ('0-13-086755-3','Peter', 'Some men 
     were standing beside the table.',1999)"

These commands load the isbn, author, story, and publishing year for three books into the table.

Synchronizing the text index

To update the text index with data from the sample table, use the following command:

db2text "UPDATE INDEX db2ext.myTextIndex FOR TEXT CONNECT TO sample"

Searching with the text index

To search the text index, use the following CONTAINS scalar search function:

db2 "SELECT author, story FROM books WHERE CONTAINS
    (story, '\"cat\"') = 1 AND YEAR >= 2000"

Note: Depending on the operating system shell you are using, you might need a different escape character in front of the double quotation marks surrounding the text search phrase. The previous example, uses "\" as an escape character.

This query searches for all books containing the term cat where the year value of the book is greater or equal to 2000. The query returns the following result table:

AUTHOR	Mike
STORY		The cat hunts some mice.

Other functions supported include SCORE and NUMBEROFMATCHES. SCORE returns an indicator on how well the search argument describes a found document. NUMBEROFMATCHES returns how many matches of the query terms are found in each resulting document.