Clarity PPM

Expand all | Collapse all

nls_database_parameters vs nls_instance_parameters vs nls_session_parameters

Jump to Best Answer
  • 1.  nls_database_parameters vs nls_instance_parameters vs nls_session_parameters

    Posted 11-02-2017 03:48 PM

    We have to set the following 5 parameters (NLS_COMP, NLS_DATE_FORMAT, NLS_LANGUAGE, NLS_SORT, NLS_TERRITORY) do the values have to match across the different NLS Parameter sets?

     

    From what I understand, the order of precedence from lower to higher is

       nls_database_parameters

       nls_instance_parameters

       nls_session_parameters

     

    Thus the values in    nls_session_parameters are the values Clarity uses.  IF that is the case, does it matter what values the other sets have?

     



  • 2.  Re: nls_database_parameters vs nls_instance_parameters vs nls_session_parameters
    Best Answer

    Posted 11-02-2017 04:27 PM

    Hi Gabriel,

     

     

    nls_database_parameters will be on the entire database server and they can be set to be different from the CA PPM recommendations, whenever applicable. Some of them cannot be changed anymore once the database was created.  

     

    CA PPM will use the nls_instance_parameters, which are the parameters set for the database schema used for CA PPM. If some of the parameters are not specified they may be inherited from default, database parameters. We do recommend all of them to be set explicitly to avoid any conflicts.

     

    nls_session_parameters will be the parameters shown by the query when querying the view, i.e. if you get the results in Toad, it will show you the Toad setting. .  

     

    So based on the above, we recommend all the parameters for the instance (nls_instance_parameters)  to be set accordingly to the Installation guide:

    https://docops.ca.com/ca-ppm/15-3/en/installing-and-upgrading/install-ca-ppm

    You can also use the Health report in CSA to see how things look - it will display in green the correct parameters and in red when they should be changed to match the requirements.

     

    I hope this helps -Nika



  • 3.  Re: nls_database_parameters vs nls_instance_parameters vs nls_session_parameters

    Posted 11-02-2017 04:42 PM

    Nika,
    Thank you for a great explanation.

    One case I currently have is that while the nls_instance_parameters values are correct, the NLS_SORT in the nls_session_parameters is incorrect.

    I used the NSQL Nick provided in a previous post to see the values that Clarity see in its session.

     

    Parameter          nls_instance_parameters              nls_session_parameters
    =================  =========================            =======================
    NLS_COMP           BINARY                               BINARY
    NLS_DATE_FORMAT    YYYY-MM-DD HH24:MI:SS YYYY-MM-DD     HH24:MI:SS
    NLS_LANGUAGE       AMERICAN                             AMERICAN
    NLS_SORT           BINARY                               XWEST_EUROPEAN
    NLS_TERRITORY      AMERICA                              AMERICA

     

     

    Should this be corrected or is it OK?



  • 4.  Re: nls_database_parameters vs nls_instance_parameters vs nls_session_parameters

    Posted 11-02-2017 05:10 PM

    The session parameters are (or can be) governed and overwritten by the "client" (whether that is a query tool like Toad or SQL*Plus, or even the PPM app/bg services).

     

    So I wouldn't put much stock into those values normally.

     

    You're also correct that the values will 'flow' from database -> instance -> session unless overridden further down the chain.

     

    However the scope at which a parameter is needed and used can vary and depends on when/how it's being called.  That can mean there are times it wouldn't be enough to just set things at the instance level when it is also expected at the database level, and so the parameter scopes we document are set accordingly.

     

    Examples that can impact when/how a parameter comes into play include:

     

    1) It just being called as a query from a client

     

    2) In the context of PPM, whether a user's locale/language settings would override some settings at the 'session' level, but some things that do not run in a user's context will depend on some consistency of setting being in place at the instance level (notably, nls_date_format).

     

    3) Things that impact how and where they are stored physically, and the impact of optimally fetching and using that information, can trickle down to the db level settings (notably, sort and comp, if I am recalling correctly, and they are done in order to get the best scenario for the data we store and the order in which we typically want to get it).



  • 5.  Re: nls_database_parameters vs nls_instance_parameters vs nls_session_parameters

    Posted 11-02-2017 05:12 PM

    The short answer:

    Those session parameters are probably fine, but if you got them from inside PPM itself and not another tool, then the session date_format one is a bit strange looking.  The sort setting helps for example to determine if 'a' comes before/after 'A' or even 'â', 'ã', and 'ä', etc.

     

    Not all countries/languages agree on the answer, and so the setting will vary according to your preferences.



  • 6.  Re: nls_database_parameters vs nls_instance_parameters vs nls_session_parameters

    Posted 11-03-2017 09:05 AM

    Hi Nick,

     

    The date format looks weird because I messed up in putting the data in the correct column for the posting.  Both values are actually the same, it is just that the date portion that was supposed to be in the session column was left in the instance column.



  • 7.  Re: nls_database_parameters vs nls_instance_parameters vs nls_session_parameters

    Posted 11-03-2017 11:08 AM

    Ah, yes I see that now.  All looks good then.