Introduction

OmniFind Text Search Server for DB2 for i (5733-OMF) is a no additional charge licensed product that was first released in IBM i 6.1. This product allows users to perform full text search on text DB2 columns using the CONTAINS and SCORE SQL built in functions. For example, if the search is for "the cat chased a mouse", the search would also find documents that contain the phrase "the cats were chasing some mice". In IBM i 7.1 the product added support for searching system objects not associated with a DB2 column, such as spooled files or IFS stream files. In the latest round of improvements, new stored procedures have been added to check the health of text search indexes and servers. This article covers those enhancements. You can get a general background for the OmniFind Text Search Server from the white paper included in the additional resources section of this article.

The OmniFind Text Search Server for DB2 for i health checker support provides a set of stored procedures that can be used to diagnose configuration problems with a text search server or index. These health checker stored procedures will verify if the text search servers or indexes are correctly functioning and return a report highlighting any problems that are detected.

The IBM OmniFind Text Search Server product itself also provides stored procedures for the most common administrative functions, which makes it very easy for users to create and maintain indexes. For example, the SYSPROC.SYSTS_CREATE() procedure can be called to create an index and the SYSPROC.SYSTS_UPDATE() procedure can be used to update an index. However, sometimes these administrative stored procedures calls fail with errors such as:

• Unable to create an index due to limited authorities
• Failure to start a text server because no Java J2SE is installed
• The text search index doesn't include the latest data changes due to the automatic update scheduler has not started
• The search of an index not functioning properly because the text search server was shut down by mistake

In these cases, the health checker procedures can be used to diagnose the problems with your IBM OmniFind installation. The stored procedure, SYSPROC.SYSTS_HC_USR_AUTH(), can be used to check if a user has necessary authorities to create an index. The SYSPROC.SYSTS_HC_GENERAL() procedure can be used to verify that all of the required software is installed. The SYSPROC.SYSTS_HC_IDX() procedure can be used to determine if there are any problems with an index configuration and the SYSPROC.SYSTS_HC_SVR() procedure can be used to analyze if a text search server is functioning correctly. All of these health checker procedures report warnings and errors by returning a stored procedure result set. The contents of this result set can be used by users to identify and fix the problem or provide detailed information to IBM Support personnel for advanced diagnosis.

Back to top

Service Details

This new OmniFind health checker support is delivered with PTF SI44462 in IBM i 7.1. The health checker support requires the IBM Application Runtime Expert (ARE) for i product, which is part of IBM i OS (5770-SS1) in IBM i 7.1. Visit the IBM ARE Support website for information on the required software and fixes for installing the ARE product.

Back to top

Checking User Authorities

The DB2 stored procedure SYSPROC.SYSTS_HC_USR_AUTH() is used to report problems with the authorities of the invoking user. A result set is returned from this stored procedure.

If the parameter INFO_TYPE is omitted, SYSPROC.SYSTS_HC_AUTH() returns warning and error messages if there are any.

The stored procedure checks the following common problems:

• *EXECUTE permission on administrative stored procedures
• User permission on administrative catalogs
• Job control special authority (for starting servers)
• Other required authorities

Syntax

>>SYSTS_HC_USER_AUTH(------------)-----------------><
                -----INFO_TYPE---

The schema qualifier is SYSPROC.

Parameter

INFO_TYPE

Return Result

A result set is returned from the stored procedure. The result set is composed of the following columns:

• TYPE – CHAR(1), the type of the message, i.e., 'I', 'W', or 'E'. The message types represents:
  • 'I' – Information message, it is not a warning or error
  • 'W' – Warning message, if not corrected, it may cause problems
  • 'E' – Error message, indicates an error is found. The error needs to be corrected for OmniFind to work correctly
• CODE – INTEGER, the Info, Warning or Error code of the message. Code ranges are:
  • 100-299: information messages
  • 300-499: warning messages
  • 500-999: error messages

• ITEM – VARCHAR(128), the specific item checked, e.g. SYSPROC.SYSTS_START() or QSYS2.SYSTEXTSERVERS.
• MESSAGE – VARCHAR(512), the detailed Info, Warning or Error message.

Examples

To return all health checking results for the authorities of the invoking user:

CALL SYSPROC.SYSTS_HC_USR_AUTH('A')

Sample output is like this:

To return the warning and error checking results for the authorities of the invoking user:

CALL SYSPROC.SYSTS_HC_USR_AUTH()

Back to top

Checking General Configurations

The DB2 stored procedure SYSPROC.SYSTS_HC_GENERAL() is used to check the general health of the OmniFind Text Search Server for DB2 for i product. The procedure checks the configuration of the product.

A result set is returned when the stored procedure has successfully completed. The result set reports issues with the product install or configuration that may need to be addressed by an administrator.

The following items are checked by this stored procedure:

• The installation of required LPPs (ICU, Java, QSH and etc.)
• PTFs that applied in the system
• The OmniFind administrative stored procedures
• The configuration of varyon and varyoff programs for Independent Auxiliary Storage Pools (iASP)
• Administrative catalogs
• OmniFind default values in QSYS2.SYSTEXTDEFAULTS

Syntax

>>SYSTS_HC_GENERAL(-------------- )---------------------><
		   ---INFO_TYPE-----
		   ---INFO_TYPE, AUTO_FIX-----

The schema qualifier is SYSPROC.

Parameters

INFO_TYPE

Specifies the information type to be returned, see INFO_TYPE in SYSTS_HC_AUTH().

AUTO_FIX

Return Result

A result set is returned from the stored procedure. The result set is composed of the following columns:

• TYPE – CHAR(1), the type of the message, see TYPE in SYSTS_HC_AUTH().
• CODE – INTEGER, the Info, see CODE in SYSTS_HC_AUTH().
• ITEM – VARCHAR(128), the specific item checked. For example: SYSPROC.SYSTS_START() or QSYS2.SYSTEXTSERVERS.
• MESSAGE – VARCHAR(512), the detailed Info, Warning or Error message.

Examples

To take the general health checking and return all messages:

CALL SYSPROC.SYSTS_HC_GENERAL('A', 0)

Sample output is like this:

To return all warning and error messages:

CALL SYSPROC.SYSTS_HC_GENERAL()

Back to top

Checking Indexes

The DB2 stored procedure SYSPROC.SYSTS_HC_IDX() is used to check a particular text index or all indexes in the current Independent Auxiliary Storage Pool (IASP) group, depending on the parameters given. If a qualified index name is given, the health checking report applies to the particular index; otherwise, all text indexes in the IASP group will be checked. A result set is returned when the stored procedure is successfully completed. The stored procedure SYSPROC.SYSTS_HC_IDX_SN() performs the same function but accepts index schema and index name as its parameters.

A list of the items checked by this procedure are:

• The base table for the index exists
• The index, column and configuration items are correct in catalogs SYSTEXTINDEXES, SYSTEXTCOLUMNS and SYSTEXTCONFIGURATION
• Records in SYSTEXTSERVERHISTORY are valid
• The collection directory of the index exists, if the index is defined in a local server
• The view for the index exists
• The server associated with the index is active
• The update scheduler is configured correctly
• The insert, update and delete triggers exist on the based table for the index
• The staging table for the index exists
• The owner of the triggers, staging table and index view are consistent
• The authorities of the index owner on the base table and administrative catalogs are correct
• The authority of the index owner includes the ability to modify the job scheduler entry used for the scheduled update
• The index owner has he special authority *JOBCTL..

Syntax

>>-SYSTS_HC_IDX(-------------- )------------------------><
		---INDEXSCHEMA, INDEXNAME, INFO_TYPE, AUTO_FIX
		---INDEXSCHEMA, INDEXNAME, INFO_TYPE
		---INFO_TYPE, AUTO_FIX
		---INFO_TYPE
>>SYSTS_HC_IDX_SN(INDEXSCHEMA,INDEXNAME)---------------><

The schema qualifier is SYSPROC.

Parameters

INFO_TYPE

Specifies the information type to be returned. See INFO_TYPE in SYSTS_HC_AUTH().

AUTO_FIX

Reserved for future use only. See AUTOFIX in SYSTS_HC_GENERAL().

INDEXSCHEMA

Specifies the schema of the index to be checked. The type of the parameter is VARCHAR(128) and CCSID 1208. The value must be a valid SQL name. The index schema parameter is case sensitive and will not be folded to upper case automatically.

INDEXNAME

Specifies the name of the index to be checked. The type of the parameter VARCHAR(128) and CCSID 1208. The value must be a valid SQL name. The index name is case sensitive and will not be folded to upper case automatically.

If parameter list is empty, health checking is applied to all existing indexes, warning and error messages are returned and no auto fixing is applied.

Return Result

A result set is returned from the stored procedure. The result set is composed of following columns:

• ID – INTEGER, the index ID.
• TYPE – CHAR(1), the type of the message, see TYPE in SYSTS_HC_AUTH().
• CODE – INTEGER, the Info, see CODE in SYSTS_HC_AUTH().
• ITEM – VARCHAR(128), the specific item checked, e.g. the staging table, the view for the index.
• MESSAGE – VARCHAR(512), the detailed Info, Warning or Error message.

Examples

To check the index TESTSCHEMA.TESTINDEX, returning all messages, no auto fix applied:

CALL SYSPROC.SYSTS_HC_IDX('TESTSCHEMA', 'TESTINDEX', 'A', 0)

Sample output is like this:

To check the index TESTSCHEMA.TESTINDEX, returning only error messages:

CALL SYSPROC.SYSTS_HC_IDX('TESTSCHEMA', 'TESTINDEX', 'E')

To check all indexes and return warning and error messages:

CALL SYSPROC.SYSTS_HC_IDX()

Back to top

Checking Servers

The DB2 stored procedure SYSTS_HC_SVR() is used to check the health of a specific local text server, or all local servers that have been configured. If a server ID is specified, the procedure checks only that text server, otherwise it checks all servers configured for the current Independent Auxiliary Storage Pool (IASP) group. A result set is returned from the stored procedure. The procedures SYSPROC.SYSTS_HC_SVR_T() and SYSPROC.SYSTS_HC_SVR_IT() perform the same function but accept different parameters for qualifying which servers are checked and what information is returned.

The stored procedure checks the following items for text servers:

• Authentication token is valid
• IP address is valid
• Server path is valid
• Server port is valid

For each active local server, additional checks are done to verify that the required server jobs are running and the server port can be contacted.

Syntax

>>SYSTS_HC_SVR(---------------)-----------------------><
		----SERVERID, INFO_TYPE, AUTO_FIX ----
		----SERVERID----
		----INFO_TYPE, AUTO_FIX----
>>SYSTS_HC_SVR_IT(SERVERID,INFO_TYPE)--------------------><
>>SYSTS_HC_SVR_T(INFO_TYPE)----------------------------><

The schema qualifier is SYSPROC.

Parameters

SERVERID

Specifies the identifier of the text server to be checked. The server identifier for a specific server can be obtained by using the IBM Systems Director Navigator for i. Using Director Navigator to obtain server information and status is discussed in the InfoCenter.

This information can also be obtained from the system catalogs with an SQL query like this:

SELECT SERVERID, SERVERNAME, SERVERPATH, ALIASNAME FROM QSYS2.SYSTEXTSERVERS;

Specifies the information type to be returned, see INFO_TYPE in SYSTS_HC_AUTH().

AUTO_FIX

Reserved for future use only, see AUTO_FIX in SYSTS_HC_GENERAL().

Return Result

A result set is returned when the successful complete of the stored procedure. The result set is composed of following columns:

• ID – INTEGER, the server ID.
• TYPE – CHAR(1), the type of the message, see TYPE in SYSTS_HC_AUTH().
• CODE – INTEGER, the Info, Warning or Error code of the message, see CODE in SYSTS_HC_AUTH().
• ITEM – VARCHAR(128), the specific item checked, generally not used in server checking since the MESSAGE gives very clear information.
• MESSAGE – VARCHAR(512), the detailed Info, Warning or Error message.

Examples

To check the server with id 50 and return all messages:

CALL SYSPROC.SYSTS_HC_SRV(50, 'A', 0)

Sample output is like this:

To check server with id 50 and return warning and error messages.

CALL SYSPROC.SYSTS_HC_SRV(50)

Back to top

Summary

The OmniFind health checker provides a set of DB2 stored procedures that can be invoked by users for checking general configurations, text servers and indexes. It can be used to return warning and/or error messages if there are any problems with the OmniFind Text Search Server for i. The information can then be used to understand the cause of a difficult problem quickly and take appropriate corrective action.

Back to top

Resources

• OmniFind Text Search Server for DB2 for i
• OmniFind White Paper
• OmniFind for IBM i
• DB2 for i Forum

About the authors

You Liang Wu joined IBM in 2010. He currently is working on the development for OmniFind Text Search Server for DB2 for i.

Nick Lawrence has worked on DB2 for i for twelve years. His responsibilities include full text search for DB2 and SQL/XML.

Rate this article

Comments

Back to top

Table of contents

• Introduction
• Service Details
• Checking User Authorities
• Checking General Configurations
• Checking Indexes
• Checking Servers
• Summary
• Resources
• About the authors
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

Dig deeper into IBM i on developerWorks