A seamless upgrade to DB2 Text Search V10.5

IBM® DB2® Text Search is integrated with DB2 V10.5 and has been available since V9.5. It is equipped with a highly sophisticated, feature-rich, full-text search server and provides powerful indexing and search capabilities. This article describes various methods of upgrading to DB2 Text search V10.5. It uses an example to walk you through an upgrade scenario. In addition, it provides troubleshooting hints to resolve common upgrade problems.

Sobha Rani Cheruku (scheruku@in.ibm.com), Staff Software Engineer, IBM

Sobha CherukuSobha Rani Cheruku (Sobha) has been with IBM Software Labs for eight years, working with various teams like Rational and DB2. She is a certified DB2 administrator and is a level-3 support specialist for DB2 Text Search and Net Search Extender.



Venkata S Vanukuru (vvanukur@in.ibm.com), Software Developer, IBM

Venkata S VanukuruVenkata S Vanukuru (Venkat) has been with IBM software labs for past six years. He is a certified DB2 Application Developer and Advanced Database Administrator. His areas of expertise include DB2, TextSearch, and DB2 LUW Install Up and Running. He is currently working with Netezza Appliance Development at India software labs.



03 October 2013

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
Different ways of DB2 Text Search nstallation

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
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:

  1. As an instance owner, stop the DB2 instance and DB2 Text Search services.
  2. Back up the config folder and run configTool to upgrade the configuration of the text server.
  3. As root, run db2iupgrade from DB2 V10.5 installation path to upgrade DB2 instance.
  4. As an instance owner, run bkuptscfg.sh script to back up all Text Search configuration information.
  5. Perform a database upgrade to upgrade the database to DB2 10.5.
  6. 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:

  1. 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.
  2. 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:

  1. Test that the DB2 Text Search can be started successfully.
  2. 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.
  3. 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 system details, and JAR manifest version:
    configTool sysinfo
    Build version = 2.1.0.0
    Build number = 3765
    Build timestamp = 2013/03/19 10:25:53.228
    Jar manifest version = 2.1.0.0, 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.
  4. Ensure that the index updates and searches work successfully with no errors.

Troubleshooting hints

  • Problem: The command db2ts start for text fails with the error message "CIE0774E Failed to fetch the text search configuration."

    Resolution: There are several possible reasons to receive this error.
    • 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 command db2ts start for text fails 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.

    Resolution: 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.
  • Problem: The command db2ts start for text fails with the error message "IQQD0040E The client specified the wrong authentication token."

    Resolution: The port that is configured for text search is in use by another application. Set it to a new value by running configTool.
  • Problem: The command db2ts start for text fails with the error message "CIE00708 Error 1060 opening windows service."

    Resolution: Update the DB2 instance with the /j:"text_search" option and restart the text search to create the 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.

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 versionDB2 Text Search server version
V10.1 GA1.5.0.1 build 2038
V10.1 FP11.5.0.1 build 2053
V10.1 FP21.5.0.1 build 2053
V10.1 FP31.5.0.1 build 2114
Table 2. Dependencies table for DB2 Text Search V10.5
DB2 versionDB2 Text Search server version
V10.5 GA2.1 build 3765
V10.5 FP12.1 build 3913

Conclusion

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.


Acknowledgements

Thanks to Marion Behnen for reviewing this article.

Resources

Learn

Discuss

  • Get involved in the My developerWorks community . Connect with other developerWorks users while exploring the developer-driven blogs, forums, groups, and wikis.

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=946876
ArticleTitle=A seamless upgrade to DB2 Text Search V10.5
publish-date=10032013