Usage

Net Search Extender can use UDFs or a stored procedure call to perform searches. The Net Search UDFs are similar to the functions used by Text Extender. They are called Contains and Textsearch. The stored procedure is also called Textsearch. The stored procedure is recommended for high performance when the application needs only a small subset of the data. If the stored procedure needs to retrieve a large amount of the data, it will require more memory and may not be as efficient.

Contains Function

The Contains UDF for Net Search Extender is similar to that used by Text Extender. Refer to Chapter 15 “DB2 Text Extender” or the Net Search Extender Administration Guide for a more detailed description. The Contains UDF can be added to the predicate list of any SQL statement. Note that the schema of the function for Net Search Extender is DB2EXT instead of DB2TX used by Text Extender.

DB2EXT.CONTAINS (index-name,'"search-term-1","search-term-2"[,...]')=1

Here is a sample that shows the db2ext.contains UDF in an SQL statement. This sample can be executed against the sample DB2MALL database. Before this sample can be run, the database must be enabled and the index must be created. This example assumes the name of the index created in the previous step is PRODUCT_NAMEI.

SELECT * FROM mall.product WHERE DB2EXT.CONTAINS (PRODUCT_NAMEI,'"books"')=1

Textsearch Function

The Textsearch UDF is the equivalent to the Search_Result UDF that is used by Text Extender. Instead of calling the function in an SQL statement predicate, it uses the DB2 Table function to generate a temporary source table. The Textsearch table and the original table are joined over the table primary key. This method results in superior performance compared to the Contains UDF. The Textsearch UDF has the following syntax:

DB2EXT.TEXTSEARCH('"search-term"', 'SCHEMA', 'INDEX', first-row, number–of-rows,
 primary-key-binding)

The fields first-row and number–of-rows are used to control the subset of data returned. The primary-key-binding must be the same data type as the primary key. Here is an example that replaces the Contains UDF from the previous example with the Textsearch UDF:

SELECT * FROM mall.product t1, table(DB2TX.SEARCH_RESULT('"books"', 'MALL', 'PRODUCT',
 'PRODUCT_NAMEI', 1, 10, CAST(NULL as INT))) t2 WHERE t1.product_id = t2.primkey

Inequality expressions, such as less than or greater than, for Net Search Extender UDTs are also UDFs. When the database is enabled, the expressions are created for each UDT to perform comparisons with the schema DB2EXT. To use these comparison functions, you must qualify the operators with the DB2EXT schema. One alternate to this is to update the DB2 CURRENT FUNCTION PATH special register to include DB2EXT as an expected schema.

Textsearch Stored Procedure

The Textsearch stored procedure has better performance but increased memory requirements. When using the stored procedure, be sure to turn on the cache-search-results option on index creation, activate a cache on the index, and limit the maximum size of the result set to a smaller number of rows. The stored procedure accepts the following parameters. Refer to the Net Search Administration Guide for a complete description of the input parameters.

DB2EXT.TEXTSEARCH('"search-term"', 'SCHEMA', 'INDEX', first-row, number–of-rows,
 rank-flag, search-term-counts-flag, search-term-counts, number-of-results)