IJ21516: THE SQL STATEMENT THAT WORKS IN DB2, SQL SERVER AND ORACLE 12.1 AND OLDER STOPPED WORKING IN ORACLE 12.2 RELEASE.

APAR status

• Closed as program error.

Error description

• This SQL statement does not work in Oracle 12.2:
  select * from sla where objectname = 'SR' and status in (select
  value from synonymdomain where domainid='SLASTATUS' and
  maxvalue='ACTIVE') and (classstructureid is null or
  classstructureid = '' or
  exists (select 1 from classancestor where
  ancestor=sla.classstructureid and
  classancestor.classstructureid= '' )) and (not exists (select 1
  from pluspcustsla where slanum=sla.sanum) or
  exists (select 1 from pluspcustsla where slanum=sla.sanum and
  customer= '' )) and vendor is null and ( sanum not in (select
  ownerid from pluspapplserv
  where ownertable='SLA' and ((commoditygroup != '' or
  commoditygroup is not null) or (commodity != '' or commodity is
  not null ) )) or sanum in (select ownerid from pluspapplserv
  where ownertable='SLA' and ((commoditygroup = '' or
  commoditygroup is null) and (commodity is null or commodity =
  '' )))) order by ranking
  
  This is the fixed version:
  select * from sla where objectname = 'SR' and status in (select
  value from synonymdomain where domainid='SLASTATUS' and
  maxvalue='ACTIVE') and (classstructureid is null or
  classstructureid = '' or
  exists (select 1 from classancestor where (sla.classstructureid
  is null or ancestor=sla.classstructureid) and
  classancestor.classstructureid= '' )) and (not exists (select 1
  from pluspcustsla where slanum=sla.sanum) or
  exists (select 1 from pluspcustsla where slanum=sla.sanum and
  customer= '' )) and vendor is null and ( sanum not in (select
  ownerid from pluspapplserv
  where ownertable='SLA' and ((commoditygroup != '' or
  commoditygroup is not null) or (commodity != '' or commodity is
  not null ) )) or sanum in (select ownerid from pluspapplserv
  where ownertable='SLA' and ((commoditygroup = '' or
  commoditygroup is null) and (commodity is null or commodity =
  '' )))) order by ranking
  
  ancestor=sla.classstructureid was replaced with
  (sla.classstructureid is null or ancestor=sla.classstructureid)
  
  The fix should be made in PlusPSLASET.getWhereClause(). This is
  a new line. Customer confirmed it worked.
  SqlFormat sql = new SqlFormat(" and (classstructureid is null
  or classstructureid = :1 or exists (select 1 from classancestor
  where (sla.classstructureid is null or
  ancestor=sla.classstructureid) and
  
  
  Steps to Reproduce:
  IMPORTANT: MAXIMO DATABASE ON ORACLE 12.2 IS REQUIRED
  
  1. Crete new SLA
  2. Create new Service Request. Do not provide any classification
  3. Apply SLA. There is no error in the log but SLA is not found
  after execution of bad SQL statement above. <== This is the
  issue
  4. Provide classification on SR and SQL will be found.
  
  classancestor.classstructureid=:classstructureid))");
  
  This fix is not Oracle specific, it will work across all
  platforms and all versions.
  
  Results:
  sql not working in Oracle 12.2
  
  Expected Results:
  Needs to be rewritten to work
  
  Product Version:
  Tivoli's process automation engine 7.6.0.8-IFIX20180130-1210
  Build 20170512-0100 DB Build V7608-63 HFDB Build HF7608-12
  IBM Maximo for Service Providers 7.6.0.0 Build 20141125-1930 DB
  Build V7600-05
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Maximo for Service Providers users, withing SLA App.         *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * SQL Statement error not working on oracle 12.2               *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  

Problem conclusion

• Fixed in code.
  

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

  SERVICE PROVIDE

• Fixed component ID

  5724R46SV

Applicable component levels