IZ12851: HISTORY REQUEST TO ORACLE WAREHOUSE STOPS RETURNING DATA

APAR status

• Closed as program error.

Error description

• Environment:
  ITM 6.2 TEPS, Linux
  ITM 6.2 TDW, Oracle running on Solaris
  
  Problem Description:
  Long term historical requests to an Oracle warehouse running on
  Solaris
  works for a while, then after a certain period of time, the same
  requests fail to return data.  Only the last 24 hours of
  historical data
  is visible.
  
  Detailed Recreation Procedure:
  - configure Oracle warehouse
  - configure TEPS to connect to warehouse using JDBC
  - make a request for long term historical data
  - wait at least 2 hours before making another long term history
  request
  - after over 2 hours, make another long term history request
  
  Related Files and Output:
  TEPS trace: ERROR (UNIT:ctsql IN ER) (UNIT:JDBC ALL)
  (UNIT:QueryImpl ALL) (UNIT:EvaluatorImpl ALL) (UNIT:ctdatabus IN
  ER)
  Approver:RP
  

Local fix

• Possible short term fix : Set the following environment variable
  in cq.ini:
  
  KFW_JDBC_POOL_SIZE=-1
  

Problem summary

• Long term historical requests to an Oracle warehouse running on
  Solaris works for a while, then after a certain period of time,
  the same
  requests fail to return data.  Only the last 24 hours of
  historical data
  is visible.
  

Problem conclusion

• The Solaris operating system has a security feature which
  closes ports if no traffic is seen on the port for a certain
  period of time.  When no historical request is made to an
  Oracle warehouse for this length of time, the port that was
  opened to the warehouse is subject to being closed, and the
  TEPS is effectively disconnected from the warehouse database.
  Connections to an Oracle warehouse must be done using JDBC,
  and the JDBC SQL evaulator does not have reconnection
  logic similar to that in the ODBC evaluator.
  The fix for this APAR is included in the following maintenance
  vehicle:
  
     | interim fix | 6.1.0.7-TIV-ITM-IF0003
  

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

  TEPS

• Fixed component ID

  5724C04PS

Applicable component levels

• R610 PSY

     UP