Objective

This documentation has the objective to help you to migrate from Db2 NSE (Net Search Extender) to Text Search, via a Test Case I created.

It contains a Test Case step by step and several references to help you and avoid spending time looking for more documentation.

This won't cover capacity planning, for example. That is crucial in most production systems.

So, reference to this documentation to create your own test migration and migration script before apply to production.

Introduction

Db2 NSE (Net Search Extender) and Text Search are features that makes the database available for text searching.

Db2 NSE is deprecated since version 10.1 and it was discontinued in v11.5:

Deprecated functionality in Version 10.1 https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.1.0/com.ibm.db2.luw.wn.doc/doc/c0023496.html

Discontinued functionality in Version 11.5 https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.5.0/com.ibm.db2.luw.wn.doc/doc/c0023234.html

Text Search is a feature that is bundled with Db2 Server product.

You can use the Text Search integrated to the Db2 Server or you may want to have a Text Search Stand Alone server.

More information about the Text Search you find here:

Db2 Text Search https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.5.0/com.ibm.db2.luw.admin.ts.doc/doc/c0051296.html

Note 1 - Very Important

So, if you make use of NSE and are planning to upgrade to Db2 v11.5, you must migrate to Text Search

I will use Db2 11.1 for this Test Case.

Starting with Version 9.7 Fix Pack 3 and later fix packs, DB2 Text Search and Net Search Extender text indexes can coexist on the same table column, according to the documentation below:

https://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.ts.doc/doc/c0053088.html

I strongly recommend that you first migrate from NSE to Text Search before upgrading to Db2 v11.5.

In that way you will be able to let Text Search working while NSE is stopped. If any problem, you could stop Text Search and start NSE again, so you will not be impacted (if the application has syntax to work with both tools) while you fix the problem with Text Search.

Note 2

Basically, the migration consists in create the indexes in the Text Search and drop the indexes from NSE.

You won't actually migrate the indexes from NSE to the Text Search tool.

Note 3

If you still have questions after reading this documentation, please, check the links/documents listed in the end of this article (section "Documentation Reference"). The answer may be there.

Differences between NSE and Text Search

Next Search Extender is like a plugin that you installs in the Db2 Server.

Text Search is bundled with the Db2 product or may be installed in a separated sever if desired, as already mentioned.

In the documentation bellow you will find the functionally comparison between those:

Db2 Text Search and Net Search Extender comparison https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5.0/com.ibm.db2.luw.admin.ts.doc/doc/c0052757.html

Another point is that the prefixes to administrative commands are different on each tool as bellow:

db2ts - prefixes for the Text Search tool. Example: db2ts "start for text"
db2text - prefixes for the Net Search Extender tool. Example: db2text start

Also, the queries may have different operators, so you may need to change your application. For example:

Net Search

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

Text Search

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

Test Case

1) Creating a Sample Database for text searching

There is a script bundled with Db2 that creates a database with data to be used with NSE.

So I created the NSE environment running that script:

db2inst2@linux-cpo9:~/sqllib/samples/extenders/db2ext> ./nsesample NSEdb
Setup the database NSEdb ...
Create the text indexes ...
Check /home/db2inst2/nsesample.log for execution information.

Basically, that script makes the following (it does more, but those are the core):

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))" >> $HOME/nsesample.log

db2 "create table db2ext.htmltab (DOCID INTEGER NOT NULL PRIMARY KEY, CATEGORY VARCHAR(20), DATE DATE, HTMLFILE CLOB(1M), FILENAME VARCHAR(20))" >> $HOME/nsesample.log

db2text start

export DB2DBDFT=<db_name>

db2text enable database for text

db2text "create index DB2EXT.COMMENT for text on DB2EXT.TEXTTAB (COMMENT) ATTRIBUTES (CAST(PRICE AS DOUBLE) AS PRICE) CACHE TABLE(docid,title) TEMPORARY MAXIMUM CACHE SIZE 1 " >> $HOME/nsesample.log

db2text "create index DB2EXT.HTMLIDX for text on DB2EXT.HTMLTAB(HTMLFILE) format HTML"

db2text update index DB2EXT.COMMENT for text

db2text update index DB2EXT.HTMLIDX for text

2) Testing the NSE

I am doing a query that uses the NSE text search ability.

Note I am using double quotes, once it is the correct syntax (operator) for NSE:

db2 => SELECT title FROM db2ext.texttab WHERE CONTAINS(comment, '"mystery"') = 1
TITLE
--------------------------------------------------
The Testament
1 record(s) selected.

3) Migrating from NSE to Text Search:

3.1) Configuring the Text Search

Use the configTool to configure the communication port fot the Text Search, log and temp directories:

db2inst2@linux-cpo9:~/sqllib/db2tss/bin> ./configTool configureParams -logPath /home/db2inst2/sqllib/db2dump/ -tempDirPath /home/db2inst2/sqllib/db2dump/
The request was successfully executed.

db2inst2@linux-cpo9:~/sqllib/db2tss/bin> configTool configureHTTPListener -configPath /home/db2inst2/sqllib/db2tss/config adminHTTPPort 50005
The request was successfully executed.

3.2) Checking the Text Search configuration:

Use the printAll option from configTool to check it:

db2inst2@linux-cpo9:/opt/ibm/db2/V11.1/db2tss/bin> ./configTool printAll -configPath /home/db2inst2/sqllib/db2tss/config

Build version = 5.2.1.0
Build number = 4694
Build timestamp = 2014/09/04 21:24:52.199
Jar manifest version = 5.2.1.0, 4694, 2014/09/04 21:24:52.199
(...)// I truncated the output because it is too long.

3.3) Export the DB2DBDFT

You need to export the DB2DBDFT to let the NSE and Text Search know what the database you are working with. In my case, the database name é "nsedb"

export DB2DBDFT=nsedb

3.4) Checking if NSE and Text Search are started:

db2inst2@linux-cpo9:~> db2text start
CTE0185 The update and locking services are already active.

db2inst2@linux-cpo9:~> 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

3.5) Enabling database for Text Search tool:

db2inst2@linux-cpo9:~> db2ts ENABLE DATABASE FOR TEXT
CIE00001 Operation completed successfully.

3.6) Checking the NSE index status:

Per the query output, the index is Active (Active Flag =1 )

db2inst2@linux-cpo9:~/sqllib/samples/extenders/db2ext> db2 select INDEXIDENTIFIER,ACTIVEFLAG from DB2EXT.TTEXTCOLUMNS
INDEXIDENTIFIER      ACTIVEFLAG
------------------   -----------
IX393318              1
IX403318              1
2 record(s) selected.

We can see IX393318 is the index Comment. And IX403318 is the index HTMLIDX.

db2inst2@linux-cpo9:~/sqllib/samples/extenders/db2ext> db2 "select EVENTVIEWNAME,INDNAME from db2ext.TEXTINDEXES"
EVENTVIEWNAME          INDNAME
--------------------- ---------
EVENTIX393318          COMMENT
EVENTIX403318          HTMLIDX
2 record(s) selected.

3.6.1) Determining the NSE indexes that you have:

There is no tool that will automatically export the DDL (Data Definition Language) of the create indexes from NSE.

Nether db2look will do that.

In other words, to know the NSE indexes you have, you need to query the NSE catalog tables or views.

Those tables and views are:

table db2ext.TEXTCOLUMNS
table db2ext.TEXTINDEXES
table db2ext.TMODELS (its views: db2ext.MODELS, db2ext.TEXTINDEXFORMATS)
table db2ext.TCONFIGURATION (its view: db2ext.INDEXCONFIGURATION)

This is an example of query (please check the tables/views above. Do not be limited to this query example):

db2inst2@linux-cpo9:/> db2 select "substr(INDEXSCHEMA,1,7) as INDEXSCHEMA ,substr(INDEXNAME,1,8) as INDEXNAME,substr(TABLESCHEMA,1,8) AS TABBLESCHAME,substr(TABLENAME,1,10) as TABLENAME,substr(COLUMN,1,8) as COLUMN,substr(FORMAT,1,7) as FORMAT from db2ext.TEXTCOLUMNS"

INDEXSCHEMA INDEXNAME TABBLESCHAME TABLENAME  COLUMN   FORMAT
----------- --------- ------------ ---------- -------- -------
DB2EXT      COMMENT   DB2EXT       TEXTTAB    COMMENT  TEXT
DB2EXT      HTMLIDX   DB2EXT       HTMLTAB    HTMLFILE HTML

2 record(s) selected.

So, query the NSE catalog tables to determine the NSE indexes you have.

It would be nice if you had those DDLs saved anywhere.

3.7) Create a Text Search index for the same column that already has a NSE index

Based in your queries outputs from section 3.6.1 above, create the DDL ("create index") for the Text Search.

The "create index" statement would be:

db2ts "CREATE INDEX index-name FOR TEXT ON table-name (column-name)"

Please, refer the following documentation to check permissions and any restrictions:

Creating a text search index
_https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5.0/com.ibm.db2.luw.admin.ts.doc/doc/t_creatingafulltextindex.htm_l

Note:

"If there exists an NSE index on the same column then the Text Search index by default will be created in inactive state. After migration, for enabling the searches using the Text Search index instead of NSE index then we need to use ALTER INDEX and set index as active. Refer the below steps for further details."

So, in my environment, I did:

db2inst2@linux-cpo9:~/sqllib/samples/extenders/db2ext> db2ts "create index DB2ts.COMMENT for text on DB2EXT.TEXTTAB(COMMENT)"
CIE00001 Operation completed successfully.

3.8) Populating the new Text Search index:

To populate the index, it is necessary to run the "update index" command:

db2inst2@linux-cpo9:~/sqllib/samples/extenders/db2ext> db2ts update index DB2ts.COMMENT for text
CIE00001 Operation completed successfully.

3.9) Checking the Text Search index status:

As we can see, the new index is Inactive.

db2inst2@linux-cpo9:~/sqllib/samples/extenders/db2ext> db2 "select indschema, indname, indstatus from SYSIBMTS.TSINDEXES"
INDSCHEMA INDNAME    INDSTATUS
--------- ---------- ----------
DB2TS     COMMENT    INACTIVE
1 record(s) selected.

At this moment I have an Active NSE index and an Inactive Text Search index for the same column of the table.

3.10) Activating the Text Search index:

Activating the Text Search index will make the NSE index be inactive for that column.

Activating the Text Search index:

db2inst2@linux-cpo9:~/sqllib/samples/extenders/db2ext> db2ts "ALTER INDEX DB2ts.COMMENT FOR TEXT SET ACTIVE"
CIE00001 Operation completed successfully.

Confirming that the NSE index got inactive (Active Flag = 0 for indexID IX393318):

db2inst2@linux-cpo9:~/sqllib/samples/extenders/db2ext> db2 select INDEXIDENTIFIER,ACTIVEFLAG from DB2EXT.TTEXTCOLUMNS
INDEXIDENTIFIER ACTIVEFLAG
----------- -----------
IX393318    0
IX403318    1
2 record(s) selected.

Confirming that the Text Search index got Activated:

db2inst2@linux-cpo9:~/sqllib/samples/extenders/db2ext> db2 "select indschema, indname, indstatus from SYSIBMTS.TSINDEXES"
INDSCHEMA INDNAME INDSTATUS
--------- -------- ----------
DB2TS     COMMENT  ACTIVE
1 record(s) selected.

3.11) Testing the query that will make use of the Text Search index:

db2inst2@linux-cpo9:~/sqllib/samples/extenders/db2ext> db2 "SELECT title FROM db2ext.texttab WHERE CONTAINS(comment,'mystery') = 1"
TITLE
----------------------
The Testament
1 record(s) selected.

Nice. It worked.

4) Change your Application

Your application may be using the syntax to query the database that works only with Db2 NSE.

So you need to review the queries of the application and change it to use the syntax for Text Search.

Please, refer the documentation below in the "Application Migration" section, to help you to change those syntaxes.

Migrate DB2 Net Search Extender indexes to DB2 TextSearch
https://www.ibm.com/developerworks/data/library/techarticle/dm-1203textsearchmigration/dm-1203textsearchmigration-pdf.pdf

This is from the documentation above:

" The following examples compare various types of NSE queries and the corresponding Text Search queries:

• Text search uses || and && operators as opposed to | and & in NSE. NSE: SELECT AUTHOR, TITLE FROM DB2EXT.TEXTTAB WHERE CONTAINS(COMMENT, '"author" |"pulitzer" & "book"') = 1 Text Search: SELECT AUTHOR, TITLE FROM DB2EXT.TEXTTAB WHERE CONTAINS(COMMENT,'author || pulitzer && book') = 1

• The query below will search for all words that start with “thr” and end with “er” with any number of characters between them. NSE: SELECT AUTHOR,TITLE FROM DB2EXT.TEXTTAB WHERE CONTAINS(COMMENT, '"thr%er"') = 1 Text Search: SELECT AUTHOR,TITLE FROM DB2EXT.TEXTTAB WHERE CONTAINS(COMMENT,'thr*er') = 1

• The query below will return any word that starts with “th” and ends with “iller” with a single character between them. NSE: SELECT AUTHOR, TITLE FROM DB2EXT.TEXTTAB WHERE CONTAINS(COMMENT,'"th_iller"') = 1 Text Search: SELECT AUTHOR, TITLE FROM DB2EXT.TEXTTAB WHERE CONTAINS(COMMENT, 'th?iller') = 1

• NSE has fuzzy search feature which can be achieved in Text Search by using the ' ~ ' operator. To specify the degree explicitly ' ~ ' followed by the number can be used. NSE: SELECT AUTHOR, TITLE FROM DB2EXT.TEXTTAB WHERE CONTAINS(COMMENT, 'fuzzy form of 80 "pullitzer"') =1 Text Search: SELECT AUTHOR, TITLE FROM DB2EXT.TEXTTAB WHERE CONTAINS(COMMENT,'pullitzer~0.8') =1

• NSE searches need to specify stemmed form explicitly whereas the default for Text Search is stemmed (search term in single quotes). Double quotes can be used in Text Search for phrase search. NSE: SELECT AUTHOR, TITLE FROM DB2EXT.TEXTTAB WHERE CONTAINS(COMMENT, 'stemmed form of "shock"') =1 Text Search: SELECT AUTHOR, TITLE FROM DB2EXT.TEXTTAB WHERE CONTAINS(COMMENT,'shock') =1

• NSE specifies the escape character with the 'ESCAPE' keyword, where as in text Search '' is the escape character. NSE: SELECT AUTHOR, TITLE FROM DB2EXT.TEXTTAB WHERE CONTAINS(COMMENT, '"100!%"ESCAPE "!"') = 1 Text Search: SELECT AUTHOR, TITLE FROM DB2EXT.TEXTTAB WHERE CONTAINS(COMMENT, '100%') = 1

• In Text Search,the SYNONYM keyword can be set to use thesaurus/synonym dictionary associated with the text search index. NSE: SELECT CATEGORY, DATE FROM DB2EXT.HTMLTAB WHERE CONTAINS(HTMLFILE, 'THESAURUS "nsesamplethes" EXPAND SYNONYM TERM OF "product"') = 1 Text Search: SELECT CATEGORY, DATE FROM DB2EXT.HTMLTAB WHERE CONTAINS(HTMLFILE,'product', 'SYNONYM=ON') = 1 "

5) Dropping the NSE index and stopping the NSE service:

In production, I recommend to drop the NSE indexes just after several days using the Text Search and making sure if is fully working.

If you are comfortable that Text Search is working and NSE will not be needed any more, go ahead and drop its indexes and stop it like this:

db2inst2@linux-cpo9:~/sqllib/samples/extenders/db2ext> db2text "drop index DB2EXT.COMMENT for text"
CTE0001 Operation completed successfully.

db2inst2@linux-cpo9:~/sqllib/samples/extenders/db2ext> db2text "drop index DB2EXT.HTMLIDX for text"
CTE0001 Operation completed successfully.

db2inst2@linux-cpo9:~/sqllib/samples/extenders/db2ext> db2text "disable database for text"
CTE0001 Operation completed successfully.

db2inst2@linux-cpo9:~/sqllib/samples/extenders/db2ext> db2text stop
CTE0261 There is at least one cache activated for a text index in this instance. Deactivate the cache for any activated index using the DEACTIVATE CACHE command, or use the FORCE option to stop.

db2inst2@linux-cpo9:~/sqllib/samples/extenders/db2ext> db2text stop force
CTE0001 Operation completed successfully.

6) Error found during the tests:

While trying to start the NSE, I received the error CTE0249

db2inst2@linux-cpo9: db2text start
sh: cteprisc: not found
sh: cteprisc: not found>
sh: cteprisc: not found
CTE0249 Executable program "cteprisc" terminated abnormally.

To fix it I run the db2iupdt for the instance.