IBM Books
(C) IBM Corp. 2000

DB2 Net Search Extender Administration and User's Guide

DB2EXT.TEXTSEARCH

In addition to the stored procedure search and the SQL scalar search functions, Net Search Extender provides two SQL table-valued functions which look very similar to the stored procedure.

Both table-valued functions are called db2ext.textsearch. The only difference between them is that one supports the HIGHLIGHT function and has two additional parameters, numberOfHits and hitInformation.

Note that you can not use the table-valued function on tables with a compound primary key.

For information on using the HIGHLIGHT function, see DB2EXT.HIGHLIGHT.

Note

The table-valued function may be used in a distributed DB2 environment only if the user table is stored in a single-node tablespace. You must also ensure that you connect to the correct node using the DB2NODE environment variable.

Function syntax

1. db2ext.textsearch without highlight support
 
                
db2ext.textSearch
                (
                query	   	        VARCHAR(4096),
                indexSchema   	   VARCHAR(128),
                indexName        	VARCHAR(128),
                resultFirstRow   	INTEGER,
                resultNumberRows 	INTEGER,
                primKeyBinding   	<supported types>,// same type as primary key
                )
 
                return table
                (
                primKey           <supported types>,// same type as primary key
                numberOfMatches   INTEGER,
                score             DOUBLE,
                totalNbResults    INTEGER
                )
 
 
2. db2ext.textsearch with highlight support
 
 
                db2ext.textSearch
                (
                query	   	        VARCHAR(4096),
                indexSchema       VARCHAR(128),
                indexName        	VARCHAR(128),
                resultFirstRow   	INTEGER,
                resultNumberRows 	INTEGER,
                primKeyBinding   	<supported types>,// same type as primary key
                numberOfHits      INTEGER
                )
 
                return table
                (
                primKey           <supported types>,// same type as primary key
                numberOfMatches   INTEGER,
                score             DOUBLE,
                totalNbResults    INTEGER
                hitInformation    BLOB(20K)
                )
 
 

Function parameters

The following are input parameters.

query
See Chapter 14, Syntax of search arguments for additional information.

indexSchema, indexName
Identifies the index to search. For more information, see CREATE INDEX.

resultFirstRow
The result list of the query is returned in parts. This parameter describes which row of the query result list is the first one to be entered into the result table of the table-valued function. The value should be >= 0.

Note that the number 0 identifies the first row in the query result list.

resultNumberRows
This parameter describes how many rows of the query result list are entered into the result table of the table-valued function, and where 0 means that all the results need to be returned.

Note that this is different from the result limit query parameter that determines the maximum size of the query result list.

primaryKeyBinding
The type of this parameter determines the type of the primaryKey Output parameter. If the text index has been created for a base table with a primary key of type <type1>, then primaryKeyBinding must also be of type <type1>.

Additionally, the parameter determines the scope of the text search. If primaryKeyBinding is set to NULL ("CAST(NULL as <type1>)", the scope of the search will be all the documents stored in the index. Alternatively, you can restrict the search to documents primaryKeyBinding is bound to.

For example, if primaryKeyBinding is set to CAST(5 as BIGINT), you restrict the search to the single document with the BIGINT primary key value of "5".

Note that only single column primary keys of the following types are supported: SMALLINT, INTEGER, BIGINT, REAL, DOUBLE, VARCHAR FOR BIT DATA, DATE, TIME, and TIMESTAMP.

numberOfhits
This option specifies the maximum number for hit information returned by the db2ext.textsearch function. If 0 is specified, the information for up to a maximum of 1100 hits is provided. This process may be time-consuming.

Note that this parameter is only necessary for constructing the highlight information required by the db2ext.highlight function.

Function parameters

The following return values are stored in a temporary table which needs to be joined to your normal table if further results are requested. Note that the NUMBEROFMATCHES, SCORE, TOTALNUMBEROFRESULTS, and HITINFORMATION are only calculated if they are requested in your select statement.

primKey
The primary key of the found document.

numberofmatches
NUMBEROFMATCHES is an INTEGER value indicating how many matches resulted for each document.

score
Score returns a DOUBLE value. As the search term increases in frequency in the document, the document score increases.

totalNumberOfResults
The query result list denotes how many results were found. Note that each row has the same value.

Also note that when you use the STOP SEARCH AFTER, or the RESULT LIMIT together with the SCORE syntax in a query, this number is no longer reliable.

hitInformation
The hit information returned by db2ext.textsearch is necessary for highlight processing. Currently, hit information for approximately 1100 hits can be contained in this output parameter. If the number of hits exceeds this threshold, hit information for these further hits is ignored.

Note that this value is only returned if you specify numberOfHits.

Usage

With the SQL table-valued function you are able to search on views in the same way you do with the stored procedure search. The exception being that no shared memory is needed, so the index does not need to be activated.

This function is primarily for those users who have used an SQL query within the stored procedure search. However, the restriction is that only a single column primary key on base tables is supported.

The following example shows how you can work on a multi-column primary key table:

select s.id from 
db2ext.sample s, table (db2ext.textSearch( 
        '"characteristics"',
         'DB2EXT',
         'COMMANDS',
         1,
         20,
         cast(NULL as INTEGER))) t 
where s.id = t.primkey

In this example, you must first create a view on this table with a single unique key and then create the index on this view.

For an example of using the SQL table-valued function with the db2ext.highlight function, see page ***.


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