Simple example with the SQL scalar search function

Use the following steps in the DB2 Net Search Extender example:

  1. Creating a database
  2. Enabling a database for text search
  3. Creating a table
  4. Creating a full-text index
  5. Loading the sample data
  6. Synchronizing the text index
  7. 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"
Use the following command to 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 LONG VARCHAR, 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.
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 the 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 these 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 quotes surrounding the text search phrase. The above example, uses "\" as an escape character.

This query searches for all books about the term cat that are greater or equal to the year 2000. The query returns the following result table:

AUTHOR    STORY
Mike   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 a resulting document.