Query behavior in Maximo on SQL Server - Full-Text Search

Technote (troubleshooting)


Problem(Abstract)

No records are returned when querying in a TEXT enabled field in Maximo.

Symptom

When querying in a TEXT enabled field in Maximo you see the following error in the Maximo log.

Msg 7619, Level 16, State 1, Line 1
Execution of a full-text operation failed. A clause of the query contained only ignored words.


Cause

The query contains ignored words.

Diagnosing the problem

An example will illustrate the problem of querying on ignored words.

The maxdemo database contains an asset record with the description:'Database software for the desktop'.

select * from asset where description ='Database software for the desktop'

The asset.description field is defined as a TEXT field and Maximo uses full text search to form the where clause.

If you query on the string "database software for the desktop", Maximo generates a query which contains a where clause which includes the following syntax:

select * from asset
where contains(description , ' FORMSOF(INFLECTIONAL,database)
& FORMSOF(INFLECTIONAL,software) & FORMSOF(INFLECTIONAL,for)
& FORMSOF(INFLECTIONAL,the) & FORMSOF(INFLECTIONAL,desktop) ')

The above query gives the error:

Msg 7619, Level 16, State 1, Line 1
Execution of a full-text operation failed. A clause of the query contained only ignored words.

However, if you query on the string, "database software desktop", the query will return the expected results.

select * from asset
where contains(description , ' FORMSOF(INFLECTIONAL,database)
& FORMSOF(INFLECTIONAL,software)
& FORMSOF(INFLECTIONAL,desktop) ')

Resolving the problem

By default, most description fields in Maximo are defined as the TEXT searchtype. This means that a SQL Server full-text index is created on that field, and searching is done using the full-text catalog, rather than a sql wildcard search.

You can remove the TEXT property from the description field, if it is necessary to query on common words which SQL Server considers as noise words. This can be performed in database configuration. You can change the search type from TEXT to WILDCARD if desired.

If you want to use full-text, you must avoid querying on noise words. Noise words are words such as "a", "the", "for" and numbers. The noise words are stored in a file which can be edited if desired. Refer to SQL Server Documentation for further information on this file.


Cross reference information
Segment Product Component Platform Version Edition
Systems and Asset Management Tivoli Asset Management for IT All
Systems and Asset Management IBM Maximo Asset Management Essentials All

Rate this page:

(0 users)Average rating

Document information


More support for:

IBM Maximo Asset Management

Software version:

6.0, 6.1, 6.2, 6.2.1, 6.2.2, 6.2.3, 6.2.4, 6.2.5, 6.2.6, 6.2.7, 6.2.8, 7.1, 7.1.1, 7.5

Operating system(s):

AIX 64bit, HP-UX, Linux, Solaris, Windows, Windows 2003 server

Reference #:

1313191

Modified date:

2009-01-13

Translate my page

Machine Translation

Content navigation