#!/bin/ksh

 #*************************************************************************
 # Licensed Materials - Property of IBM                                   *
 # 5724-E49                                                               *
 # 5765-F35                                                               *
 # 5765-F41                                                               *
 # 5724-N76                                                               *
 # 5724-B55
 # (c) Copyright IBM Corporation 2000,2006.                               *
 # 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 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.                                             *
 #                                                                        *
 #*************************************************************************
 # Product Name:      DB2 Net Search Extender
 #
 # Source File Name:  nsesample_partitioned
 #
 # Description:       This script creates a sample database,
 #                    creates a sample tables,
 #                    imports rows of sample data into it,
 #                    enables the database, and enables the text
 #                    columns.
 # *******************************************************************

 # *******************************************************************
 # S Y N T A X :
 #        nsesample_partitioned <databasename> <nodename> <tablespace_file_name>
 #
 #        If database <databasename> does not exist it will be created.
 #        Creates sample tables and imports sample data into it.
 #
 #        If <nodename> and <tablespace_file_name> do not exists, the
 #	  database will be created but no caching for text index
 #
 #
 # ********************************************************************
 #
 # 0. The following steps have been done in advance:
 #    - DB2 Net Search Extender has been installed.
 #    - DB2 default instance has been created.
 #    - copy directory 'nsesamplehtml' into <http_server_root_dir>
 #
 # ********************************************************************
 #
 # Environment: Unix
 #
 # ********************************************************************

 # ********************************************************************
 # Get syntax help
 # ********************************************************************

help1()
{
echo "------------------------------------------------------------------------"
echo "Usage : nsesample_partitioned database_name [ node_num table_space_filename ]" 
echo "------------------------------------------------------------------------"
exit
}

 # Remove nsesample_partitioned log file if exists ********************************
rm $HOME/nsesample_partitioned.log 2> /dev/null

 # ********************************************************************
 # Get database name and http server root directory
 # ********************************************************************


if [[ -z $1 ]] 
  then help1
fi

if [ $1 = -h ] 
  then help1 
fi

if [ $1 = --help ] 
  then help1 
fi

if [[ -n $2 ]]
 then
   if [[ -z $3 ]]
    then help1
   fi
   export DB2NODE=$2
fi

echo "**********************************************************">> $HOME/nsesample_partitioned.log
echo "**********************************************************">> $HOME/nsesample_partitioned.log
echo "Start nsesample_partitioned ...                                       ">> $HOME/nsesample_partitioned.log


echo "Setup the database $1 ..."
 # ********************************************************************
 # Start DB2
 # ********************************************************************
echo "********************************************************* ">> $HOME/nsesample_partitioned.log
echo "Start the database manager:                               ">> $HOME/nsesample_partitioned.log
echo "db2start                                                  ">> $HOME/nsesample_partitioned.log
db2start                                                         >> $HOME/nsesample_partitioned.log

 # ********************************************************************
 # Create database
 # ********************************************************************
echo "**********************************************************">> $HOME/nsesample_partitioned.log
echo "Create database $1                                        ">> $HOME/nsesample_partitioned.log
db2 create database $1                                         >> $HOME/nsesample_partitioned.log


 # ********************************************************************
 # Connect to database
 # ********************************************************************
echo "**********************************************************">> $HOME/nsesample_partitioned.log
echo "Connect to database $1                                    ">> $HOME/nsesample_partitioned.log
db2 connect to $1                                              >> $HOME/nsesample_partitioned.log


if [[ -z $2 ]]
 then
TAB_NAME=""
CACHE=""
 else
 # *******************************************************************
 # Create partition group
 # *******************************************************************
echo "**********************************************************">> $HOME/nsesample_partitioned.log
echo "create database partition group nsesamp on node ($2)       ">> $HOME/nsesample_partitioned.log
db2 "create database partition group nsesamp on node ($2)	 ">> $HOME/nsesample_partitioned.log

 # *******************************************************************
 # Create tablespace in partition
 # *******************************************************************
echo "**********************************************************">> $HOME/nsesample_partitioned.log
echo "create tablespace nsesamp in database partition group nsesamp managed by database using (file '$3' 10000) ON NODES ($2)">> $HOME/nsesample_partitioned.log
db2 "create tablespace nsesamp in database partition group nsesamp managed by database using (file '$3' 10000) ON NODES ($2)">> $HOME/nsesample_partitioned.log
TAB_NAME="in nsesamp"
CACHE="CACHE TABLE(docid, title) TEMPORARY MAXIMUM CACHE SIZE 1"
fi

 # ********************************************************************
 # Create sample table
 # ********************************************************************
echo "********************************************************* ">> $HOME/nsesample_partitioned.log
echo "Create sample table db2ext.texttab in database $1:        ">> $HOME/nsesample_partitioned.log
echo "db2 create table db2ext.texttab (docid INTEGER NOT NULL, author VARCHAR(50), title VARCHAR(50), comment LONG VARCHAR, price DECIMAL(8,2), PRIMARY KEY (docid)   $TAB_NAME">> $HOME/nsesample_partitioned.log
db2 "create table db2ext.texttab (docid INTEGER NOT NULL, author VARCHAR(50), title VARCHAR(50), comment LONG VARCHAR, price DECIMAL(8,2), PRIMARY KEY (docid)) $TAB_NAME" >> $HOME/nsesample_partitioned.log

echo "Create sample table db2ext.htmltab in database $1:">> $HOME/nsesample_partitioned.log
echo "db2 create table db2ext.htmltab (DOCID INTEGER NOT NULL PRIMARY KEY, CATEGORY VARCHAR(20), DATE DATE, HTMLFILE CLOB(1M), FILENAME VARCHAR(20))   $TAB_NAME">> $HOME/nsesample_partitioned.log
db2 "create table db2ext.htmltab (DOCID INTEGER NOT NULL PRIMARY KEY, CATEGORY VARCHAR(20), DATE DATE, HTMLFILE CLOB(1M), FILENAME VARCHAR(20))   $TAB_NAME" >> $HOME/nsesample_partitioned.log
 # ********************************************************************


 # ********************************************************************
 # Import data into sample table
 # ********************************************************************
echo "******************************************************** ">> $HOME/nsesample_partitioned.log
echo "Import data into the sample tables:                      ">> $HOME/nsesample_partitioned.log
echo "db2 import from texttab.del of del insert into DB2EXT.TEXTTAB (docid, author, title, comment, price)                                    ">> $HOME/nsesample_partitioned.log
db2 "import from texttab.del of del insert into DB2EXT.TEXTTAB (docid, author, title, comment, price)"                                         >> $HOME/nsesample_partitioned.log

echo "db2 load   from htmltab.del of del lobs from nsesamplehtml/ modified by lobsinfile insert into db2ext.htmltab ">> $HOME/nsesample_partitioned.log
db2 "load   from htmltab.del of del lobs from nsesamplehtml/ modified by lobsinfile insert into db2ext.htmltab"                               >> $HOME/nsesample_partitioned.log
 # *********************************************************************


 # *********************************************************************
 # Start the Update and Locking services
 # *********************************************************************
echo "********************************************************* ">> $HOME/nsesample_partitioned.log
echo "Start the Update and Locking services:                    ">> $HOME/nsesample_partitioned.log
echo "db2text start                                             ">> $HOME/nsesample_partitioned.log
db2text start                                                  >> $HOME/nsesample_partitioned.log
 # *********************************************************************


 # *********************************************************************
 # Set the environment variable DB2DBDFT
 # *********************************************************************
echo "**********************************************************">> $HOME/nsesample_partitioned.log
echo "export DB2DBDFT=$1                                           ">> $HOME/nsesample_partitioned.log
export DB2DBDFT=$1
 # *********************************************************************


 # *********************************************************************
 # Enable the database
 # *********************************************************************
echo "**********************************************************">> $HOME/nsesample_partitioned.log
echo "Enable the database $1:                                   ">> $HOME/nsesample_partitioned.log
echo "db2text enable database for text                          ">> $HOME/nsesample_partitioned.log
db2text enable database for text                               >> $HOME/nsesample_partitioned.log
 # *********************************************************************


 # *********************************************************************
 # Create index
 # *********************************************************************

echo "Create the text indexes ... " 
echo "**********************************************************">> $HOME/nsesample_partitioned.log
echo " Create and update indexes:                   ">> $HOME/nsesample_partitioned.log
echo db2text "create index DB2EXT.COMMENT for text on DB2EXT.TEXTTAB (COMMENT) ATTRIBUTES (CAST(PRICE AS DOUBLE) AS PRICE) $CACHE">> $HOME/nsesample_partitioned.log
db2text "create index DB2EXT.COMMENT for text on DB2EXT.TEXTTAB (COMMENT) ATTRIBUTES (CAST(PRICE AS DOUBLE) AS PRICE) $CACHE" >> $HOME/nsesample_partitioned.log

echo db2text update index DB2EXT.COMMENT for text              >> $HOME/nsesample_partitioned.log
db2text update index DB2EXT.COMMENT for text                   >> $HOME/nsesample_partitioned.log

if [[ -n $2 ]]
then
echo db2text activate cache for index DB2EXT.COMMENT for text            >> $HOME/nsesample_partitioned.log
db2text activate cache for index DB2EXT.COMMENT for text                   >> $HOME/nsesample_partitioned.log
fi

echo "db2text create index DB2EXT.HTMLIDX for text on DB2EXT.HTMLTAB(HTMLFILE) format HTML"                                                     >> $HOME/nsesample_partitioned.log
db2text "create index DB2EXT.HTMLIDX for text on DB2EXT.HTMLTAB(HTMLFILE) format HTML"                                                          >> $HOME/nsesample_partitioned.log
echo db2text update index DB2EXT.HTMLIDX for text              >> $HOME/nsesample_partitioned.log
db2text update index DB2EXT.HTMLIDX for text                   >> $HOME/nsesample_partitioned.log
 # *********************************************************************


 # *********************************************************************
 # Compile the thesaurus definition file using the db2extth utility
 # *********************************************************************
echo "**********************************************************">> $HOME/nsesample_partitioned.log
echo "Compile sample Thesaurus 'nsesamplethes'                  ">> $HOME/nsesample_partitioned.log

echo "cp nsesamplethes.def $HOME/sqllib/db2ext/thes                 ">> $HOME/nsesample_partitioned.log
cp -f nsesamplethes.def $HOME/sqllib/db2ext/thes/                      >> $HOME/nsesample_partitioned.log

echo "db2extth -f $HOME/sqllib/db2ext/thes/nsesamplethes.def -ccsid 1252 ">> $HOME/nsesample_partitioned.log
db2extth -f $HOME/sqllib/db2ext/thes/nsesamplethes.def -ccsid 1252    >> $HOME/nsesample_partitioned.log
 # *********************************************************************


echo "**********************************************************">> $HOME/nsesample_partitioned.log
echo "Starting search           *************************">> $HOME/nsesample_partitioned.log
echo "**********************************************************">> $HOME/nsesample_partitioned.log


rm -f /tmp/nsesearch_partitioned 2>/dev/null
cat >/tmp/nsesearch_partitioned <<HERE
    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;

HERE


if [[ -n $2 ]]
then
cat >>/tmp/nsesearch_partitioned <<HERE
    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,?,?);    
HERE
fi

db2 -tvf /tmp/nsesearch_partitioned >> $HOME/nsesample_partitioned.log

rm -f /tmp/nsesearch_partitioned 2>/dev/null


echo "**********************************************************">> $HOME/nsesample_partitioned.log
echo "connect reset                    *************************">> $HOME/nsesample_partitioned.log
echo "**********************************************************">> $HOME/nsesample_partitioned.log
echo db2 connect reset >> $HOME/nsesample_partitioned.log
db2 connect reset >> $HOME/nsesample_partitioned.log

 # *********************************************************************
 # Inform user
 # *********************************************************************
echo "**********************************************************">> $HOME/nsesample_partitioned.log
echo "Finished nsesample_partitioned.script ***                             ">> $HOME/nsesample_partitioned.log
echo "**********************************************************">> $HOME/nsesample_partitioned.log
echo "**********************************************************">> $HOME/nsesample_partitioned.log
echo "Check $HOME/nsesample_partitioned.log for execution information."

 #**********************************************************************
 #*** End of DB2 Net Search Extender sample 'nsesample_partitioned'
 #**********************************************************************
