Introduction to DB2 Text Search
IBM DB2 Text Search enables an IBM DB2 for Linux, UNIX®, and Windows® database user to create applications with full text-search capabilities by embedding full text-search clauses in SQL and XQuery statements. It is a highly sophisticated and feature-rich full text search engine from DB2 compared to its predecessor, Net Search Extender (NSE). NSE is already deprecated, and it is therefore advisable for the existing DB2 NSE customers to start using DB2 Text Search for managing their search applications.
Some of the key capabilities and differentiating features that make DB2 Text Search a better replacement search engine than NSE are:
- Integrated installation along with DB2
- Stored procedures for administration
- XPath-like syntax to search XML docs and from within XQuery
- Stronger linguistic processing support for over 20 languages
- Ability to control the index update processing
- Work load distribution with stand-alone server support
Advantages of upgrading to DB2 Text Search 10.5
The latest version of DB2 Text Search offers several new features and enhancements. The following features are available from DB2 V10.1 and above:
- Range-partitioned tables
- Data Partitioning Feature (DPF)
- Integration of a new text search server, including search feature improvements and better performance/scalability
- Infrastructure enhancements
- Support for a stand-alone server so the users can have a choice of integrated or stand-alone server
In addition, with DB2 V10.5, the following enhancements are available:
- Additional options to control the index update processing. The administrators can now define a time window where the index update can be executed, thus avoiding the load on system during peak time.
- Simplifies the indexing procedure by combining both create and initial update. The administrators can also control which data to be processed during the execution of initial update.
Upgrading DB2 Text Search
The upgrade procedure of DB2 Text Search can be viewed as a two-step process that includes installation of DB2 10.5 and configuration of a DB2 instance:
DB2 Text Search Upgrade to V10.5 = Installation of DB2 V10.5 with Text Search + Configuration of Text Search and DB2 instance
Installation of DB2 10.5 with Text Search
You can install DB2 Text Search in one of three different ways (as root and non-root), as shown in Figure 1:
Figure 1. Different ways of DB2 Text Search installation
Script install method
The script install method is an interactive way of installing DB2 using the utility db2_install. With this
mode of installation, the DB2 Text Search component is installed by default. This method
of installation is preferred where configuration is done after
installation. This is the preferred way to install DB2 when you are
upgrading DB2 Text Search.
Note: This is not available on Windows.
GUI install method
This method provides a GUI to install and configure DB2. The utility db2setup is used in this installation method. There is no option to directly upgrade an existing DB2 copy, so you can use this only to install and not to configure in the case of an upgrade scenario. With this method, DB2 Text Search is not installed by default (with typical install), so you need to choose custom install to select the text search component.
Silent install method
This method provides a command line interface to install and configure DB2. A response file is used for the installation. Like the GUI method, you can only use this for installation and not configuration in the case of an upgrade scenario. The keywords related to Text Search are:
*COMP = TEXT_SEARCH ** DB2 Text Search *DB2_INST.CONFIGURE_TEXT_SEARCH = NO ** YES or NO *DB2_INST.TEXT_SEARCH_HTTP_SERVICE_NAME = db2j_db2inst1 *DB2_INST.TEXT_SEARCH_HTTP_PORT_NUMBER = 55000
Only the first keyword is used for installation and the next three keywords are used for configuration.
Configure Text Search and the DB2 instance
This step of the upgrade procedure assumes that you successfully installed DB2 Text Search using any of the previously listed install methods. The configuration tasks need to be performed for DB2 and Text Search to use the new capabilities of DB2 V10.5. The necessary steps are depicted in Figure 2:
Figure 2. DB2 Text Search upgrade configuration
Here, two scenarios of upgrade have been considered:
- Scenario 1: An already up and running DB2 Text Search exists and planning to upgrade to DB2 V10.5.
- Scenario 2: DB2 Text search does not exist prior to upgrade but user wants to configure after upgrading to DB2 V10.5.
Configure DB2 Text Search for Scenario 1:
- As an instance owner, stop the DB2 instance and DB2 Text Search services.
- Back up the config folder and run configTool to upgrade the configuration of the text server.
- As root, run db2iupgrade from DB2 V10.5 installation path to upgrade DB2 instance.
- As an instance owner, run bkuptscfg.sh script to back up all Text Search configuration information.
- Perform a database upgrade to upgrade the database to DB2 10.5.
- Verify the text search server information is correct in the DB2 catalog table SYSIBMTS.TSSERVERS. If the value of the token or port number in the catalog table is empty or incorrect, update the text server information manually.
Configure DB2 Text Search for Scenario 2:
- Run db2iupgrade with –j option from DB2 V10.5 installation path to upgrade the DB2 instance and also to configure Text Search using the default/provided values for service name and TCP/IP port number.
- Perform a database upgrade to upgrade the database to DB2 10.5.
The detailed steps along with the execution of required commands and their outputs are captured in the following example.
Example for an end-to-end upgrade (V97 FP8 to V10.5)
This example illustrates the complete flow of upgrading an up-and-running DB2 server configured with Text Search to the latest release. In this setup, there is a DB2 database and text search indexes created on a DB2 V9.7 FP8 instance named db2inst1, and the following steps guide through the process of upgrading to V10.5 GA release.
Note: If the DB2 Text Search instance is enabled with rich text functionality, disable it before upgrading and enable it after successfully upgrading.
Step 1: As an instance owner, stop DB2 and Text Search instance services:
db2stop 08/22/2013 13:31:41 0 0 SQL1064N DB2STOP processing was successful. SQL1064N DB2STOP processing was successful. db2ts stop for text CIE00001 Operation completed successfully.
Step 2: As an instance owner, back up the DB2 Text Search configuration related files and folders located under ~/sqllib/db2tss/config:
cp -fR ~/sqllib/db2tss/config ~/backup_config
Step 3: As root, install the latest DB2 V10.5 release in a new installation path. In this setup, DB2 is installed in /opt/IBM/db2/V10.5_GA using the script installation method.
Step 4: As an instance owner, upgrade the DB2 Text Search server by running the "configTool upgradeConfig …" command, which copies, replaces, and modifies Text Search configuration files and folders:
/opt/IBM/db2/V10.5_GA/db2tss/bin/configTool upgradeConfigFolder -sourceConfigFolder /opt/IBM/db2/V10.5_GA/cfg/db2tss/config -targetConfigFolder /home/db2inst1/sqllib/db2tss/config The request was successfully executed.
Note: The configTool should be called from the location of the newly installed DB2 copy the instance is upgrading to.
Step 5: As root, upgrade the DB2 instance to V10.5 by running the db2iupgrade command.
Note:‘-j’ option is not specified with ‘db2iupgrade’ because the instance was already configured with Text Search in the previous DB2 version.
Step 6: As an instance owner, run the script ‘~/sqllib/db2tss/bin/bkuptscfg.sh’ to back up all Text Search configuration information. These details are written to db2tssrvall.cfg and db2tssrvupg.cfg files located under ~/sqllib/db2tss/config directory.
Note: ‘-j’ option is not specified with ‘db2iupgrade’ because the instance was already configured with Text Search in the previous DB2 version.
./bkuptscfg.sh db2inst1 Starting to backup DB2 Text Search server properties ... The text search server properties have been successfully backed up for "db2inst1".
Note: If this script is not executed, then the next step of upgrading the database might fail, resulting in the Text Search configuration values being reset to default.
Step 7: As an instance owner, start the DB2 instance service. Upgrade all the databases owned by the instance to the newer release by running the upgrade database command:
UPGRADE DATABASE booksdb DB20000I The UPGRADE DATABASE command completed successfully.
Along with the other necessary database upgrade tasks, this command also internally calls the following stored procedures to upgrade the text search specific catalog tables and indexes to the latest version:
- SYSTS_UPGRADE_CATALOG: This procedure performs the DB2 Text Search catalog table upgrade, including the tasks to create/drop/update the required tables, views, triggers, and so on.
- SYSTS_UPGRADE_INDEX: This procedure updates the DB2 Text Search specific index information in DB2 and Text Search catalog tables like sysibm.sysindexes, sysibmts.systsindexes, and so on.
These stored procedures are introduced from V10.1 onwards. If the procedures result in a failure during the database upgrade, they must be run separately, as shown in the following example:
CALL SYSPROC.SYSTS_UPGRADE_CATALOG('en_US',?) Value of output parameters -------------------------- Parameter Name : MESSAGE Parameter Value : CIE0213W The DB2 Text Search Catalog has been upgraded to the current version. You will now need to update the text search index using the SYSPROC.SYSTS_UPGRADE_INDEX stored procedure. index also needs to be updated by calling "SYSPROC.SYSTS_UPGRADE_INDEX". Return Status = 0 CALL SYSPROC.SYSTS_UPGRADE_INDEX('en_US', ?) Parameter Name : MESSAGE Parameter Value : CIE00001 Operation completed successfully. Return Status = 0
Step 8: Check the server properties values in sysibmts.systsservers table by comparing them with the values backed up in Step 6. If the token or port number is incorrect or empty, then the catalog table should be manually updated with the right value:
UPDATE sysibmts.systsservers SET SERVERAUTHTOKEN='b/iQVx8=' WHERE SERVERID=1 DB20000I The SQL command completed successfully.
Verify the Text Search Server upgrade
Once the upgrade is complete, verify that the DB2 Text Search is operational by following these steps:
- Test that the DB2 Text Search can be started successfully.
- Verify that the version value in the Text Search catalog table
sysibmts.systsdefaults is updated with the upgraded release number:
SELECT substr(value,1,15) FROM sysibmts.systsdefaults WHERE name='VERSION' 1 --------------- DB2 10.05.0000 1 record(s) selected.
- Ensure that the Text Search uses the correct and compatible version of the
server. For V10.5, the following command can be used to display the system
information including the server version, build number, operating
and JAR manifest version:
configTool sysinfo Build version = 126.96.36.199 Build number = 3765 Build timestamp = 2013/03/19 10:25:53.228 Jar manifest version = 188.8.131.52, 3765, 2013/03/19 10:25:53.228 Operating system name = AIX Operating system architecture (JVM) = ppc64 Operating system version = 7.1 Java version = IBM J9 VM 1.7.0 JVM: 2.6 JVMInfo: JRE 1.7.0 AIX ppc64-64 20121024_126071 (JIT enabled, AOT enabled) J9VM - R26_Java726_SR3_20121024_1635_B126071 JIT - r11.b02_20120924_26343a GC - R26_Java726_SR3_20121024_1635_B126071 J9CL - 20121024_126071
For the lower versions, configTool printAll command can be used to display these details.
- Ensure that the index updates and searches work successfully with no errors.
Problem: The commandResolution: There are several possible reasons to receive this error.
db2ts start for textfails with the error message "CIE0774E Failed to fetch the text search configuration."
- Verify if the authenticationToken is empty and generate one by running configTool with generateToken option.
- Execute the startup.sh script located in the /sqllib/db2tss/bin/ folder to receive the complete error message. If it fails with the message similar to "IQQG0149E The system cannot create the directory," verify that the paths set for DB2 Text Search configuration parameters (InstallPath, logPath, and tempDirPath) are valid and with write access to instance owner.
Problem: The commandResolution: The DB2 instance is upgraded to a new fix pack/release, but the text search server is not upgraded. Upgrade the text server by running the configTool with the appropriate options.
db2ts start for textfails with the error message "CIE00340 Cannot start executable program "/bin/sh." An attempt to run the configTool throws the error "java.io.FileNotFoundException:" on any of the DB2 Text Search configuration files.
Problem: The commandResolution: The port that is configured for text search is in use by another application. Set it to a new value by running configTool.
db2ts start for textfails with the error message "IQQD0040E The client specified the wrong authentication token."
Problem: The commandResolution: Update the DB2 instance with the /j:"text_search" option and restart the text search to create the service.
db2ts start for textfails with the error message "CIE00708 Error 1060 opening windows service."
Problem: All DB2 Text Search administration commands and searches fail with the error message "CIE00756E Connect failed on host localhost" after upgrading to a latest fix pack/release.Resolution: The option –j:"text_search" should be specified only to configure the DB2 Text Search for the first time. If an instance is already configured with DB2 Text Search and is updated/upgraded to the latest fix pack/release with –j option, a default port number is assigned for Text Search, overwriting the previous value. Verify that the port number specified in the SYSIBMTS.TSSERVERS table matches with the value returned by configTool. If any mismatch is found, update the catalog table manually.
DB2 Text Search and compatible text servers
Refer to the following tables and verify the DB2 Text Search server version number for the corresponding DB2 fix pack/release:
Table 1. Dependencies table for DB2 Text Search V10.1
|DB2 version||DB2 Text Search server version|
|V10.1 GA||184.108.40.206 build 2038|
|V10.1 FP1||220.127.116.11 build 2053|
|V10.1 FP2||18.104.22.168 build 2053|
|V10.1 FP3||22.214.171.124 build 2114|
Table 2. Dependencies table for DB2 Text Search V10.5
|DB2 version||DB2 Text Search server version|
|V10.5 GA||2.1 build 3765|
|V10.5 FP1||2.1 build 3913|
This article lists the salient features of DB2 Text Search, describes various upgrade methods, and focuses on step-by-step procedures for upgrading to DB2 Text Search V10.5. It also includes troubleshooting hints that can help you easily fix the problems without having to contact IBM support.
Thanks to Marion Behnen for reviewing this article.
- "DB2 Text Search server deployment scenarios " explains DB2 Text Search server deployment scenarios.
- "Installing and configuring DB2 Text Search " provides the details on installing and configuring DB2 Text Search.
- "DB2 Text Search key features and concepts " describes the key features and concepts of DB2 Text Search.
- In "DB2 Text Search, Part 1" (developerWorks, April 2010), get the details on the basics of DB2 text search, including text index creation and administration.
- "Enable C++ applications for Web services using XML-RPC " (developerWorks, June 2006) is a step-by-step guide to exposing C++ methods as services.
- 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 involved in the My developerWorks community . Connect with other developerWorks users while exploring the developer-driven blogs, forums, groups, and wikis.