![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
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
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
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.
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.
Learn
- Refer to the DB2 Information Center for more details on Net Search
Extender.
- Read the DB2
Version for Linux, UNIX, and Windows documentation for a better
understanding of DB2 and related components.
- Visit the developerWorks
Information Management zone to find more resources for DB2
developers and administrators.
- Stay current with developerWorks technical events and
webcasts focused on a
variety of IBM products and IT industry topics.
- Follow developerWorks on
Twitter.
- Watch developerWorks on-demand demos ranging from product installation
and setup demos for beginners, to advanced functionality for experienced
developers.
Get products and technologies
- Download a trial
version of DB2
for Linux, UNIX, and Windows.
- Build your next
development project with IBM trial software, available
for download directly from developerWorks.
Discuss
- Participate in the discussion forum.
- Get involved in the My developerWorks
community. Connect with other developerWorks users while exploring
the developer-driven blogs, forums, groups, and wikis.
Lalatendu 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.
