Usage

There are two methods of performing a search using Text Extender. The first method is to call UDFs. Using UDFs is the preferred method for most applications. They do not require the Text Extender client instance to be installed on the local machine. Therefore, it is possible to use this method from a Web interface without requiring the user to install the Text Extender client. Because the UDF is referenced directly from SQL statements, it can be used in most programming languages and environments. Any application that can make SQL statements can take advantage of Text Extender. The second method is to use the Text Extender API. The Text Extender API uses the DB2 CLI to communicate with the database. To use the API, the Text Extender client or server must be installed on the local machine where the application is being run.

Text Extender UDFs

Applications can access Text Extender indexes by referencing UDFs from SQL statements. The most commonly used UDFs are Contains, Search_Result, Handle_List, Rank, No_Of_Matches, and Refine. During database enablement, these functions are created under the db2tx schema. The two main functions for performing searches are Contains and Search_result. The Contains UDF is easier to use but is slower on large tables. It is called inside the predicate list of the SQL statement. The search_result UDF provides faster performance for large tables but uses a more complicated syntax. It takes advantage of DB2's Table Valued Function feature. The other UDFs, Handle_List, Rank, No_Of_Matches, are used in combination with the search_result UDF. To retrieve this information, they are added to the SQL statement's select list.

Contains Function

The Contains UDF has a very simple syntax and can be added to the predicate list of any SQL statement. The handle column name is the same name used when enabling the text column. Take note of the double quote inside the single quote surrounding the search text. More than one search string can be provided in a single function call. Multiple search strings can be combined by separating them with commas or by using boolean logic. The “|” charater represents a logical OR. The “&” character represents a logical AND. The syntax is as follows. The word “NOT” is used to negate an expression. The search term may also contain masking characters similar to the LIKE predicate. The percent character, “%”, is used to represent an arbitrary number of characters. The underscore character, “_”, is used to represent exactly one character.

DB2TX.CONTAINS (handle-column-name,'"search-term-1","search-term-2"[,...]')=1

Here is a sample that shows the db2tx.contains UDF in an SQL statement. This sample can be executed against the sample DB2MALL database. Before this sample can be run, the database and PRODUCT_NAME column must be enabled. This sample assumes a column handle called PRODUCT_NAMEH is created on the PRODUCT_NAME column.

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

When running SQL statements on UNIX platforms by preceding them with “db2”, the SQL statement should be put in quotes to prevent the UNIX shell from attempting to parse the statement. The quotes used in the Contains UDF will conflict with the quotes encapsulating the SQL statement unless the backslash escape character is used in front of the quotes on either side of the search text.

In addition to boolean logic, search keywords can be specified to operate on the search terms. Examples of search keywords include, “IN SAME SENTENCE AS”, “IN SAME PARAGRAPH AS”, “SYNONYM FORM OF”, “STEMMED FORM OF”, “FUZZY FORM OF”, “SOUNDS LIKE”, and “IS ABOUT”. A description of these and other search keywords can be found in the Text Extender Administration Guide.

Search_Result Function

Similar to the Contains UDF, the Search_Result UDF is used to look for a search term. The search term uses the same form as used in the Contains UDF. Instead of calling the function in an SQL statement predicate, it uses the DB2 Table function to generate a temporary source table. The Search_Result table and the original table are joined over the text handle column. This method results in superior performance, but is not as easily understood. The Search_Result UDF has the following syntax:

DB2TX.SEARCH_RESULT('SCHEMA', 'TABLE', 'TEXTHANDLE',' "search-term"')

Here is an example that replaces the Contains UDF from the previous example with the Search_Result UDF:

SELECT * FROM mall.product t1, table(DB2TX.SEARCH_RESULT('MALL', 'PRODUCT',
 'PRODUCT_NAMEH',' "books"')) t2 WHERE t1.product_nameh = t2.handle

Equality and relational operators, such as equal to, less than, or greater than, for Text Extender UDTs are themselves considered to be UDTs. When the database is enabled, they are created for each UDT to perform comparisons with the schema DB2TX. To use these comparison functions, you must qualify the operators with the DB2TX schema. One alternate to this is to update the DB2 CURRENT FUNCTION PATH special register to include DB2TX as an expected schema. This enables DB2 to locate special inequality functions needed to compare Text Extender data types. A second alternative is to cast these columns to a DB2 data type such as CHAR(60) before using inequality expressions. For example:

SELECT * FROM mall.product t1, table(DB2TX.SEARCH_RESULT('MALL', 'PRODUCT',
 'PRODUCT_NAMEH',' "books"')) t2 WHERE CAST(t1.product_nameh AS CHAR(60))= CAST(t2.handle
 AS CHAR(60))

Text Extender APIs

Applications can also perform searches using Text Extender API function calls. The API functions provide the same search capabilities as UDFs, but also allow returning a data stream with the highlighted information for matches. The DesGetMatches API function is similar to the UDF Contains. The DesGetSearchResultTable API function is similar to the UDF Search_Result. Table 15.3 lists each Text Extender API and provides a brief description.