IV80068: ALLOW THE SETTING OF NLS_DATE_FORMAT FOR THE DATABASE CONNECTIONS.

4.1.1-TIV-ITNMIP-Linux-FP0002
4.1.1_README_NLS_FP0002
4.1.1_README_FP0002
4.2.0-TIV-NOIHD-Linux-FP0001
4.2.0-TIV-ITNMIP-Linux-FP0001
4.2.0_README_FP0001
4.2.0-TIV-NOIHD-AIX-FP0001
4.2.0-TIV-ITNMIP-AIX-FP0001
IBM Tivoli Network Manager IP Edition 4.1.1 Fix Pack 2, 4.1.1-TIV-ITNMIP-FP0002

APAR status

• Closed as program error.

Error description

• - This is occuring on ITNM 4.1.1 FP1 on RHEL 6, 64 Bits.
  This doesn't happen on 3.9 FP4 ITNM system.
  In this case, oracle language settings are configured to use
  german.
  
  -   In ncp_model.DOMAIN.log/trace  there are several occurences
  of the following SQl error:
      ( statement: 'SELECT COUNT(m.ENTITYID) C from managedStatus
        m inner join entity e on e.ENTITYID = m.ENTITYID and
        e.DOMAINMGRID = ? and m.CHANGETIME > ?' varbinds:
        [104,'2015-12-08 09:52:06'] )
  
     2015-12-09T08:01:35: Warning: W-RIV-002-206: [1666807552t]
     CMdlManagedStatus.cc(207) A database 'execute' operation has
     failed : CNcpOracleSth.cc line 381:  (Error 1843) ORA-01843:
     not a valid month
  
  
  
  Running the above SQL statement directly against the database,
  the same error is printed:
  
      SELECT COUNT(m.ENTITYID) C from managedStatus m inner join
  entity e on e.ENTITYID = m.ENTITYID and e.DOMAINMGRID = 104 and
  m.CHANGETIME > '2015-12-08 09:52:06'
  
                =>  ORA-01843: Not a valid month
  
  The system behaves as if it's ignoring Oracle language setting
  and this results in queries not being executed
  
  
  
  - The manual query works fine when a german date format is used:
      SELECT COUNT(m.ENTITYID) C from managedStatus m inner join
      entity e on e.ENTITYID = m.ENTITYID and e.DOMAINMGRID = 104
      and m.CHANGETIME > '08-12-2015 09:52:06';
  
      => 93
  
  
  - When setting both, the timestamp and date format:
          ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD
                                                   HH24:MI:SS'
          ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD
                                               HH24:MI:SS'
  , the manual SQL command
          SELECT COUNT(m.ENTITYID) C from managedStatus m inner
          join entity e on e.ENTITYID = m.ENTITYID and
          e.DOMAINMGRID = 104 and m.CHANGETIME > '2015-12-08
          09:52:06'
  works.
  
  
  ==> So the request here is to change the code to allow setting
  of NLS_DATE_FORMAT for the database connection
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Users attempt to use managed status.                         *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * In ncp_model.<domain>.log/trace  there are several           *
  * occurrences of the following SQl error:                      *
  * ( statement: 'SELECT COUNT(m.ENTITYID) C from managedStatus  *
  * m inner join entity e on e.ENTITYID = m.ENTITYID and         *
  * e.DOMAINMGRID = ? and m.CHANGETIME > ?' varbinds:            *
  * [104,'2015-12-08 09:52:06'] )                                *
  * 2015-12-09T08:01:35: Warning: W-RIV-002-206: [1666807552t]   *
  * CMdlManagedStatus.cc(207) A database 'execute' operation has *
  * failed : CNcpOracleSth.cc line 381:  (Error 1843) ORA-01843: *
  * not a valid month                                            *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * The following fixpacks will contain the fix:                 *
  * | fix pack | 4.1.1-ITNMIP-FP0002                             *
  ****************************************************************
  

Problem conclusion

• Added the setting of NLS_DATE_FORMAT for the DB connections. It
  is in addition to the setting of timestamp format when opening a
  session.
          ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD
  HH24:MI:SS'
          ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD
  HH24:MI:SS'
  
  The consequences of the "not a valid month" error here are
  likely that the managed status will not work as expected because
  ncp_model likely won't pick up any of the changes. So this
  should only be a problem if the customer is attempting to use
  managed status - perhaps by right clicking on devices in the
  GUI.
  

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

• R411 PSN

     UP

• R411 PSY

     UP