Searching for text using an SQL Table-Valued Function

Use the SQL Table-Valued Function where you implement an interface, but do not need all results back, or do not have all the real memory required to use the stored procedure interface.

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

The SQL Table-Valued Function gives you the same cursor interface as the stored procedure to get 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

Note that only a single primary key column is allowed. See the DB2EXT.TEXTSEARCH for further details on the parameters.