Clarity

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?

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

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?

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).

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.

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.

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