IZ85900: BMXAA4210E - DATABASE ERROR NUMBER -104 HAS OCCURED

APAR status

• Closed as program error.

Error description

• ENVIRONMENT:
  
  IBM WebSphere Application Server 6.1
  Service Desk Integration MEA 7.2.0.00
  SRM Service Request Management 7.2.0.0
  IBM Tivoli Configuration Management  7.2.0.0
  IBM Tivoli Change Management Content 7.2.0.0
  IMBot SRM Service Desk 7.2.0.00
  IBM Tivoli Integration Composer 7.2.0.0
  SRM Service Desk Content - Classification 7.2.0.0
  SRM Service Desk Content - Best Practices 7.2.0.0
  IBM Tivoli Common Process Components 7.2.0.00
  SRM Service Desk 7.2.0.0
  SRM Screen Capturer 7.2.0.0
  SRM Problem Management 7.2.0.0
  SRM Solution 7.2.0.0
  SRM Incident Management 7.2.0.0
  SRM Service Catalog Base 7.2.0.0
  SRM SLA Hold 7.2.0.0
  CI PMP Content 7.2.0.0
  SRM Search 7.2.0.0
  SRM Survey Management 7.2.0.0
  TPAE 7.1.1.5
  IBM Tivoli UI Widgets 7.2.0.00
  IBM Tivoli Change Management 7.2.0.0
  Service Provider 7.1.1.1
  CCI PMP 7.2.0.00
  SRM Service Catalog Content 7.2.0.0
  Base Services 7.1.1.5
  Windows Server 1008 6.0
  
  
  PROBLEM
  ======
  User made a custom query in order to filter all the Incidents
  that are
  
  owned by a person and were previously owned by a defined group.
  In the
  example below the groupID is 3LAPPL.
  
  ((owner is not null and '3LAPPL' in (select
  tkownerhistory.ownergroup
  from tkownerhistory where
  incident.ticketid=tkownerhistory.ticketid and
  tkownerhistory.ownergroup is not null order by
  tkownerhistory.owndate
  desc fetch first 1 rows only)) or ownergroup='3LAPPL')
  
  The query works fine but if they change the group id with
  another one like
  3LSAPTECH (which is also a valid group in my environment).
  
  ((owner is not null and '3LSAPTEC' in (select
  tkownerhistory.ownergroup
  from tkownerhistory where
  incident.ticketid=tkownerhistory.ticketid and
  tkownerhistory.ownergroup is not null order by
  tkownerhistory.owndate
  desc fetch first 1 rows only)) or ownergroup='3LSAPTEC')
  
  Customer get the following error:
  <screenshot showing...
  BMXAA4210E - Database error number -104 occurred
  An unexpected token "for" was found following "nergroup is not
  null"
  Expected tokens may include ")" SQLCODE = -104 , SQLSTATE =
  42601 Driver 3.53.70.
  
  As you can see is complaining about a ?for? found in a strange
  place,
  but my query doesn?t contain a ?for? statement at all! I can say
  that
  the query is working for about half of my groups while the other
  half
  comes this this error message.
  
  The strange part is that the error is not written to the log
  file as
  expected and I can only navigate away from the error message if
  user close
  the browser or navigate to another website outside Maximo
  application.
  
  User wrote the statement in db2command and it fetches the
  records without any problems.
  
  ----------------------------------------------------------------
  
  
  L2 Analysis - L2 Support have been able to reproduce the same
  behaviour but by modifying the query to demo data on BS 7117
  with DB2.
  
  Steps to reproduce
  
  1. Go To -> Service Desk -> Incident
  2. In Incident application. Advanced Search -> Where Clause
  3. Fill in following query :-
  (owner is not null and ticketid in (select g.ticketid from
  tkownerhistory g left join incident i on g.ticketid=i.ticketid
  and
  g.ownergroup = 'HARDWARE' order by g.owndate desc fetch first 1
  rows
  only)) or ownergroup in
  ('ERP','EMAIL','NETWORK','TIER2','HARDWARE')
  
  * No error and Incident is listed.
  
  4. Advanced Search -> Where Clause
  5. Fill in following query :-
  (owner is not null and ticketid in (select g.ticketid from
  tkownerhistory g left join incident i on g.ticketid=i.ticketid
  and
  g.ownergroup = 'HARDWARE' order by g.owndate desc fetch first 1
  rows
  only)) or ownergroup in
  ('FACILITY','ERP','EMAIL','NETWORK','TIER2','HARDWARE')
  
  The following error message is displayed:
  
  BMXAA4210E - Database error number -104 has occured. An
  unexpected token
  "for" was found following "nergroup = 'HARDWARE'". Expected
  tokens may
  include: ")".. SQLCODE=-104, SQLSTATE=42601,DRIVER=3.53.70
  
  
  This behaviour was reproduced in Maximo QA env 7117+DB2
  
  http://qalab01.swg.usma.ibm.com:9997/maximo/webclient/login/logi
  n.jsp?welcome=true
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: db2 only, query with where clause that has   *
  *                 "order by" clause which is not at the end of *
  *                 where clause                                 *
  ****************************************************************
  * PROBLEM DESCRIPTION: ENVIRONMENT:                            *
  *                                                              *
  *                      IBM WebSphere Application Server 6.1    *
  *                                                              *
  *                      Service Desk Integration MEA 7.2.0.00   *
  *                                                              *
  *                      SRM Service Request Management 7.2.0.0  *
  *                                                              *
  *                      IBM Tivoli Configuration Management     *
  *                      7.2.0.0                                 *
  *                                                              *
  *                      IBM Tivoli Change Management Content    *
  *                      7.2.0.0                                 *
  *                                                              *
  *                      IMBot SRM Service Desk 7.2.0.00         *
  *                                                              *
  *                      IBM Tivoli Integration Composer 7.2.0.0 *
  *                                                              *
  *                      SRM Service Desk Content -              *
  *                      Classification 7.2.0.0                  *
  *                                                              *
  *                      SRM Service Desk Content - Best         *
  *                      Practices 7.2.0.0                       *
  *                                                              *
  *                      IBM Tivoli Common Process Components    *
  *                      7.2.0.00                                *
  *                                                              *
  *                      SRM Service Desk 7.2.0.0                *
  *                                                              *
  *                      SRM Screen Capturer 7.2.0.0             *
  *                                                              *
  *                      SRM Problem Management 7.2.0.0          *
  *                                                              *
  *                      SRM Solution 7.2.0.0                    *
  *                                                              *
  *                      SRM Incident Management 7.2.0.0         *
  *                                                              *
  *                      SRM Service Catalog Base 7.2.0.0        *
  *                                                              *
  *                      SRM SLA Hold 7.2.0.0                    *
  *                                                              *
  *                      CI PMP Content 7.2.0.0                  *
  *                                                              *
  *                      SRM Search 7.2.0.0                      *
  *                                                              *
  *                      SRM Survey Management 7.2.0.0           *
  *                                                              *
  *                      TPAE 7.1.1.5                            *
  *                                                              *
  *                      IBM Tivoli UI Widgets 7.2.0.00          *
  *                                                              *
  *                      IBM Tivoli Change Management 7.2.0.0    *
  *                                                              *
  *                      Service Provider 7.1.1.1                *
  *                                                              *
  *                      CCI PMP 7.2.0.00                        *
  *                                                              *
  *                      SRM Service Catalog Content 7.2.0.0     *
  *                                                              *
  *                      Base Services 7.1.1.5                   *
  *                                                              *
  *                      Windows Server 1008 6.0                 *
  *                                                              *
  *                      PROBLEM                                 *
  *                                                              *
  *                      ======                                  *
  *                                                              *
  *                      User made a custom query in order to    *
  *                      filter all the Incidents                *
  *                                                              *
  *                      that are                                *
  *                                                              *
  *                      owned by a person and were previously   *
  *                      owned by a defined group.               *
  *                                                              *
  *                      In the                                  *
  *                                                              *
  *                      example below the groupID is 3LAPPL.    *
  *                                                              *
  *                      ((owner is not null and '3LAPPL' in     *
  *                      (select                                 *
  *                                                              *
  *                      tkownerhistory.ownergroup               *
  *                                                              *
  *                      from tkownerhistory where               *
  *                                                              *
  *                      incident.ticketid=tkownerhistory.ticket *
  *                      id and                                  *
  *                                                              *
  *                      tkownerhistory.ownergroup is not null   *
  *                      order by                                *
  *                                                              *
  *                      tkownerhistory.owndate                  *
  *                                                              *
  *                      desc fetch first 1 rows only)) or       *
  *                      ownergroup='3LAPPL')                    *
  *                                                              *
  *                      The query works fine but if they change *
  *                      the group id with                       *
  *                                                              *
  *                      another one like                        *
  *                                                              *
  *                      3LSAPTECH (which is also a valid group  *
  *                      in my environment).                     *
  *                                                              *
  *                      ((owner is not null and '3LSAPTEC' in   *
  *                      (select                                 *
  *                                                              *
  *                      tkownerhistory.ownergroup               *
  *                                                              *
  *                      from tkownerhistory where               *
  *                                                              *
  *                      incident.ticketid=tkownerhistory.ticket *
  *                      id and                                  *
  *                                                              *
  *                      tkownerhistory.ownergroup is not null   *
  *                      order by                                *
  *                                                              *
  *                      tkownerhistory.owndate                  *
  *                                                              *
  *                      desc fetch first 1 rows only)) or       *
  *                      ownergroup='3LSAPTEC')                  *
  *                                                              *
  *                      Customer get the following error:       *
  *                                                              *
  *                      <screenshot showing...                  *
  *                                                              *
  *                      BMXAA4210E - Database error number -104 *
  *                      occurred                                *
  *                                                              *
  *                      An unexpected token "for" was found     *
  *                      following "nergroup is not              *
  *                                                              *
  *                      null"                                   *
  *                                                              *
  *                      Expected tokens may include ")" SQLCODE *
  *                      = -104 , SQLSTATE =                     *
  *                                                              *
  *                      42601 Driver 3.53.70.                   *
  *                                                              *
  *                      As you can see is complaining about a   *
  *                      ?for? found in a strange                *
  *                                                              *
  *                      place,                                  *
  *                                                              *
  *                      but my query doesn?t contain a ?for?    *
  *                      statement at all! I can say             *
  *                                                              *
  *                      that                                    *
  *                                                              *
  *                      the query is working for about half of  *
  *                      my groups while the other               *
  *                                                              *
  *                      half                                    *
  *                                                              *
  *                      comes this this error message.          *
  *                                                              *
  *                      The strange part is that the error is   *
  *                      not written to the log                  *
  *                                                              *
  *                      file as                                 *
  *                                                              *
  *                      expected and I can only navigate away   *
  *                      from the error message if               *
  *                                                              *
  *                      user close                              *
  *                                                              *
  *                      the browser or navigate to another      *
  *                      website outside Maximo                  *
  *                                                              *
  *                      application.                            *
  *                                                              *
  *                      User wrote the statement in db2command  *
  *                      and it fetches the                      *
  *                                                              *
  *                      records without any problems.           *
  *                                                              *
  *                      --------------------------------------- *
  *                      -------------------------               *
  *                                                              *
  *                      L2 Analysis - L2 Support have been able *
  *                      to reproduce the same                   *
  *                                                              *
  *                      behaviour but by modifying the query to *
  *                      demo data on BS 7117                    *
  *                                                              *
  *                      with DB2.                               *
  *                                                              *
  *                      Steps to reproduce                      *
  *                                                              *
  *                      1. Go To -> Service Desk -> Incident    *
  *                                                              *
  *                      2. In Incident application. Advanced    *
  *                      Search -> Where Clause                  *
  *                                                              *
  *                      3. Fill in following query :-           *
  *                                                              *
  *                      (owner is not null and ticketid in      *
  *                      (select g.ticketid from                 *
  *                                                              *
  *                      tkownerhistory g left join incident i   *
  *                      on g.ticketid=i.ticketid                *
  *                                                              *
  *                      and                                     *
  *                                                              *
  *                      g.ownergroup = 'HARDWARE' order by      *
  *                      g.owndate desc fetch first 1            *
  *                                                              *
  *                      rows                                    *
  *                                                              *
  *                      only)) or ownergroup in                 *
  *                                                              *
  *                      ('ERP','EMAIL','NETWORK','TIER2','HARDW *
  *                      ARE')                                   *
  *                                                              *
  *                      * No error and Incident is listed.      *
  *                                                              *
  *                      4. Advanced Search -> Where Clause      *
  *                                                              *
  *                      5. Fill in following query :-           *
  *                                                              *
  *                      (owner is not null and ticketid in      *
  *                      (select g.ticketid from                 *
  *                                                              *
  *                      tkownerhistory g left join incident i   *
  *                      on g.ticketid=i.ticketid                *
  *                                                              *
  *                      and                                     *
  *                                                              *
  *                      g.ownergroup = 'HARDWARE' order by      *
  *                      g.owndate desc fetch first 1            *
  *                                                              *
  *                      rows                                    *
  *                                                              *
  *                      only)) or ownergroup in                 *
  *                                                              *
  *                      ('FACILITY','ERP','EMAIL','NETWORK','TI *
  *                      ER2','HARDWARE')                        *
  *                                                              *
  *                      The following error message is          *
  *                      displayed:                              *
  *                                                              *
  *                      BMXAA4210E - Database error number -104 *
  *                      has occured. An                         *
  *                                                              *
  *                      unexpected token                        *
  *                                                              *
  *                      "for" was found following "nergroup =   *
  *                      'HARDWARE'". Expected                   *
  *                                                              *
  *                      tokens may                              *
  *                                                              *
  *                      include: ")".. SQLCODE=-104,            *
  *                      SQLSTATE=42601,DRIVER=3.53.70           *
  *                                                              *
  *                      This behaviour was reproduced in Maximo *
  *                      QA env 7117+DB2                         *
  *                                                              *
  *                      http://qalab01.swg.usma.ibm.com:9997/ma *
  *                      ximo/webclient/login/logi               *
  *                                                              *
  *                      n.jsp?welcome=true                      *
  *                                                              *
  *                      LOCAL FIX:                              *
  *                                                              *
  *                      N/A                                     *
  *                                                              *
  ****************************************************************
  * RECOMMENDATION:                                              *
  *                                                              *
  *                                                              *
  *                                                              *
  ****************************************************************
  BMXAA4210E - DATABASE ERROR NUMBER -104 HAS OCCURED
  

Problem conclusion

• This is a bug.  If the search where clause has "order by", we
  need to strip out the "order by" clause for getting the count
  as DB2 doesn't like it.  But the existing code throws out
  everything in the where clause after the "order by" text.  The
  fix is to only strip out the part "order by *** desc" or "order
  by *** asc".
  
  
  
  
  
  The fix for this APAR is contained in the following maintenance
  package:
  
    | release\fix pack | REL 7.1.1.9 - BS
  

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:

Modules/Macros

• MAXIMO
  

Fix information

• Fixed component name

  SELF SERVICE

• Fixed component ID

  5724R46SS

Applicable component levels

• R711 PSY

     UP