SCORE

The SCORE function searches a text search index using criteria that are specified in a search argument and returns a relevance score that measures how well a document matches the query.

Requirement: To use the SCORE function, Text Search for DB2® for z/OS® must be installed and configured. See IBM® Text Search for DB2 for z/OS for more information.

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

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

Notes:

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

search-argument-options

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

Notes:

The same clause must not be specified more than once.

The schema is SYSIBM.

column-name

Specifies 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 that is 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. 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

Specifies an expression that returns a value that is a string value (except a LOB) that contains the terms to be searched for and must not be all blanks or the empty string. The actual length of the string must not exceed 4096 Unicode characters. 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.

string-constant

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

The options that can be specified as part of the search-argument-options are as follows:

QUERYLANGUAGE = value

Specifies the query language. The value can be any of the supported language codes. If the QUERYLANGUAGE option is not specified, the default is the language value of the text search index that is used when this function is invoked. If the language value of the text search index is AUTO, the default value for QUERYLANGUAGE is en_US.

RESULTLIMIT = value

Specifies the maximum number of results that are to be returned from the underlying search engine. The value can be an integer value between 1 and 2 147 483 647. If the RESULTLIMIT option is not specified, no result limit is in effect for the query.

This scalar function cannot be called for each row of the result table, depending on the plan that the optimizer chooses. This function can be called once for the query to the underlying search engine, and a result set of all of the primary keys that match are returned from the search engine. This result set is then joined to the table containing the column to identify the result rows. In this case, the RESULTLIMIT value acts like a FETCH FIRST ?? ROWS from the underlying text search engine and can be used as an optimization. If the search engine is called for each row of the result because the optimizer determines that is the best plan, then the RESULTLIMIT option has no effect.

SYNONYM = OFF or SYNONYM = ON

Specifies whether to use a synonym dictionary that is associated with the text search index. Use the Synonym Tool to add a synonym dictionary to the collection. The default is OFF.

OFF: Do not use a synonym dictionary.
ON: Use the synonym dictionary that is 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 the null value. If the third argument is null, the result is as if the third argument was not specified.

The result is greater than 0 but less than 1 if the column contains a match for the search criteria that the search argument specifies. The better a document matches the query, the more relevant the score and the larger the result value. If the column does not contain a match, the result is 0.

SCORE is a non-deterministic function.

Example

Assume that information about employees' skills are stored in a table named EMP_RESUME. The following statement generates a list of employees in the order of how well their resumes matches the query "programmer AND (java OR cobol)", along with a relevance value that is normalized between 0 (zero) 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

DB2 first evaluates the CONTAINS predicate in the WHERE clause, and therefore, does not evaluate the SCORE function in the SELECT list for every row of the table. In this case, the arguments for SCORE and CONTAINS must be identical.