When a search argument finds too many occurrences, it can often be useful to narrow, or refine, the search by combining the initial search argument with a second search argument in a Boolean-AND relationship.
You can refine search results without using the REFINE function, by storing the results in a table and making the next search against this table. However, depending on the number of qualifying terms, this method is less efficient than that of storing the latest search argument and using REFINE.
The following steps show how to make a search, and then refine it using the REFINE function. The REFINE function returns a search argument that is a Boolean-AND combination of its two input parameters. The combined search argument returned by REFINE is a value of type LONG VARCHAR.
Create a table PREVIOUS_SEARCHES to hold the search arguments of searches that have already been made.
CREATE TABLE PREVIOUS_SEARCHES (step INT, searchargument LONG VARCHAR)
![]() |
Search for the word "compress" in the sample table.
SELECT COMMENT FROM DB2TX.SAMPLE WHERE DB2TX.CONTAINS (COMMENTHANDLE, '"compress"') = 1
Insert the search argument into the PREVIOUS_SEARCHES table for use by further steps.
INSERT INTO PREVIOUS_SEARCHES VALUES (1, '"compress"')
![]() |
Assuming that the search returns too many text documents, refine the search by combining the previous search term with the word "compiler" using the REFINE function.
WITH LAST_STEP(STEP_MAX) AS (SELECT MAX(STEP) FROM PREVIOUS_SEARCHES), LAST_SEARCH(LAST_SEARCH) AS (SELECT SEARCHARGUMENT FROM PREVIOUS_SEARCHES,LAST_STEP WHERE STEP = STEP_MAX) SELECT COMMENT FROM DB2TX.SAMPLE, LAST_SEARCH WHERE DB2TX.CONTAINS(COMMENTHANDLE, DB2TX.REFINE(LAST_SEARCH, '"compiler"')) = 1
Insert the refined search argument into the PREVIOUS_SEARCHES table for use by further steps.
INSERT INTO PREVIOUS_SEARCHES WITH LAST_STEP(STEP_MAX) AS (SELECT MAX(STEP) FROM PREVIOUS_SEARCHES) SELECT STEP_MAX+1, DB2TX.REFINE(SEARCHARGUMENT, '"compiler"') FROM PREVIOUS_SEARCHES, LAST_STEP
![]() |
You can repeat this step until the number of text documents found is small enough.