IV45467: TOPOVIZ CREATES ILLEGAL SQL QUERY WHEN USING ADVANCED SQL FILTER

IBM Tivoli Network Manager IP 3.9.0 Fix Pack 4, 3.9.0-TIV-ITNMIP-FP0004
IBM Tivoli Network Manager IP Edition 3.9.0 Fix Pack 5, 3.9.0-TIV-ITNMIP-FP0005

APAR status

• Closed as program error.

Error description

• topoviz creates illegal sql query when using advanced sql filter
  
  When creating a network view with an advanced sql filter in
  topoviz the
  backend creates an illegal sql query which doesn't work in
  mysql.
  Caused by: java.lang.RuntimeException: Error executing query:
  SELECT
  e.entityId FROM ncim.customChassis t INNER JOIN ncim.entityData
  e ON e.
  entityId = t.entityId INNER JOIN ncim.domainMembers dm ON
  dm.entit
  yId = e.entityId WHERE dm.domainMgrId = 6 AND (t.(DEVICE_TYPE
  NOT IN
  ('CPE') AND NETWORK_NAME = 'BS620 BS01-02') or (NETWORK_NAME =
  'BS620
  BS01' AND DEVICE_TYPE IN ('DS')))
          at
  com.micromuse.precision.common.dblayer.NcimDatabaseDAOImpl.
  getEntityIdSetFromFilter(NcimDatabaseDAOImpl.java:2616)
          at
  com.micromuse.topoviz.server.dblayer.ViewPlugInFiltered.
  getEntityIdsForView(ViewPlugInFiltered.java:203)
          at
  com.micromuse.topoviz.server.dblayer.NetworkViewDAOImpl.
  buildCache(NetworkViewDAOImpl.java:3204)
          at
  com.micromuse.topoviz.server.dblayer.NetworkViewDAOImpl.
  cacheView(NetworkViewDAOImpl.java:3142)
          ... 36 more
  Caused by: java.sql.SQLException: You have an error in your SQL
  syntax;
  check the manual that corresponds to your MySQL server version
  for the
  right syntax to use near '(DEVICE_TYPE NOT IN ('CPE') AND
  NETWORK
  _NAME = 'BS620 BS01-02') or (NETWORK_NAME' at line 1
          at
  com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2921)
          at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1570)
          at
  com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
          at
  com.mysql.jdbc.Connection.execSQL(Connection.java:2972)
          at
  com.mysql.jdbc.Connection.execSQL(Connection.java:2902)
          at
  com.mysql.jdbc.Statement.executeQuery(Statement.java:822)
          at
  com.micromuse.precision.common.dblayer.NcimDatabaseDAOImpl.
  getEntityIdSetFromFilter(NcimDatabaseDAOImpl.java:2599)
          ... 39 more
  
  
  The advanced query is surrounded by t.(...) which isn't
  supported in
  mysql.
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * ITNM 3.9 users                                               *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * The query used in network filtered view was prefixed by      *
  * "t.", to avoid column name conflicts, but didn't work for    *
  * all cases.                                                   *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * | fix pack | 3.9.0-ITNMIP-FP0004                             *
  ****************************************************************
  

Problem conclusion

• Alias is added at runtime to basic filters only; for advanced
  filter, user needs to manually prefix column names with "t.".
  Basic filters are the ones that can be opened in FilterBuilder
  basic tab.
  

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

  TIV NETWK MGR I

• Fixed component ID

  5724S4500

Applicable component levels

• R390 PSN

     UP

• R390 PSY

     UP

• R401 PSN

     UP

• R401 PSY

     UP