-- *****************************************************************************
-- Licensed Materials - Property of IBM                                        *
--                                                                             *
-- (c) Copyright IBM Corporation 2008.                                         *
-- All rights reserved.                                                        *
--                                                                             *
-- US Government Users Restricted Rights - Use, duplication or                 *
-- disclosure restricted by GSA ADP Schedule Contract with                     *
-- IBM Corporation.                                                            *
--******************************************************************************
--  Permission Notice                                                          *
--                                                                             *
--  Permission is granted to copy, use, modify, and merge this sample          *
--  software into your applications and to permit others to do any of the      *
--  foregoing. You may further distribute this software for                    *
--  commercial purposes only as part of your application that adds             *
--  significant value and function beyond that provided by these               *
--  samples.                                                                   *
--  You must include this permission statement and retain the copyright        *
--  notice in all copies and modified versions of this software.               *
--                                                                             *
--******************************************************************************
--                                                                             *
--  DISCLAIMER OF WARRANTIES                                                   *
--                                                                             *
--  The sample software is provided to you by IBM to assist you in             *
--  developing your applications. THIS SOFTWARE IS PROVIDED AS-IS,             *
--  WITHOUT WARRANTY OF ANY KIND. IBM SHALL NOT BE LIABLE FOR ANY              *
--  DAMAGES ARISING OUT OF YOUR USE OR THE USE BY ANY THIRD PARTY OF           *
--  OF THE SAMPLE SOFTWARE EVEN IF IT HAS BEEN ADVISED OF THE POSSIBILITY      *
--  OF SUCH DAMAGES. IN ADDITION, IBM SHALL NOT BE LIABLE FOR ANY THIRD        *
--  PARTY CLAIMS AGAINST YOU.                                                  *
--                                                                             *
--******************************************************************************
--
-- DB2 Net Search Extender
--
-- Version:       9.7
-- Content:       Samples for the usage of DB2 Net Search Extender
-- Prerequisite:  1. nsesample executed (nsesample.bat for windows, 
--		     nsesample for unix)
--                2. Net Search Extender has to be started using
--                   'db2text start'.
--                3. Connect to the database used for the sample.
-- Usage:         Call from the operating system command line:
--                db2 -tvf search
--                (For Windows NT: first enter the db2 command line environment
--		   with db2cmd command )
-- ****************************************************************************

    SELECT AUTHOR,TITLE
        FROM DB2EXT.TEXTTAB
        WHERE CONTAINS(COMMENT, '"book"') = 1;

    SELECT AUTHOR,TITLE,
        NUMBEROFMATCHES(COMMENT, '"book"')
        FROM DB2EXT.TEXTTAB;

    WITH TEMPTABLE(docid,score)
        AS (SELECT docid,
                   SCORE(COMMENT,'"book"')
            FROM DB2EXT.TEXTTAB)
    SELECT *
            FROM TEMPTABLE
            WHERE score > 0  
            ORDER BY score ASC;

    SELECT AUTHOR,TITLE
	    FROM DB2EXT.TEXTTAB
	    WHERE CONTAINS(COMMENT, '"mystery bestseller"') =1;

    SELECT AUTHOR,TITLE
        FROM DB2EXT.TEXTTAB
        WHERE CONTAINS(COMMENT,
          '("kid", "dinosaur")') = 1;

    SELECT AUTHOR, TITLE
        FROM DB2EXT.TEXTTAB
        WHERE CONTAINS(COMMENT, 
                              '"author" | "pulitzer" ') = 1;

    SELECT AUTHOR, TITLE
        FROM DB2EXT.TEXTTAB
        WHERE CONTAINS(COMMENT, 
                       '"author" | "pulitzer" & "book"') = 1;

    SELECT AUTHOR, TITLE
        FROM DB2EXT.TEXTTAB
        WHERE CONTAINS(COMMENT, 
	'("bestseller" | "thriller") & NOT "mystery"')=1;

    SELECT AUTHOR,TITLE
        FROM DB2EXT.TEXTTAB
        WHERE CONTAINS(COMMENT, '"thr%er"') = 1;

    SELECT AUTHOR, TITLE
        FROM DB2EXT.TEXTTAB
        WHERE CONTAINS(COMMENT, '"th_iller"') = 1;

    SELECT AUTHOR, TITLE
        FROM DB2EXT.TEXTTAB
        WHERE CONTAINS(COMMENT, 'precise form of "Pulitzer"') =1;

    SELECT AUTHOR, TITLE
        FROM DB2EXT.TEXTTAB
        WHERE CONTAINS(COMMENT, 'fuzzy form of 80 "pullitzer"') =1;

    SELECT AUTHOR, TITLE
        FROM DB2EXT.TEXTTAB
        WHERE CONTAINS(COMMENT, 'stemmed form of "shock"') =1;

    SELECT AUTHOR, TITLE
        FROM DB2EXT.TEXTTAB
        WHERE CONTAINS(COMMENT, 
                    '"100!%" ESCAPE "!"') = 1;

    SELECT AUTHOR,TITLE
        FROM DB2EXT.TEXTTAB
        WHERE CONTAINS(COMMENT,
           '"web" IN SAME SENTENCE AS "disk"') = 1;

    SELECT AUTHOR, TITLE
        FROM DB2EXT.TEXTTAB
        WHERE CONTAINS(COMMENT,
           '"computer" IN SAME PARAGRAPH AS "web"') = 1;

    SELECT CATEGORY, DATE
        FROM DB2EXT.HTMLTAB
        WHERE CONTAINS(HTMLFILE,
                           'SECTIONS ("h2") "IBM"') = 1;

    SELECT CATEGORY, DATE
        FROM DB2EXT.HTMLTAB
        WHERE CONTAINS(HTMLFILE,
           'THESAURUS "nsesamplethes"
           EXPAND RELATED
           TERM OF "product"') = 1;

    SELECT CATEGORY, DATE
        FROM DB2EXT.HTMLTAB
        WHERE CONTAINS(HTMLFILE,
           'THESAURUS "nsesamplethes"
           EXPAND SYNONYM
           TERM OF "product"') = 1;

    SELECT AUTHOR, TITLE
        FROM DB2EXT.TEXTTAB
        WHERE CONTAINS(COMMENT,
        'IS ABOUT EN_US "something related to dinosaur"') = 1;

    SELECT AUTHOR, TITLE
        FROM DB2EXT.TEXTTAB
        WHERE CONTAINS(COMMENT,
        '(ATTRIBUTE "PRICE"  between 9 and 20)') = 1;

    SELECT PRIMKEY
        FROM TABLE(db2ext.textsearch
        ('"book"','DB2EXT','COMMENT',0,2,cast(NULL as INTEGER))) as TP;

    SELECT p.docid, substr(db2ext.highlight(p.comment, tp.hitinformation, 'Window_NUMBER = 1, WINDOW_SIZE = 110, TAGS = ("<bf>", "</bf>")' ),1,60) as Highlight
    FROM db2ext.texttab p, TABLE(db2ext.textsearch ('"book"','DB2EXT','COMMENT',0,2,cast(NULL as INTEGER), 10)) tp
    where tp.primkey = p.docid;    

    call db2ext.textsearch('"book"','DB2EXT','COMMENT',0,2,0,1,?,?);    

