IBM Support

Maximo Integrity Checker performance issue

Troubleshooting


Problem

Maximo Integrity Checker utility can take a long time to verify domains. For every column that is mapped to a Maximo domain, Integrity Checker verifies that each value in the column exists in the domain. A warning is reported if any value in the column does not exist in the domain.

Symptom

Especially on large databases, with large amounts of data in the bigger Maximo tables such as WorkOrder and WOStatus, verifying the values in these tables can be time intensive. There is no way to turn off this feature of Integrity Checker.

Cause

Integrity Checker verifies that for every Maximo column that is mapped to a domain, every value that is stored in that column is a value from the domain. On a table with millions of rows of data, and several columns that are mapped to domains, this can be a time consuming process.

Environment

Windows, Maximo 7.x

Diagnosing The Problem

Reviewing an Integrity Checker log, domain checking begins with this informative message
BMXAA0422I - Verifying column value list values. Fri Aug 29 10:17:56 PDT 2014
Domain checking is complete with this informative message, which indicates that Integrity Checker is moving on to the next phase:
BMXAA0423I - Verifying column-autokey mappings. Fri Aug 29 11:07:19 PDT 2014

Note that each message is followed by a timestamp. The difference between the two timestamps is the time that Integrity Checker spent verifying domain data - in this case, nearly 50 minutes just to check the domains.

You can determine which columns of a table are mapped to which domains with this SQL query:
Select objectnmame, attributename, defaultvalue, domainid From maxattribute Where objectname='<tablename>' And domainid Is Not null Order By 2 ;

Resolving The Problem

Improved performance has been observed by adding the following 3 temporary database indexes to the domain tables ALNDomain, SynonymDomain, and NumericDomain:

Create Index alndomain_tmp1 On alndomain(domainid, value) ;
Create Index syndomain_tmp1 On synonymdomain(domainid, value) ;
Create Index numdomain_tmp1 On numericdomain(domainid, value) ;

In the example above, after adding the 3 indexes and running Integrity Checker again, elapsed time to verify the domain data was reduced from 50 minutes to 15 minutes. Note that these indexes can slightly slow the process of adding new values to domains or updating existing values. To maintain the original integrity of the database, such temporary indexes should be removed again. However, since seldom is a value added to or updated in an existing domain, the benefits of retaining the indexes greatly outweigh any negative impact.

[{"Product":{"code":"SSLKT6","label":"IBM Maximo Asset Management"},"Business Unit":{"code":"BU055","label":"Cognitive Applications"},"Component":"Upgrade Integ Checker","Platform":[{"code":"PF033","label":"Windows"}],"Version":"7.1;7.5","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":"Not Applicable","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":"Not Applicable","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":"Not Applicable","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"LOB59","label":"Sustainability Software"}}]

Document Information

Modified date:
17 June 2018

UID

swg21683325