Verify DB2 Net Search Extender indexes using the NSE Index Verification tool

How to ensure that NSE text indexes are valid

Net Search Extender, here after called NSE, is an IBM® DB2® extender providing full text search support in DB2. NSE searches documents stored in columns of database tables by maintaining its internal text indexes. Rather than searching sequentially through text documents stored in database columns at query time, NSE uses its text indexes to search in an efficient manner. Verification of NSE indexes are vital because the errors reported by the top layer applications on full text index can be misleading. This unnecessarily delays the problem resolution at the production system. To prevent such a situation, the NSE Index Verification tool was developed to verify the NSE index validity and consistency, and to check whether or not indexes are valid.

Share:

Lalatendu Patro (lalatendu@in.ibm.com), Software Developer, IBM

Author photoLalatendu Patro started his work with IBM as a Development Lead for Net Search Extender in DB2. He has worked on DB2 Install URE along with NSE development activities. Prior to IBM, he has worked in embedded systems for messaging protocols such as SMS, Email, ActiveSync and server technologies for gateways and server applications. Currently he is working as the Development Lead for DB2 TextSearch project.



25 April 2013 (First published 16 August 2012)

Also available in Chinese

Overview

NSE offers full text search capability to DB2 and other databases by helping with their internal indexes. These text indexes get updated automatically or manually (as configured) with an update in the database columns for which these indexes are created.

NSE indexes normally consist of mapping indexes and internal text indexes. NSE keeps both of these data structures in sync for every index update operation. Hence, for normal operational situations, the following conditions should be maintained:

  • NSE mapping indexes are proper.
  • Internal text indexes are proper.
  • Mapping indexes and internal text indexes are in sync.

When top layer applications find errors in NSE functionality, you should verify the consistency of NSE indexes to confirm the errors. The NSE Index Verification tool was developed to verify the validity of the NSE index. This tool verifies the previously mentioned three conditions and tells you whether the NSE internal data structure is proper or not.

This tool is available in both Windows® and UNIX®-based platforms. In the Windows platform, it is checknseindex.exe, and in UNIX-based platforms it is checknseindex. This tool can be found in the directory /<DB2 instance directory>/bin/ in the supported releases or fix packs. This tool is available in the following DB2 for Linux, UNIX, and Windows releases:

  • DB2 Version 9.7 FP6 and later (Version 9.7 FP 8 onwards supports database clauses)
  • DB2 Version 10.1 FP1 and later (Version 10.1 FP2 onwards supports database clauses)
  • DB2 Version 10.5

NSE Index Verification tool usage options

This tool supports three options for verifying an NSE index. These options simplify the use of the tool, so that you can run it with information that you have available. Basically this tool requires "Index Path" and "Index Id" to verify the NSE index. However in many situations you may find it difficult to get the IndexPath or IndexId. Hence the tool supports options where you can provide the database details, and the tool can get the required information from the database and verify the NSE index.

The tool options are classified based on available information with the user, with a choice to run the tool either using "index directory and index ID" or with database information such as table name-column name, index schema-index name. The tool supports the following three kinds of clauses:

  • Index-path clause (without database options)
  • Index-database clause (with database options)
  • Table-column-database clause (with database options)
Listing 1. Tool options
>>-checknseindex--+----------------------------------+---------->
+-| index-path-clause |------------+
+-| index-database-clause |--------+
'-| table-column-database-clause |-'
>--+-----+--+-------------+--+-----+---------------------------><
'- -v-'  '- -deepCheck-'  '- -h-'

index-path-clause

|-- -p--absolute_index_path-- -i--index_id----------------------|

index-database-clause

|-- -indschema--index_schema_name-- -indname--index_name-------->

>-- -dbname--database_name--------------------------------------|

table-column-database-clause

|-- -tabschema--table_schema_name-- -tabname--table_name-------->

>-- -colname--column_name-- -dbname--database_name--------------|

The following parameters are supported:

  • -i <index_id>: Identifies the NSE index to validate
  • -p <absolute_index_path>: Path that contains the NSE index files
  • -indschema<index_schema_name>: Schema of the NSE index
  • -indname<index_name>: Index name of NSE index
  • -tabschema<table_schema_name> :Schema of the table on which NSE index is created
  • -tabname<table_name>: Table name on which NSE index is created
  • -colname<column_name>: Column name of the table on which NSE index is created
  • -dbname<database_name>: Database name for which NSE index belongs to
  • -deepCheck: Specifies that additional internal structures should be validated
  • -v: Specifies that verbose output should be printed
  • -h: Causes to print the help message

Tool clauses in detail

  • index-path-clause:

    checknseindex.exe -p <index directory> -i <index ID>

    Mandatory parameters for this clause are -i and -p , whereas the -v and -deepCheck options are optional parameters.

  • index-database-clause:

    checknseindex.exe -indschema <index schema> -indname <index name> -dbname <databasename> -v deepCheck

    Mandatory parameters for this clause are index schema name, index name and database name.

  • table-column-database-clause:

    checknseindex.exe -tabschema <table schema> -tabname <table name> -colname <column name> -dbname <database name>

    Mandatory parameters for this clause are Table schema, table name, column name and database name.

To verify that NSE indexes are present in directory "C:\DOCUME~1\ALLUSE~1\APPLIC~1\IBM\DB2\db2build\LITU\db2ext\indexes\NODE0000\" for index ID IX373308, the tool can be run as follows:

checknseindex.exe -p E:\IBM\DB2\db2build\LITU\db2ext\indexes\NODE0000 -i IX373308

To use the tool with optional parameters, run it as follows:

checknseindex.exe -p E:\IBM\DB2\db2build\LITU\db2ext\indexes\NODE0000 -i IX373308 -v -deepCheck


An example of using the tool

Listing 2 shows the NSE Index Verification tool output when indexes are invalid.

Listing 2. Example usage of the tool
checknseindex.exe -p C:\NODE0000 -i lx045010-v -deepCheck
=============================================================
  checknseindex
  ---------
  Net Search Extender index validation utility
=============================================================
 Validating NSE mapping indexes..\.

    Forward mapping data control record : Number of blocks = "3"
    Reverse mapping data control record : Number of blocks = "3"
    Forward mapping index control record : Number of documents =
  "589872187"
    Reverse mapping index control record : Number of documents = 
  "4332"
   CTE5257E  The NSE index validation utility found the NSE mapping index
   to be invalid.  Reason code: "3". Diagnostic data: "".

How to find the index ID and index directory

Index ID and index directory can be found in the database by using the following query:

db2 SELECT INDEXSCHEMA,INDEXNAME,INDEXIDENTIFIER, INDEXDIRECTORY from DB2EXT.TTEXTINDEXES

Listing 3 shows sample output of the previous query (in UNIX platform).

Listing 3. Example usage of the tool
INDEXSCHEMA  INDEXNAME   INDEXIDENTIFIER  INDEXDIRECTORY
-----------  ---------   ---------------  ------------------------------------
DB2INST1     INDEXVIEW   IX403809         /home/db2inst1/sqllib/db2ext/indexes

MYSCHEMA     MYTITLEIDX  IX450211         /home/db2inst1/sqllib/db2ext/indexes

DB2INST1     COMMENT     IX050811         /home/db2inst1/sqllib/db2ext/indexes

  3 records(s) selected.

In the previous case, the index ID and index directory with INDEXSCHEMA (MYSCHEMA) and INDEXNAME (MYTITLEIDX) is as follows.

  • Index ID IX450211
  • Index directory is /home/db2inst1/sqllib/db2ext/indexes

Therefore, for the previous example, the command to check the index consistency is as follows:

./checknseindex -p /home/db2inst1/sqllib/db2ext/indexes/NODE0000 -i IX450211


Considerations for running the index verification tool

The user should have access privileges to the index directory. (This tool needs read access to NSE internal indexes to analyze consistency of internal data structures.)

For very large text indexes, the tool may require more time to analyze the internal indexes. Hence the -deepCheck option can be used to check internal index consistencies if mapping indexes are found to be proper when checked without -deepCheck option.

The tool can be used offline or online (that is, with or without database support). Since the tool analyzes NSE internal indexes, you should avoid updating indexes either manually or automatically while the tool is being used. NSE index update modifies NSE internal indexes, hence tool usage during this process may result in imcorrect results. Text search operations using NSE is possible while using the tool. Even basic database insert/update/delete operations are permitted during tool use.

In rare cases, the internal key list becomes exhausted. As a result NSE does not allow text index update. Searching is possible with this problem. The index verification tool verifies this key list exhaustion condition with the -deepCheck option and reports this condition. If the situation is reported, you need to plan to recreate the text index in parallel.


Conclusion

The index verification tool confirms the consistency of NSE internal indexes. It ensures NSE internal structures are proper, and that NSE is operating normally. This tool will report any inconsistencies, allowing you to save troubleshooting time for any NSE text index related errors at the production systems, and to resolve the problems.

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=830071
ArticleTitle=Verify DB2 Net Search Extender indexes using the NSE Index Verification tool
publish-date=04252013