Question & Answer
Question
Are there certain types of searches that can be implemented in Maximo that utilize defaults?
Cause
Limited Documentation
Answer
Maximo Search Methodologies
Maximo supports four different search methodologies: Exact, None, Text, and Wildcard. Each field in the Maximo database can be set to employ any of these methods with the limitation of Text searching, as it does not apply to numeric columns.
To reduce user confusion, it is a best practice use search types consistently. If some fields are set to Exact and others to Wildcard, it will be difficult for users to remember which search type is valid in which field. The best approach is to set all fields longer than twenty five (25) characters to Text style searching and all other character fields to Exact. Using these criteria, training users on the use of wildcards, and training users on how to identify Text search fields will be very powerful in attaining best performance from database searches while providing simplicity in searching.
Any search executed by Maximo generates an SQL select statement that is passed to the database server to find records. The database uses relational database technology indexing to search for records. Indexes can be thought of as a sorted binary representation of the columns defined by the index. Finding records using an index is very fast and efficient, requires minimal memory, and requires minimal storage I/O. If an index is not defined for a particular search or an index cannot be used, each individual record in a table must me read into memory and searched for the values. Non-indexed searching is very inefficient and is triggers a Full Table Scan.
Exact default searching: When a fields search behavior defaults to Exact search method, searches are performed on fields with the exact value the user enters. This method is case sensitive and will not find items where the case of the value entered is different.
If a user enters the value "Boiler" in an Exact field search, only records where the entire column matches the word "Boiler" will be found. Instances of "BOILER", "Boilers", "Boiler is overheating" will not be found.
Exact searching is the most efficient database search method as long as the field searched is indexed. This method enables the database to analyze the SQL select statement and employ indexes to their maximum potential.
A SQL example of an exact search is:
SELECT * from WORKORDER where WONUM = '123';
Additional functionality can be included in an Exact search by including wildcards in the search criteria; however, caution should be exercised when using wildcards as they can impact performance of not only the search executed but also all users using the same database.
Wildcard characters include % (percent sign) and _ (underscore character). The most common wildcard is %.
If a user is searching for a record that starts with "Boiler" a value of "Boiler%" may be entered and all records where the specified column starts with "Boiler" will be found including "Boiler", "Boilers", "Boiler is overheating". This method can still use indexes and will be fairly efficient though not as efficient as an Exact search.
If a user is searching for a record that contains "Boiler" somewhere in the field, a value of "%Boiler%" may be entered and all records where the specified column contains the word "Boiler" will be found including "Boiler", "Boilers", "Boiler is overheating", "The Boiler" and "When the Boilers are on". This method is the most inefficient search. Indexes cannot be used with this search approach and each record from the table must be read and searched for the value. Users should avoid this search method whenever possible. This search method is the same as employing the Wildcard search method as the default search methodology for a field.
Underscore characters (_) are rarely used as wildcards. These can be used to search for variants on a single character. A user can enter "the animal is _ame" and the results can return, "the animal is lame" and "the animal is tame". The same caveat applies to using this character as the first character in a search as the %. This approach will disable the use of indexes and is very inefficient.
None default method: This method is designed to disable searching on the field it is assigned to. This method is often used when extra columns are defined to maintain data but business requirements do not need users to be able to search on those fields. Other good candidates for the
Text default searching: When a fields search behavior defaults to the Text search method, searches are performed on fields using a complex text algorithm to determine the root of the word searched. Searches are done for all instances that contain the root word or any derivative of the root word. This method is not case sensitive and will find items regardless of the case entered.
If a user enters the value "Boiler" in a Text field search, records where the column matches the word "Boiler" will be found. In addition, instances of "BOILER", "Boilers", "Boiler is overheating", "Boiling" and "Boiled" will also be found.
While Text searching holds some characteristics in common with Wildcard searching, it is much more efficient because preprocessing is done by the database server to determine the best use of indexes. This method enables the database to analyze the SQL select statement and employ indexes to find records.
Text searches have a substantial number of rules associated with them. Users may often be confused by the results of a text search. Database technology Text searching does not employ punctuation in the traditional form. Database technologies have a method designed to remove punctuation from Text searches.
In an effort to expand on the Text search capability, Maximo employs a system to replace certain punctuation characters. Commas (,) are replaced with logical "OR". This allows a user to search for value1 OR value2 with a single search. Wildcards are not modified and are passed as is to the database to provide wildcard like functionality. The database parses out any remaining punctuation from the search string and searches using the database Text search rules.
Some items may be reserved words and prevent typical searching if used incorrectly. Some common words are not searchable as they are common words found in any phrase so they are not indexed. These may include the word "the", "and", etc...
Examples:
- Searching for "Boiler-1" in a Text search field will search for the word "Boiler" and, according to the rules of Maximo, any instances of the number one (1) near the word Boiler. In fact, this search will return no results because the number 1 is a reserved word.
- Searching for Boiler-99 will return all records where the word "Boiler" is near the number 99. This means it will return "99 large boilers", "boiler-99" and "BOILED ham for 99 people".
- Searching for "lightblub, 100W" will search for all records that have either "lightbulb" OR "100W" in them.
Text searching rules can cause problems if naming conventions include dashes, percent signs, underscores or commas. During implementation, it is a best practice to stay away from naming conventions that use these characters.
Out of the box Maximo employs Text searches for character fields exceeding twenty-five (25) characters in length. Although these searches are not as efficient as 'Exact" searches, they are many times better than Wildcard searches. Users should be trained on Text search usage. It is recommended that Text remain the search type for long character fields.
Wildcard default searching: When a fields search behavior defaults to the Wildcard search method, searches are performed on fields with the "%" wildcard on the beginning and end of the search value the user enters. This method is case sensitive and will not find items where the case of the value entered is different.
If a user enters the value "Boiler" in a Wildcard field search, all records where the entire column matches the word "Boiler" will be found. Instances of "BOILER" (all upper case) will not be found because it is not the same case.
Wildcard searching is the least efficient database search method because it cannot use indexes. This method appears to provide the most flexibility in searching but, as a default, becomes too demanding for the database server when large numbers of concurrent users are using it.
A SQL example of a wildcard search is:
SELECT * from WORKORDER where WONUM like '%123%';
When Wildcard searches are enabled, by default, users can force exact match search by preceding the search value with an "=" (equal sign). This will change the search method to an Exact search method.
Example:
Entering the value =123
Results in the following select statement:
SELECT * from WORKORDER where WONUM = '123';
Users can also modify the Wildcard search method by using a wildcard character in the search string.
Example:
Entering the value 123%
Results in the following select statement:
SELECT * from WORKORDER where WONUM like '123%';
This will allow the use of indexes since there is not a leading wildcard on the search value.
When the default search method is Wildcard users should be encouraged to use "=" in front of, or a wildcard in all search values unless absolutely necessary. Since it is common for users to forget to enter these values, IBM commonly recommends changing the default search methodology to Exact
Wildcard searches include % (percent sign) at the beginning and end of each search value.
If a user is searching for a record that contains "Boiler" a value of "Boiler" may be entered and all records where the specified column contains the word "Boiler" will be found including "Boiler", "Boilers", "Boiler is overheating", "The Boiler" and "When the Boilers are on". This method is the most inefficient search. Indexes cannot be used with this search approach and each record from the table must be read and searched for the value. Users should avoid this search method whenever possible.
If a user is searching for a record that starts with "Boiler," a value of "Boiler%" may be entered and all records where the specified column starts with the word "Boiler" will be found including "Boiler" and "Boilers". This search method is the same as employing the Exact search method and appending a "%" to the search value.
Out of the box, Maximo uses the Wildcard search for all character fields shorter than twenty-six (26) characters. It is a best practice to consider changing the default search method to Exact when concurrent usage is greater than 50 users.
Note: When saved queries are created with the default wildcard search method, saved queries are saved with the leading wildcards. These queries must be modified manually after changing the default search method.
Changing the default search method of fields
Each field in Maximo has a default search method associated with it. The default Out of the box approach is Wildcard for character fields under 26 characters and Text for character fields over 25 characters. The type of search associated to a field can be viewed or modified in two ways:
Through the Maximo user interface, one field at a time. Refer to Administering Maximo Asset Management in the IBM Knowledge Center for more information on this approach. Links for the guides: Maximo 7.6 and Maximo 7.5.
Mass updates of the maxattribute table:
Shut down the Maximo Application Server(s)
Execute the following two updates against the Maximo schema via an interactive SQL utility.
update maxattribute set searchtype = 'EXACT' where searchtype = 'WILDCARD'
update maxattributecfg set searchtype = 'EXACT' where searchtype =
'WILDCARD'
Start the Maximo Application Server(s).
For more information on database text indexes, review the Oracle Text Reference Guide.
Date searches are not a specific type of Maximo search methodology and are not covered by this document. However; there are technotes for Oracle and Microsoft SQL Server which cover specific SQL syntax related to date searching. Date queries can become quite complex and it is recommended that collaboration with a DBA be included in the planning of date searches.
Related Information
[{"Product":{"code":"SSLKT6","label":"IBM Maximo Asset Management"},"Business Unit":{"code":"BU055","label":"Cognitive Applications"},"Component":"System Related","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"Version Independent","Edition":"","Line of Business":{"code":"LOB59","label":"Sustainability Software"}},{"Product":{"code":"SSLKTY","label":"Maximo Asset Management for IT"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":" ","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"LOB59","label":"Sustainability Software"}},{"Product":{"code":"SSWK4A","label":"Maximo Asset Management Essentials"},"Business Unit":{"code":"BU055","label":"Cognitive Applications"},"Component":" ","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"LOB59","label":"Sustainability Software"}},{"Product":{"code":"SSKTXT","label":"Tivoli Change and Configuration Management Database"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":" ","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"LOB59","label":"Sustainability Software"}},{"Product":{"code":"SS6HJK","label":"Tivoli Service Request Manager"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":" ","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"LOB59","label":"Sustainability Software"}},{"Product":{"code":"SSWT9A","label":"IBM Control Desk"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":" ","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"LOB59","label":"Sustainability Software"}}]
Was this topic helpful?
Document Information
Modified date:
17 June 2018
UID
swg21375684