The SEARCH_RESULT UDF exploits the DB2 concept of table-valued functions. The UDF is used in the FROM clause of an SQL statement, and returns an intermediate table with the search result of the specified search string. The syntax of the search string is the same as described in Chapter 10, Syntax of search arguments. The advantage of this UDF compared with CONTAINS or RANK is a significant performance improvement when large tables are involved.
The returned table has the following structure:
Column Name Datatype HANDLE DB2TX.DB2TEXTH, DB2TX.DB2TEXTFH NUMBER_OF_MATCHES INTEGER RANK DOUBLE
Example:
SET CURRENT FUNCTION PATH = db2tx WITH REPHANDLE (MYDOCHANDLE) AS ( SELECT DB2TX.DB2TEXTH(PROTOTYPEHANDLE) FROM db2tx.textcolumns WHERE TABLESCHEMA = 'DB2TX' AND TABLENAME = 'SAMPLE' AND COLUMNNAME = 'COMMENT' AND HANDLENAME = 'COMMENTHANDLE' ) SELECT NUMBER_OF_MATCHES,RANK,HANDLE FROM REPHANDLE, TABLE(DB2TX.SEARCH_RESULT(MYDOCHANDLE,'"compress"')) T1
SELECT NUMBER_OF_MATCHES,RANK,HANDLE causes all three items to be returned, but you can specify them in any combination. You may wish, for example to omit RANK to avoid the intensive processing associated with it.
If you need only the HANDLE value, you can simply use SELECT COUNT(*).