IBM Support

[Db2] Simple test case shell script for Db2 Text Search

Question & Answer


Question

Is there any simple test case shell script for Db2 Text Search?

Answer

This document contains frequently asked questions (FAQ), especially simple test case shell script about the product DB2 for Linux UNIX and Windows.
Simple shell script or scenario cannot clarify whether the current environment work fine or not only but also show us a basic usage and how Db2 works for Db2 beginner and Db2 average users.  If users could provide a problem reproducible shell script, it helps Db2 technical support analysts to identify the root cause.  Users can clarify whether newer Db2 Version, Fixpack or Operating System patch, and so on, could have the same problem or not by themselves.  Users can create their own shell script base tool or application referencing the described scripts as a sample or an example.  Scripts were verified on Linux platform so need to modify as needed for other platforms.
Here is key note for this article:
- As documented in V11.5 manual, Net Search Extender (NSE) is no longer supported.
- Verified with Db2 V11.5.0.0 and V10.5 FP10 on Red Hat Enterprise Linux 7.8.
- Db2 V11.5.0.0 is installed at "/opt/ibm/db2/V11.5" path.
- Db2 instance name is db2inst1 and its home directory is /home/db2inst1
- To run the shell script, it is needed to install Db2 Text Search feature files under Db2 product directory and configure the Db2 instance for handling Db2 Text Search feature.
0.  If you installed Db2 and created an instance, go next step.  If your environment does not, here is an example for how to install Db2 by using a response file method on Linux or AIX.  Other than a response file method, refer manual.  Here is the system requirement and prerequisites.   Here is one of Japanese good technotes for this matter.  You need to install Db2 server first and then install Text Search component second, two installations on UNIX and Linux.  But you can install Db2 server and Text Search component at the same time timing on Windows.
 
    1) Decompress a product file under "/prod/V115" and save a license file at "/prod/V115" by root user.  Then, cd to the "server" directory and save 5 lines as db2.rsp where db2setup file is located directory by root user on Linux:
PROD          = DB2_SERVER_EDITION
FILE          = /opt/ibm/db2/V115
LIC_AGREEMENT = ACCEPT
INSTALL_TYPE  = TYPICAL
LANG          = JP
         * For AIX, replace "/opt/ibm/db2/V115" with "/opt/IBM/db2/V115" (capital letter IBM).
 
    2) Run "./db2setup -r db2.rsp" as root user to install Db2.
    3) On Linux, run as root for creating the instance and apply a license:
 
useradd db2inst1
useradd db2fenc1
/opt/ibm/db2/V115/instance/db2icrt -u db2fenc1 db2inst1
/opt/ibm/db2/V115/adm/db2licm -a /prod/V115/db2xxxx.lic
       * On AIX, run as root for creating the instance and apply a license:
  
useradd -m -d /home/db2inst1 db2inst1 
useradd -m -d /home/db2fenc1 db2fenc1 
/opt/IBM/db2/V115/instance/db2icrt -u db2fenc1 db2inst1
/opt/IBM/db2/V115/adm/db2licm -a /prod/V115/db2xxxx.lic
1.  First, need to check whether Db2 product is installed with the DB2 Text Search or not.
    It is suggested to contact your DBA to see whether it is already installed or not.  If not, need to review manual to install DB2 Text Search feature first of all:
    It can be confirmed whether installed or not by running command on UNIX and Linux: 
db2ls -b DB2_INSTALLED_DIRECTORY -q -a | grep TEXT
   
    Here is an example output when it is already installed on Db2 V11.5. 
TEXT_SEARCH                        11.5.0.0          0   DB2 Text Search
    If it does not return any row, Text Search is not installed at Db2 product directory.
    If so, see manual and follow steps to install it.
    Here is an example for using a response file case for your reference.
    Save text as db2text.rsp at product decompressed directory where
    db2setup file is located:
PROD          = DB2_SERVER_EDITION
FILE          = /opt/ibm/db2/V11.5
LIC_AGREEMENT = ACCEPT
INSTALL_TYPE  = CUSTOM
COMP          = TEXT_SEARCH
    Then, run "./db2setup -r db2text.rsp" by root user.
2.  Second, need to check whether the instance is configured to run DB2 Text Search or not.
    Run configTool as instance owner to see whether the instance is configured or not.
    Here is an example. 
configTool printAll -configPath /home/db2inst1/sqllib/db2tss/config

    Specify the instance home directory "home/db2inst1/" and "db2tss/config".

    If the output's installPath is empty, it is not configured yet.
    If not yet, need to run as root user to configure the instance as integrated Db2
    Text Search server: 
DB2_INSTALLED_DIRECTORY/instance/db2iupdt -j "TEXT_SEARCH" db2inst1
    Here is an example:
/opt/ibm/db2/V11.5/instance/db2iupdt -j "TEXT_SEARCH" db2inst1
    If the output's installPath has an existing path, it is ready to go.
3.  Log on as instance owner and make sure Db2 is up and running.
    Save as repro.sh and run "chmod 777 repro.sh".  Then, execute it:
  
#!/bin/sh

export LANG=en_US.utf8

db2 -v "drop db db1"
db2 -v "create db db1 using codeset utf-8 territory us"
db2 -v "connect to db1"
db2 -v "call sysproc.sysinstallobjects ( 'EXPLAIN' ,'C' , null, null )"
db2ts "start for text"
db2ts "enable database for text connect to db1"
db2 -v "create table t1 (id varchar(20) not null, doc varchar(50), primary key (id))"    
export DB2DBDFT=DB1

db2 -v "insert into t1 (id, doc) values ('1', 'I love cat and dog.')"
db2 -v "insert into t1 (id, doc) values ('2', 'He likes the cats and dogs.')"
db2 -v "insert into t1 (id, doc) values ('3', 'She needs cats only.')"
db2 -v "insert into t1 (id, doc) values ('4', 'Boy look at the cat and fish.')"
db2ts "create index idx_text for text on t1 (doc) format text connect to db1"
db2ts "update index idx_text for text connect to db1"

db2 -v "select id from t1 where contains (doc, '"cat"') = 1"
db2 -v "select id from t1 where contains (doc, '"cat~0.1"') = 1"
db2 -v "select id from t1 where contains (doc, '"cat~"0.8') = 1"
    1st select returns 1,2,3,4.  2nd one returns 1,2,3,4.  3rd one returns 1 and4.
4.  To clean up, logon as instance owner. Then, run:
db2ts "stop for text"
db2 -v "terminate"
db2 -v "drop db db1"
5.  For a multi-byte language, here is a Japanese shell script test case.
    Log on as instance owner and make sure Db2 is up and running.
    Save as ja_repro.sh and run "chmod 777 ja_repro.sh".  Then, execute:
  
#!/bin/sh

export LANG=ja_JP.utf8

db2 -v "drop db db1"
db2 -v "create db db1 using codeset utf-8 territory ja"
db2 -v "connect to db1"
db2 -v "call sysproc.sysinstallobjects ( 'EXPLAIN' ,'C' , null, null )"
db2ts "start for text"
db2ts "enable database for text connect to db1"
db2 -v "create table t1 (id varchar(20) not null, doc varchar(50), primary key (id))"    
export DB2DBDFT=DB1

db2 -v "insert into t1 (id, doc) values ('1', 'おめでとう')"
db2 -v "insert into t1 (id, doc) values ('2', 'おめでとうございます')"
db2 -v "insert into t1 (id, doc) values ('3', 'おめでた')"
db2 -v "insert into t1 (id, doc) values ('4', 'おめでとさん')"
db2 -v "insert into t1 (id, doc) values ('5', 'あけおめ')"
db2ts "create index idx_text for text on t1 (doc) format text connect to db1"
db2ts "update index idx_text for text connect to db1"

db2 -v "select id from t1 where contains (doc, '"おめ"') = 1"
db2 -v "select id from t1 where contains (doc, '"おめで"') = 1"
db2 -v "select id from t1 where contains (doc, '"おめでと"') = 1"
db2 -v "select id from t1 where contains (doc, '"おめでとう"') = 1"
    1st select statements returns 1,2,3,4,5. 2nd one returns 1,2,3,4.  3rd one returns 1,2,4.
    4th one returns1 and 2.
6.  To clean up, logon as instance owner.  Then, run:
db2ts "stop for text"
db2 -v "terminate"
db2 -v "drop db db1"
7. To uninstall Text Search, execute "db2stop" by instance owner.  And, save one line file as db2un.rsp at the same directory where db2setup files is located by root user:
REMOVE_COMP   = TEXT_SEARCH
   Then, run, "./db2_deinstall -r db2un.rsp" by root user, and type the base installation directory.
 

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"ARM Category":[],"Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"},{"code":"PF033","label":"Windows"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
30 September 2021

UID

ibm16187407

Page Feedback