Db2 Text Search and NSE semantic and syntactic differences for searching

Db2 Text Search and Net Search Extender differ both semantically and syntactically. It is important to be aware of the differences between the two search servers.

Important: Net Search Extender (NSE) is no longer supported in Db2. Use the Db2 Text Search feature.

Use the following tables illustrating plain and structured text searches to help you understand differences in full-text search language processing.

Table 1. Plain text searches
Feature or attribute Net Search Extender syntax Corresponding Db2 Text search syntax Comments
Double quotation marks 
select author, title from
 books where contains
(bookinfo, '"band"') = 1
Example 1:
select author, title from
 books where contains
(bookinfo, 'band') = 1
Example 2:
Select author, year, title
from books where contains(
bookinfo, 'great adventure
') = 1
Net Search Extender uses double-quotation marks. For Db2 Text Search this translates into a phrase search. If you want to apply the default stemmed or lemma search in text search, the double quotation marks must be removed in Db2 Text Search.
Scoring algorithm 
select author, year, 
title, integer(score
(bookinfo, children"')*
100) as relevance from 
books where  contains
(bookinfo, '"children"')
 = 1  order by relevance
 
select author, year, title
, integer(score
(bookinfo, 'children')*100
) as relevance from  books
 where  contains(bookinfo,
 'children') = 1  order by
 relevance
 The scoring algorithm in Net Search Extender is different. This often results in a difference in the ordering of results
Enabling use of synonyms 
select author, year, 
title from books where 
contains(bookinfo, 
'THESAURUS "mysynonyms" 
EXPAND SYNONYM TERM OF 
"clock"') = 1
 
select author, year, 
title from books where 
contains(bookinfo, 
'clock', 'SYNONYM=ON') = 1
  
Boolean operators 
select author, year, 
title from books where 
contains(bookinfo,  
'"children & adventure"')
 = 1 
select author, year, title
 from books where contains
(bookinfo,  'children &&
 adventure') = 1
Net Search Extender uses &, |, NOT operators. Db2 Text Search uses AND (&&), OR (||) and NOT (-) operators.
Case sensitivity
Example 1:
select author, year, 
title from books where 
contains(bookinfo,  
'"great adventure"') = 1
Example 2: 
Select author, year, 
title from books where 
contains(bookinfo,  
'PRECISE FORM OF "tree"')
 = 1
Example 1: 
select author, year, title
 from books where contains
(bookinfo,  '"great 
adventure"') = 1
Example 2: 
Select author, year, title
 from books where contains
(bookinfo,  '"tree"') = 1

In example 1, Net Search Extender searches for the sequence 'great adventure'. This translates into a case-insensitive phrase search in Db2 Text Search.

In example 2, Net Search Extender searches for the cases-sensitive term. Db2 Text Search searches the case-insensitive form of the search term, will find Tree, but not trees.
Wildcard (single character) 
Select author, year, 
title from books where 
contains(bookinfo, 
'"stal_"') = 1  
Select author, year, title
 from books where contains
(bookinfo,  'stal?') = 1
Net Search Extender uses the _ operator. Db2 Text Search uses the ? operator.
Wildcard (multiple characters) 
Select author, year, 
title from books where 
contains(bookinfo, 
'"stal%"') = 1
 
Select author, year, title
 from books where contains
(bookinfo,  'stal*') = 1
Net Search Extender uses the % operator. Db2 Text Search uses the * operator.
Important: a % sign preceding a search term denotes an optional term in Db2 Text Search.
Stemmed search 
Select author, year, 
title from books where 
contains(bookinfo,  
STEMMED FORM OF "children"
') = 1
 
Select author, year, title
 from books where contains
(bookinfo,  'children') = 
1

Stemmed search for Net Search Extender will find 'child' and 'children' in this example. This is default search behavior for Db2 Text Search, a search for children will include documents containing child as well.
Fuzzy search
Example 1:
Select author, year, 
title from books where 
contains(bookinfo,  
'FUZZY FORM OF "children"
') = 1
Example 2:
Select author, year, 
title from books where 
contains(bookinfo, 
'FUZZY FORM OF 80 
children') = 1
Example 1:
Select author, year, title
 from books where contains
(bookinfo,  'children~') =
 1
Example 2:
Select author, year, title
 from books where contains
(bookinfo,  'children~0.8'
) = 1

Example 1 shows the default syntax for a fuzzy search. The default match level of Net Search Extender is 80, the default for Db2 Text Search is 0.5.

Example 2 shows how to specify the degree by explicitly adding a factor.

Proximity search
Example 1:
Select author, year, 
title from books where 
contains(bookinfo, 
'"wizard IN SAME SENTENCE
 AS dragon') = 1
Example 2:
Select author, year, 
title from books where 
contains(bookinfo, 
'"wizard IN SAME 
PARAGRAPH AS dragon') = 1
Example 1:
Select author, year, title
 from books where contains
(bookinfo,  '(wizard 
dragon)~8') = 1
Example 2:
Select author, year, title
 from books where contains
(bookinfo,  '(wizard 
dragon)~30') = 1
 Db2 Text Search uses the number of words, not the grammatical structure.
Restricting the number of results 
Select author, year, 
title from books where 
contains(bookinfo,  
'"children" RESULT 
LIMIT 5') = 1
 
Select author, year, title
 from books where contains
(bookinfo,  'children', 
RESULTLIMIT 5) = 1
  
Free text search 
Select author, year, 
title from books where 
contains(bookinfo,  
'IS ABOUT EN_US 
"something about 
wizards"') = 1
Example 1:
Select author, year, title
 from books where contains
(bookinfo,  'wizards', 
'LANGUAGE EN_US') = 1
Example 2:
Select author, year, title
 from books where contains
(bookinfo,  'something || 
about || wizards', 
'LANGUAGE EN_US') = 1
 The 'is about' option searches for documents containing any of the words, it can be represented either by a query that combines the terms with OR (in case all of them are relevant) or alternatively, simply a search for the interesting term.
Table 2. Structured searches (XML)
Feature or attribute Net Search Extender Corresponding Db2 Text search syntax Comments
Structured search with SQL 
select author, year, 
title from books where 
contains(bookinfo, ' 
SECTION("/bookinfo/story"
) ("Hobbit")')=1
 
select author, year, title
 from books where contains
(bookinfo, '@xmlxp:''
/bookinfo/story 
[. contains(
"hobbit")]''')=1
  
Structured search with XQuery   
xquery db2-fn:xmlcolumn
-contains('BOOKS.BOOKINFO'
,  '@xmlxp:''/bookinfo/
story [. contains("hobbit"
)]''')