Configuration Framework Improvements as of SAP HANA 2 SP04
When operating the SAP HANA system, configuration changes are required for various reasons such as performance or stability improvement. When changing these configuration parameters, the question may arise if the parameter(s) in question could be changed online or if a restart of SAP HANA is required so that the parameter change will take effect.
SAP HANA 2 SP04 introduces two new views “CONFIGURATION_PARAMETER_PROPERTIES” and and “M_CONFIGURATION_PARAMETER_VALUES “ to check if a certain parameter change requires a restart. Additionally, you can see for some of these parameters even more details like a description, the default value, unit of the parameter value or recommended value ranges. Here you can find the content of the table CONFIGURATION_PARAMETER_PROPERTIES (taken from: SAP HANA Configuration Parameter Reference)
You could check this information by executing the following statement on SYSTEMDB or TenantDB to get an overview e.g.: SELECT INIFILE_NAMES, SECTION, KEY, DESCRIPTION, DEFAULT_VALUE, UNIT, VALUE_RESTRICTIONS, RESTART_REQUIRED FROM CONFIGURATION_PARAMETER_PROPERTIES
In case you see entries like this: <service>.ini, >, it means the value is taken from the service specific ini-file. E.g. nameserver reads nameserver.ini, indexserver indexserver.ini. Of course, you can check that also only for a single parameter mentioned in the column “KEY” e.g.: SELECT INIFILE_NAMES, SECTION, KEY, DESCRIPTION, DEFAULT_VALUE, UNIT, VALUE_RESTRICTIONS, RESTART_REQUIRED FROM CONFIGURATION_PARAMETER_PROPERTIES where KEY=’takeover_retries’
If you are interested in which ini-file and section the parameter is used, you could check e.g. in SAP HANA Cockpit by searching for the parameter. Alternatively you could also use the second view M_CONFIGURATION_PARAMETER_VALUES, which provides also a lot of valuable information:
|PORT||Port number of the service|
|SECTION||Section of parameter|
|HAS_KEY_INDEX||Whether the parameter is an indexed parameter|
|LAYER_NAME||The layer the parameter is defined on|
|VALUE||The effective value of the parameter|
|HAS_PROPERTIES||If the parameter has properties defined in CONFIGURATION_PARAMETER_PROPERTIES|
|RAW_VALUE||The value found in the ini-file|
|VIOLATED_RESTRICTIONS||Indicates if predefined restrictions have been violated|
|RESTART_REQUIRED||Whether a restart of the service is required for the value to take effect|
With help of the view: M_CONFIGURATION_PARAMETER_VALUES, it is also possible to identify the already changed parameters which still need a restart to take effect. When searching for table entries where the restart flag RESTART_REQUIRED is set to true: SELECT * from M_CONFIGURATION_PARAMETER_VALUES where RESTART_REQUIRED =’TRUE’ You will get a list of the changed parameter which still require a restart:
In case you see an entry in the field “VIOLATED_RESTRICTIONS”, it means that the parameter was set to an unsupported value. Here you see that the Parameter was set on the wrong layer (e.g. SYSTEM instead of DATABASE) and you can correct that before the planned restart. If you search again after the correction and restart, the list is cleared and no restart is required anymore:
New Alerts available
There is also an alert available which gives a reminder that a restart is pending. If a parameter was changed which requires a restart, but this has not happened until now there will be an alert shown:
This check runs automatically once a day to limit the number of alerts. But if you want to run the check immediately you can do it as well. For that you can search for the specific alert and click on “Check Now” like shown in the screen shot.
This check will run for a few seconds and you will get the result listed in the list of the current alerts. Since it is defined as a priority low alert, the alerts have to be selected accordingly. By default you see only very-high, high and medium alerts. In addition there is also an alert available if you set a parameter to an unsupported value.
Also this alert is running on a daily basis, but can be executed manually like shown before using the “Check Now” button:
In case you want to check with a SQL if a parameter is set to an unsupported value, (here as an example for the parameter: client_distribution_mode), you could run: SELECT * from M_CONFIGURATION_PARAMETER_VALUES where VIOLATED_RESTRICTIONS = ’VALUE_RESTRICTION’
Here you can see if there is a value restriction recognized in column “VIOLATED_RESTRICTIONS”, the parameter name in column “KEY” and the value in the ini-file in column “RAW_VALUE” which is set to “on”. If you want to know more details about the expected parameter values and why you get the value restriction flag, you could check with help of the SQL for the mentioned parameter name: SELECT KEY, RESTART_REQUIRED, INIFILE_NAMES, VALUE_RESTRICTIONS from CONFIGURATION_PARAMETER_PROPERTIES where KEY =’client_distribution_mode’
Here you see in column “VALUE_RESTRICTIONS” the possible values which could be: off, connection, statement or all – but “on” is not allowed for that parameter and therefore you will see the following alert:
In case you need to change SAP HANA configuration parameter(s), you have now the possibility to check beforehand of the change, if a restart is required and if your intended value is supported with help of the new view CONFIGURATION_PARAMETER_PROPERTIES. In case you have already changed parameter(s) and want to check if a restart is necessary, or if a parameter value is set to an unsupported value, you can use another new view M_CONFIGURATION_PARAMETER_VALUES. Here you can see also the name and location of those parameters. A second possibility is to check for the new predefined alerts which reminds you that a restart is still required or if a parameter was recognized to be set to an unsupported value.