IV72287: SAM.COMPUTER_SYSTEMS VIEW RETURNS ERROR "SQL0727N AN ERROR OCCURRED DURING IMPLICIT SYSTEM ACTION TYPE "3"".

APAR status

• Closed as program error.

Error description

• When using the API call
  https://ILMThostname:9081/api/sam/computer_systems, it returns
  a SQL exception as shown below.
  
  
  {"error":"Java::ComIbmDb2JccAm::SqlException: An error occurred
  during implicit system action type \"3\". Information returned
  for the error
  includes SQLCODE \"-104\", SQLSTATE \"42601\" and message tokens
  \"END-OF-STATEMENT|where cs.is_curr8|<interval_qualifier>\"..
  SQLCODE=-727, SQLSTATE=56098, DRIVER=3.64.104"}
  
  
  The above API call is actually running this select statement
  below.
  
  
  SELECT COMPUTER_SYSTEMS.ID,
  COMPUTER_SYSTEMS.PARENT_ID, COMPUTER_SYSTEMS.TYPE,
  COMPUTER_SYSTEMS.OS,
  COMPUTER_SYSTEMS.HOST_NAME, COMPUTER_SYSTEMS.DNS_NAME,
  COMPUTER_SYSTEMS.IP_ADDRESS, COMPUTER_SYSTEMS.LAST_SEEN,
  COMPUTER_SYSTEMS.HARDWARE_MANUFACTURER,
  COMPUTER_SYSTEMS.HARDWARE_MODEL,
  COMPUTER_SYSTEMS.HARDWARE_SERIAL_NUMBER,
  COMPUTER_SYSTEMS.PROCESSOR_TYPE,
  COMPUTER_SYSTEMS.PROCESSOR_BRAND,
  COMPUTER_SYSTEMS.PROCESSOR_VENDOR,
  COMPUTER_SYSTEMS.PROCESSOR_MODEL,
  COMPUTER_SYSTEMS.PARTITION_CORES,
  COMPUTER_SYSTEMS.SERVER_PROCESSORS,
  COMPUTER_SYSTEMS.SERVER_CORES FROM SAM.COMPUTER_SYSTEMS AS
  COMPUTER_SYSTEMS ORDER BY COMPUTER_SYSTEMS.ID ASC"
  
  
  When executing the above statement from DB2, it will return
  the same error.
  
  
  SQL0727N  An error occurred during implicit system action type
  "3".
  Information returned for the error includes SQLCODE "-104",
  SQLSTATE
  "42601"  and message tokens "END-OF-STATEMENT|where
  cs.is_curr8|<interval_qualifier>".
  SQLSTATE=56098
  
  
  Even a simple "db2 "select * from sam.computer_systems", it also
  returns the same error as well.
  
  
  This sam.computer_views is not completed or missing the where
  clause.
  
  ===================
  
  The above problem has been seen in both ILMT v9.2 and SUA v9.2
  releases.
  

Local fix

Problem summary

• ****************************************************************
  ERROR DESCRIPTION:
  When you try to retrieve information about computer systems by
  using the REST API call
  https://server_host_name:port_number/api/sam/computer_systems,
  the following SQL exception is returned:
  An error occurred during implicit system action type \"3\".
  Information returned for the error includes SQLCODE \"-104\",
  SQLSTATE \"42601\" and message tokens \"END-OF-STATEMENT|where
  cs.is_curr8|interval_qualifier\". SQLCODE=-727, SQLSTATE=56098,
  DRIVER=3.64.104"}
  Behavior after you install the update:
  The error does not occur and the information can be retrieved
  by using REST API.
  ****************************************************************
  * RECOMMENDATION: Apply appropriate update when available or
  * refer to the documentation update
  ****************************************************************
  

Problem conclusion

• The fix for this APAR is contained in the following maintenance
  packages:
   | Update | SUA 9.2.0.2 site 51
   | Update | ILMT 9.2.0.2 site 63
  

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

  LIC METRIC TOOL

• Fixed component ID

  5724LMTEM

Applicable component levels

• R920 PSY

     UP