DB2/400 SQL : FullText

Question

SQL DB2/400 : Is there somebody has tried to work wth FullText.

If i can have a sql exemple code, it will be great .

I would like, for exemple, to use it with a clob column.

Many thanks

Answer 1

This pulls out all of space delimited words from mylib.myfile[mytext] and puts them into a table with two columns. I limit the word length to 15 cause really when do users type in words longer than 15 chars anyways and I've probably already found a match on the 1st 15 and presented a list to the user.

I copied this technique off the interweb.

CREATE TABLE mylib.myidx AS
(                                   
  WITH SPLITTER (ID, START, E, SECTION, ORIGINAL, NUM) AS 
  ( SELECT UID, 1, LOCATE(' ', mytext), CAST('' AS VARCHAR(8000)), mytext, 0  
      FROM mylib/myfile                                               
    UNION ALL
    SELECT ID, E + 1,
           CASE WHEN LOCATE(' ',ORIGINAL, E + 1) > 0
                THEN LOCATE(' ', ORIGINAL, E + 1)
                ELSE LENGTH(ORIGINAL) + 1
             END,
           SUBSTRING(ORIGINAL, START, E - START), 
           ORIGINAL, NUM + 1
      FROM SPLITTER
      WHERE E > START
  )                 
  SELECT ID AS UID                                                 
        ,CAST(SECTION AS VARCHAR(15)) AS SECTION                         
    FROM SPLITTER
    WHERE LENGTH(SECTION)>1                                 
)
WITH DATA;