IBM Books
(C) IBM Corp. 2000

DB2 Net Search Extender Administration and User's Guide


Simple example with cache usage and stored procedure search

Use the following steps in the DB2 Net Search Extender stored procedure search example:

  1. Creating a text index with cache option.
  2. Synchronizing the index and activating the cache.
  3. Searching with the TEXTSEARCH Stored Procedure.
Note

The stored procedure example assumes that the steps from the previous example are complete and that the database is still enabled.

Creating a text index with cache option
As the database is already enabled, use the following command to create a full-text index:
db2text "CREATE INDEX db2ext.mySTPTextIndex FOR TEXT ON books (story) 
        CACHE TABLE (author, story) MAXIMUM CACHE SIZE 1 
        CONNECT TO sample"

In this example, the full-text index is for the column story and it specifies a cache table on the columns author and story. The name of the text index is mySTPTextIndex.

Synchronizing the index and activating the cache
To update the index according to the data inserted into the table, use the following command:
db2text "UPDATE INDEX db2ext.mySTPTextIndex FOR TEXT CONNECT TO sample"

To activate the cache, use the following command:

db2text "ACTIVATE CACHE FOR INDEX db2ext.mySTPTextIndex FOR TEXT 
        CONNECT TO sample"

Searching with the TEXTSEARCH Stored Procedure
You can only use the DB2 Net Search Extender stored procedure in certain cases. For details, see Using a stored procedure search.
 db2 "call db2ext.textSearch
        ('\"cat\"','DB2EXT','MYSTPTEXTINDEX',0,2,0,0,?,?)"

This query searches for all books about a cat, but only returns the first two results. In this case, the query returns the following result table:

Value of output parameters
--------------------------
Parameter Name  : SEARCHTERMCOUNTS
Parameter Value : 1
Parameter Name  : TOTALNUMBEROFRESULTS
Parameter Value : 1
 
AUTHOR    STORY
Mike      The cat hunts some mice.
 
Return Status = 0 

For more samples about the search syntax check the following file: sqllib\sample\db2ext\search

For details about the other parameters used in the query, see Searching for text using a stored procedure search.


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]