Scenario: Indexing and searching

After you have installed and configured Db2® Text Search, there are four steps that you must take before performing searches.

Important: Net Search Extender (NSE) is no longer supported in Db2. Use the Db2 Text Search feature.
  1. Start the Db2 Text Search instance services.
  2. Prepare the database for use by Db2 Text Search.
    Enable the database and use the configure procedure to complete the Text Search server association. You must enable the database only once for Db2 Text Search. The configure procedure is necessary in the following cases:
    • enablement was incomplete
    • for partitioned databases
    • for stand-alone Text Search server setups.
    Note that you cannot enable Net Search Extender for a database once it has been enabled for Db2 Text Search.
  3. Create a text search index on a column that contains, or will contain, text that you want to search.
  4. Populate the text search index. This adds data to the empty, newly created text search index.

    To set up automatic updates for text search indexes according to specified update frequencies, see the topic about scheduling a Db2 Text Search index update.

After a text search index contains data, you can search the index using an SQL statement and can search with XQuery if the index contains XML data.
As Figure 1 shows, you should update existing text search indexes, either manually or automatically, to reflect changes to the text column that the index is associated with.
Figure 1. Setting up text search indexes for searching in a non-partitioned instance with an integrated Text Search server
Text search indexes require an initial update before you can search them and incremental updates for data changes

Basic scenario

Suppose that you want to make the products in the PRODUCT table in the SAMPLE database searchable by Db2 Text Search. Assuming that you already created the sample database (by running the db2sampl command) and that you set the DB2DBDFT environment variable to SAMPLE, you could issue the following commands: 
	db2ts START FOR TEXT
	db2ts ENABLE DATABASE FOR TEXT
	db2ts CREATE INDEX myschema.productindex FOR TEXT ON product(name)
	db2ts UPDATE INDEX myschema.productindex FOR TEXT
The product names and descriptions contained in the NAME column of PRODUCT are now indexed and searchable. If you want to find the product IDs of all the snow shovels, you can issue the following search query:
	db2 "SELECT pid FROM product WHERE CONTAINS (name, 'snow shovel') = 1"

Coexistence scenario for Db2 Text Search and Net Search Extender

If a database is already enabled for Net Search Extender, and you want to use Text Search in that database, you can use the index coexistence feature to query the database.

Start the database for text search. 

db2ts start for text 
DB20000I  The SQL command completed successfully.

Enable Text Search for a database where Net Search Extender indexes are already present. 

db2ts enable database for text 
CIE00001 Operation completed successfully

Create and update a Db2 Text Search index on a column which has an existing Net Search Extender index.

db2ts "CREATE INDEX db2ts.title_idx FOR TEXT ON books(title)" 
CIE00001 Operation completed successfully.

db2ts "UPDATE INDEX db2ts.title_idx FOR TEXT" 
CIE00001 Operation completed successfully.

Activate the new Db2 Text Search index to switch query processing from the NSE index to the new index. 

db2ts "ALTER INDEX db2ts.title_idx FOR TEXT SET ACTIVE" 
CIE00001 Operation completed successfully.

Issue a query to use the Db2 Text Search index.

db2 "select isbn, title from books where contains(title,'top')=1"  

ISBN              TITLE                                
--------------    ------------------------------------- 
123-014014014     Climber's Mountain Tops  
111-223334444     Top of the Mountain: Mountain Lore   
2 record(s) selected.

Queries that attempt to use both types of text indexes are not supported. For example, here the title column has an active Db2 Text Search index, while the bookinfo column has an active Net Search Extender index. The search will return an error because all text indexes in one query must be of the same index type. 

db2 "select isbn, title from books where contains(title, 'top')=1 and 
contains(bookinfo, '" MOUNTAIN "')=1"   

ISBN              TITLE                                                                                      
------------------ ---------------------------------------------- 

SQL20425N Column "BOOKINFO" in table "BOOKS" was specified as an argument to 
a text search function, but a text search index does not exist for the column. 
SQLSTATE=38H12

To avoid this error, create a Db2 Text Search index on the bookinfo column and activate it. 

db2ts "CREATE INDEX db2ts.bookinfo_idx FOR TEXT ON books( bookinfo )" 
CIE00001 Operation completed successfully. 

db2ts ALTER INDEX db2ts.bookinfo_idx FOR TEXT set active 
CIE00001 Operation completed successfully.