Creating a text search index on unsupported data types

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

About this task

A text column in an index must be one of the following supported types:
  • CHAR
  • VARCHAR
  • LONG VARCHAR
  • CLOB
  • GRAPHIC
  • VARGRAPHIC
  • LONG VARGRAPHIC
  • DBCLOB
  • BLOB
  • XML
To convert the data type of the column to one of valid types, use one of the following methods:
  • Run the db2ts CREATE INDEX command with the name of a transformation function.
    	db2ts "CREATE INDEX index-name FOR TEXT ON 
	table-name (function-name(text-column-name))"
  • Use a user-defined external function (UDF), which is specified by function-name, that accesses text documents in a column that is not of a supported type for text searching, performs a data-type conversion of that value, and returns the value as one of the supported data types.

Example

In the following example, there is a table UDTTABLE that contains a column of a user-defined type (UDT) named "COMPRESSED_TEXT", which is defined as CLOB(1M). To create an index on that data type, first create a UDF called UNCOMPRESS, which receives a value of type COMPRESSED_TEXT. Next, create your text search index in the following way:
	db2ts "CREATE INDEX UDTINDEX FOR TEXT ON 
	UDTTABLE (UNCOMPRESS(text)) ..."