Steps of creating and troubleshooting db2 text serach
1. Install db2 with test search. (normal db2 does not have text search)
https://www.ibm.com/support/knowledgecenter/SSEPGG_10.1.0/com.ibm.db2.luw.admin.ts.doc/doc/c_installation.html
2.
https://www.ibm.com/support/knowledgecenter/en/SSFUEU_7.0.0/com.ibm.swg.ba.cognos.administrators_guide.7.0.0.doc/t_adm_db2_installandconfigure_textsearch.html
C:\Program Files\IBM\SQLLIB_01\cfg\db2tss\config>configTool generateToken -configPath "C:\Program Files\IBM\SQLLIB_01\cfg\db2tss\config" -seed DB2_01
The request was successfully executed.
configTool generateToken -configPath "/home/kanmanda/sqllib/db2tss/bin/config" -seed kanmanda
3.
C:\Program Files\IBM\SQLLIB_01\cfg\db2tss\config>adminTool status -configPath "C:\Program Files\IBM\SQLLIB_01\cfg\db2tss\config"
IQQD0055E The search server is stopped. It must be started for the tool to run.
4.
db2ts START FOR TEXT
db2ts STOP FOR TEXT
5.
C:\Program Files\IBM\SQLLIB_01\cfg\db2tss\config>db2 create db m1
6.
set DB2DBDFT=m1
7.
db2 connect to m1
8.
C:\Program Files\IBM\SQLLIB_01\cfg\db2tss\config>db2ts "ENABLE DATABASE FOR TEXT"
SQL20427N An error occurred during a text search administration procedure or command. The error message is "CIE0320E Table space "SYSTOOLSPACE" does not exist. ". SQLSTATE=38H14
9.
C:\Program Files\IBM\SQLLIB_01\cfg\db2tss\config>db2 create tablespace SYSTOOLSPACE
DB20000I The SQL command completed successfully.
10.
C:\Program Files\IBM\SQLLIB_01\cfg\db2tss\config>db2ts "ENABLE DATABASE FOR TEXT"
CIE00001 Operation completed successfully.
11.Creating table and indexes.
CREATE TABLE testing (
"JS_KEY" DECFLOAT(16) NOT NULL ,
"JS_SEARCHCLOB" VARCHAR(4000 OCTETS) ,
"JS_JSONDATA" VARCHAR(4000 OCTETS) ,
"JS_LASTUPDT" TIMESTAMP(0) WITH DEFAULT "SYSIBM"."TIMESTAMP"(CURRENT TIMESTAMP) ,
"USM_O_C_CREATED_DTE" TIMESTAMP(7) ,
"USM_O_CIF_NAME" VARCHAR(100 OCTETS) )
IN "USERSPACE1"
ORGANIZE BY ROW;
ALTER TABLE testing
ADD PRIMARY KEY
("JS_KEY");
db2ts test_FS_IDX FOR TEXT ON testing(JS_SEARCHCLOB)"
12. Import data to table
db2 "import from a.del of del modified by coldel| insert into testing (JS_KEY, JS_SEARCHCLOB)"JS_SEARCHCLOB,JS_JSONDATA,JS_LASTUPDT,USM_O_C_CREATED_DTE,USM_O_CIF_NAME)"
13 .Once data imported update the index
db2ts "update index testing_TEST_FS_IDX for text"
14.
db2 "select count(*) from JOMC.JUSEARCH_USEARCHAPPLICATION where (contains(JS_SEARCHCLOB, '(TOUCH ''N GO)') = 1) fetch first 2 rows only"
db2 "select * from JOMC.JUSEARCH_USEARCHAPPLICATION where (contains(JS_SEARCHCLOB, '0000406400X05011802') = 1) fetch first 5 rows only"
15. Troubleshooting
[db2inst1@deale1 bin]$ db2ts START FOR TEXT
SQL20427N An error occurred during a text search administration procedure or command. The error message is "CIE00340 Cannot start executable program "/bin/sh". ". SQLSTATE=38H14
Check the config file and fix .Mainly with permission and instance port error happens
change in config.xml
<config>
<query/>
<server>
<port>55000</port>
<installPath>/home/kanmanda/sqllib/db2tss/</installPath>
<configPath>/home/kanmanda/sqllib/db2tss/config</configPath>
<stellentEnabled>false</stellentEnabled>
</server>
<index>
<enableXMLNameSpaceSearch>false</enableXMLNameSpaceSearch>
<enableXMLDateSearch>false</enableXMLDateSearch>
</index>
</config
Also other common error messages
2.
[db2inst1@deale1 config]$ db2ts START FOR TEXT
CIE00001 Operation completed successfully.
[db2inst1@deale1 ~]$ db2ts "START FOR TEXT"
SQL20427N An error occurred during a text search administration procedure or command. The error message is "CIE00204 Instance services already started. ". SQLSTATE=38H14
[db2inst1@deale1 ~]$ db2ts "ENABLE DATABASE FOR TEXT"
SQL20427N An error occurred during a text search administration procedure or command. The error message is "CIE00322 Specified or default database already enabled for text. ". SQLSTATE=38H14
3. If you try to drop table before dropping index following error happens
db2 drop table testing
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL20536N The operation cannot be processed because it involves a text index.
Reason code = "1". SQLSTATE=429C9
[db2inst1@deale1 config]$ db2ts "DROP INDEX testing_FS_IDX FOR TEXT"
CIE00001 Operation completed successfully.
[db2inst1@deale1 config]$ db2 drop table testing
DB20000I The SQL command completed successfully.
4. In case following error
[db2instb@amulet1 ~]$ db2ts "START FOR TEXT"
SQL20427N An error occurred during a text search administration procedure or command. The error message is "CIE0774E The DB2 Text Search configuration could not be found. More diagnostic information is available in the db2diag log files. ". SQLSTATE=38H14
Do the following
<installPath>/home/db2instb/sqllib/db2tss/</installPath>
<configPath>/home/db2instb/sqllib/db2tss/config</configPath>
home/db2inst1/sqllib/db2tss/bin/configTool generateToken -configPath "/home/db2inst1/sqllib/db2tss/config" -seed db2inst1
The request was successfully executed.
configTool generateToken -configPath "/home/db2instb/sqllib/db2tss/config" -seed db2instb
configTool generateToken -configPath "/home/kanmanda/sqllib/db2tss/bin” -seed db2instb
[db2instb@amulet1 bin]$ configTool generateToken -configPath "/home/db2instb/sqllib/db2tss/config" -seed db2instb
The request was successfully executed.
[db2instb@amulet1 bin]$
[db2instb@amulet1 bin]$ db2ts "ENABLE DATABASE FOR TEXT"
CIE00001 Operation completed successfully.
[db2instb@amulet1 bin]$
5. You can enable only single db per instance ,in case you want to enable other db in same instance -you will get
IQQG0335E The server cannot start. See the error log file for more details.
Exception: IQQD0034E The port 55000 that was specified is already in use.
Causes of the problem:
IQQG0020E java.net.BindException: Address already in use
6. The below error -follow the technote
SQL20427N An error occurred during a text search administration procedure or command. The error message is ""IQQD0040E The client specified the wrong authentication token." ". SQLSTATE=38H14
https://www-01.ibm.com/support/docview.wss?uid=swg21600879
Comments
Post a Comment