DB2EXT.TEXTSEARCH (for stored procedure search)

Function syntax

db2ext.TextSearch(

        IN      query                   VARCHAR(4096),
        IN      indexSchema             VARCHAR(128),
        IN      indexName               VARCHAR(128),
        IN      resultFirstRow          INTEGER,
        IN      resultNumberRows        INTEGER,
        IN      scoringFlag             INTEGER,
        IN      searchTermCountsFlag    INTEGER,
        OUT     searchTermCounts        VARCHAR(4096),
        OUT     totalNumberOfResults    INTEGER ) 

Function parameters

The following are input parameters.

Query
See Syntax of search arguments for further information.
indexSchema, indexName
To identify the index to search. Refer to CREATE INDEX.
resultFirstrow
The query result list is returned in parts. The parameter describes which row of the query result list is the first one to be put into the result set of the stored procedure. The first row in the query result list is identified by the number 0.
resultNumberRows
This parameter describes how many rows of the query result list are put into the result set of the stored procedure.

This is not to be confused with the "result limit" expression in the query, which determines the maximum size of the query result list.

The value should be >= 0. Where 0 means that all the results need to be returned.

Note

If a larger result set is requested, ensure that a temporary user tablespace is available. If there is none available, then create a tablespace. The following example creates a tablespace on a UNIX platform:

db2 "create user temporary tablespace tempts managed by system 
            using ('/work/tempts.ts')"
scoringFlag
0 means there is no scoring and 1 means there is scoring. If scoring is requested, an additional column with the score values is returned with the highest value first.
searchTermCountsFlag
This controls the searchTermCounts processing. If searchTermCountsFlag is 0, the searchTermCounts is not calculated.

Function parameters

The following are output parameters.

searchTermCounts
The number of occurrences of each search term query in the index. These counts are returned as a blank separated list in the order of search terms in the query.

See the searchTermCountsFlag for information.

totalNumberOfResults
The total number of results found in the query result list.

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

Usage

The columns in the result set returned by the stored procedure are given by the CACHE TABLE option of the DB2TEXT CREATE INDEX command. If scoringFlag=1, then a column of type double is added. This column contains the SCORE value.

Use the following options to increase the performance of a second query with the same string as the first query. Note that this must be in a different cursor window with no totalNumberOfResults required:

To ensure that you connect to the correct node for searching, it may be necessary to set the DB2NODE environment variable.

For UNIX, use the following command:

export DB2NODE=<no>

Note that it is important that all physical nodes have a synchronized time.

For Windows, use:

set DB2NODE= <no>
Note

A fenced user ID that is different from the instance owner ID does not work with partitioned databases.