DB2 10.5 for Linux, UNIX, and Windows

Searching for text using an SQL Table-Valued Function

Use the SQL Table-Valued Function if you do not need all of the search results, and if you do not have enough memory to use a cached index as used in the stored procedure search.

There are two SQL table-valued functions available, both called db2ext.textsearch. One of them has two additional parameters for use with the db2ext.highlight function.

The SQL Table-Valued Function gives you the same cursor interface as the stored procedure to access only parts of the result. However, you still need to join the results with the user table. You can see this in following example:

db2 "select docid , author, score from TABLE(db2ext.textsearch('\"book\" ',
     'DB2EXT','COMMENT',3,2,cast(NULL as integer))) as t, db2ext.texttab u 
      where u.docid = t.primkey"

The following are the values you could return from the SQL Table-Valued Function:

--> primKey <single primary key type>
the primary key

-->  score         		DOUBLE
the score value of the found document

--> NbResults  	   	INTEGER
the total number of found results (same value for all rows) 

--> numberOfMatches 	INTEGER 
the number of hits in the document

Note:

Only a single primary key column is allowed.
In a partitioned database environment, to be able to search using the SQL Table-Valued Function, the table must use a table space on a single partition and the function should be called on the same partition. Otherwise the search will be disallowed and errors will be returned.