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.
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.
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) )
The following are input parameters.
Note that the number 0 identifies the first row in the query result list.
Note that this is different from the result limit query parameter that determines the maximum size of the query result list.
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.
Note that this parameter is only necessary for constructing the highlight information required by the db2ext.highlight function.
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.
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.
Note that this value is only returned if you specify numberOfHits.
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 ***.