IBM Books
(C) IBM Corp. 2000

DB2 Net Search Extender Administration and User's Guide

Creating a text index on a nonsupported data type

To create an index, the text columns must be one of the following data types:

If the documents are in a column of a different type, such as a user-defined type (UDT), you must provide a function that takes the user type as input and provides as an output type one of the above-mentioned types.

Specify the name of this transformation function. See CREATE INDEX for further information.

Example: You intend to store compressed text in a table.

  1. Create a user-defined type (UDT) for the text in an interactive SQL session:
    db2 "CREATE DISTINCT TYPE COMPRESSED_TEXT AS CLOB(1M)"
    
  2. Create a table and insert the text into it:
    db2 "CREATE TABLE UDTTABLE (author VARCHAR(50) not null,
                                  text COMPRESSED_TEXT, primary key (author))"
    db2 "INSERT ..."
    
  3. Create a user-defined function (UDF) called, for example, uncompress. This receives a value of type COMPRESSED_TEXT and returns the corresponding uncompressed text as, for example, a CLOB(10M) value.
  4. Create your text index in the following way to specify the uncompress UDF:
    db2text "CREATE INDEX UDTINDEX for text ON UDTTABLE
                                   (uncompress(text))
                                   ..."
    


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]