DB2 10.5 for Linux, UNIX, and Windows

SCORE function

The SCORE function searches a text search index using criteria that you specify in a search argument and returns a relevance score that measures how well a document satisfies the query as compared with the other documents in the column.

Function syntax


>>-SCORE--(--column-name--,--search-argument-------------------->

>--+------------------------+--)-------------------------------><
   |                    (1) |      
   '-,--string-constant-----'

Notes:

string-constant must conform to the rules for search-argument-options.


search-argument-options

   .----------------------------------.   
   V  (1)                             |   
|--------+- ------------------------+-+-------------------------|
         +-QUERYLANGUAGE--=--locale-+     
         +-RESULTLIMIT--=--value----+     
         |             .-OFF-.      |     
         '-SYNONYM--=--+-ON--+------'

Notes:

You cannot specify the same clause more than once.

The schema is SYSIBM.

Function parameters

column-name

A qualified or unqualified name of a column that has a text search index that is to be searched. The column must exist in the table or view identified in the FROM clause in the statement and the column of the table, or the column of the underlying base table of the view, must have an associated text search index (SQLSTATE 38H12). The underlying expression of the column of a view must be a simple column reference to the column of an underlying table, either directly or through another, nested view.

search-argument

An expression that returns a value that is a string value (except a LOB) that contains the terms to be searched for and is not all blanks or the empty string (SQLSTATE 42815). The string value that results from the expression should be less than or equal to 4096 bytes (SQLSTATE 42815). The value is converted to Unicode before it is used to search the text search index. The maximum number of terms per query must not exceed 1024 (SQLSTATE 38H10).

string-constant

A string constant that specifies the search argument options that are in effect for the function.

The options that you can specify as part of the search-argument-options are as follows:

QUERYLANGUAGE = locale

Specifies the locale that the DB2® Text Search engine uses when performing a text search on a DB2 text column. The value can be any of the supported locales. If you do not specify QUERYLANGUAGE, the default is the locale of the text search index. If the LANGUAGE parameter of the text search index is AUTO, the default value for QUERYLANGUAGE is en_US.

RESULTLIMIT = value

If the optimizer chooses a plan that calls the search engine for each row of the result set to obtain the SCORE, then the RESULTLIMIT option has no effect on performance. However, if the search engine is called once for the entire result set, RESULTLIMIT acts like a FETCH FIRST clause.

When using multiple text searches that specify RESULTLIMIT in the same query, use the same search-argument. If you use different search-argument values, you might not receive the results that you expect.

For partitioned text indexes, the result limit is applied to each partition separately.

Note: If the number of results is an issue, limit the number of results through a refinement of the search terms, rather than by using RESULTLIMIT. Because RESULTLIMIT returns at most the specified number of results with no consideration of their scores, the highest-ranking documents might not be included.

SYNONYM = OFF | ON

Specifies whether to use a synonym dictionary that is associated with the text search index. The default is OFF. To use synonyms, add the synonym dictionary to the text search index using the Synonym Tool.

OFF: Do not use a synonym dictionary.
ON: Use the synonym dictionary associated with the text search index.

The result of the function is a double-precision floating-point number. If the second argument can be null, the result can be null; if the second argument is null, the result is null. If the third argument is null, the result is as if you did not specify the third argument.

The result is greater than 0 but less than 1 if the column contains a match for the search criteria specified by the search argument. The more frequently a match is found, the larger the result value. If the column does not contain a match, the result is 0.

SCORE is a non-deterministic function.

Note: You must take additional steps when using parameter markers as a search argument inside the text search functions. Parameter markers do not have a type when precompiled in JDBC and ODBC programs, but the search argument in the text search functions must resolve to a string value. Because the unknown type of the parameter marker cannot be resolved to a string value (SQLCODE -418), you must explicitly cast the parameter marker to the VARCHAR data type.

Example

The following query is used to generate a list of employees in order of how well their resumes satisfy the query "programmer AND (java OR cobol)", along with a relevance value that is normalized between 0 and 100:

	SELECT EMPNO, 
		INTEGER(SCORE(RESUME, 
			'programmer AND (java OR cobol)') * 100) AS RELEVANCE 
			FROM EMP_RESUME 
			WHERE RESUME_FORMAT = 'ascii' 
				AND CONTAINS(RESUME, 'programmer AND (java OR cobol)') = 1 
		ORDER BY RELEVANCE DESC

Usage notes

The SCORE value reflects a document's relative relevance when compared to the SCORE value of all documents from the same text index collection. For a partitioned database a text index may consist of multiple collections, however document scores are not normalized across partitions. Comparing or sorting SCORE values across text index collections is therefore not meaningful and does not provide a proper measure of relevance for documents in a partitioned text index.