Documentation
(C) IBM Corp. 1996, 1999

Text Extender: Administration and Programming


Improving search performance

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(*).


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]