IJ39625: MAXIMO DEFAULT CONFIGURATION FOR ORACLE DOES NOT NOT ALLOW USING INDEXES ON DATE FIELDS. THIS APPLIED ONLY TO ORACLE DATABASES.

APAR status

• Closed as program error.

Error description

• PROBLEM:
  
  When there is a database search on an Oracle DATE field, MAXIMO
  constructs the SQL statement based using the SQL driver "ts"
  which is translated at Oracle level to "TO_TIMESTAMP". Due to an
  Oracle limitation, when an Oracle DATE field is searched using
  TO_TIMESTAMP function, the index on the field is not used.
  
  PERFORMANCE ISSUE:
  
  As indexes on DATE fields are not being used, system
  performance is impacted if the search involves a DATE field and
  can lead to FULL TABLE SCAN which is a performance heavy
  operation for large tables.
  
  STEPS TO REPRODUCE:
  1- Enable Maximo Logs.
  2- Create an index on a DATE field on a table with large amount
  of data (for example WORKORDER.REPORTDATE).
  3- Through MAXIMO Work Order Tracking application, use "More
  Search Fields", scroll down and enter a value in "Reported Date"
  and click "Find".
  4- You will notice the log file contains an SQL similar to the
  following:
  select * from workorder where ((woclass = 'WORKORDER' or woclass
  = 'ACTIVITY') and historyflag = 0 and siteid = 'BEDFORD' and
  istask = 0 and reportdate >= {ts '2022-04-12 00:00:00.000' })
  5- At the database level that is translated to:
  select * from workorder where ((woclass = 'WORKORDER' or woclass
  = 'ACTIVITY') and historyflag = 0 and siteid = 'BEDFORD' and
  istask = 0 and reportdate >= TO_TIMESTAMP ('2022-04-12
  00:00:00.000' , 'YYYY-MM-DD HH24:MI:SS.FF'))
  6- Ensure Oracle PLAN_TABLE is created on the maximo schema -
  use Oracle documentation to create that table. Modify query in
  step 5 to only include report date and run explain plan for it,
  as follows:
  select * from workorder where reportdate >= TO_TIMESTAMP
  ('2022-04-12 00:00:00.000' , 'YYYY-MM-DD HH24:MI:SS.FF'))
  7- Run "select * from plan_table;". You will notice that the
  query resulted in a full table scan and is not using the index
  on the REPORTDATE field.
  
  CURRENT ERRONEOUS RESULT:
  Index is not being used.
  
  EXPECTED RESULT:
  Index should be used.
  
  APAR is required to set the default value for maximo property
  "mxe.db.format.timestamp" to "to_date" if the target database is
  Oracle.
  
  
  NOTE, existing Oracle clients can resolve the issue by:
  
  1- Shutdown the maximo enviornment.
  2- Login as MAXIMO schema owner and run the following update at
  the database level:
  
  update maxpropvalue set propvalue = 'to_date' where propname =
  'mxe.db.format.timestamp';
  3- Restart the maximo enviornment.
  
  ENVIRONMENT:
  
  Tivoli's process automation engine 7.6.1.0-IFIX20190910-1703
  Build 20180718-1141 DB Build V7610-83 HFDB Build HF7610-09
  
  Entry Edition Content Best Practices for IBM Control Desk
  7.6.1.0319 Build ICD-PROD-149-201811151250 DB Build V7540-01
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Maximo default settings for Oracle issues a to_timestamp to  *
  * query date fields, which does not allow indexes on date      *
  * columns to be used                                           *
  ****************************************************************
  

Problem conclusion

• Updated maximo property "mxe.db.format.timestamp" to "to_date"
  for Oracle databases
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  MAXIMO SYSTEMS

• Fixed component ID

  5724R46AV

Applicable component levels