Introduction
SAP HANA is constantly collecting various health and performance statistics that can be viewed by database administrators (DBAs) to ensure the system is operating at optimal capacity. Situations may arise where SAP HANA encounters problems and that will typically trigger an alert to notify DBAs of pending or potential issues. By analyzing the patterns of alerts in the past, the DBA can develop insights into the behavior of their systems, for instance, to learn how system configuration changes may be affecting performance. This document describes how to enable Historical Alerts on your SAP HANA systems using SAP HANA DB Control Center (DCC).
Requirements
- An SAP HANA system with DCC, both SPS11 or higher
- SAP HANA Studio
Steps
We will be using DCC and SAP HANA Studio in order to accomplish our task. DCC collects both system health data (as viewed in the Enterprise Health Monitor), and HANA alert data (as viewed in the Alert Monitor). We will enable historical system health data using the SAP DCC Setup wizard, then complete our setup by enabling and configuring Historical Alerts collection in SAP HANA Studio. Finally, we will learn how to undo the changes made to our system.
- Preparing Your System
- Enable Historical Alerts in DCC
- Complete Setup of Historical Alerts in SAP HANA Studio
- Enable Historical Alerts on Registered Systems
- Turn Off Historical Alerts and Undo Changes
Preparing Your System
For a number of steps in this tutorial, we will be interacting with the tables inside the catalog of your HANA system. To access the necessary tables, log in to your system in HANA Studio, then in the Systems view expand your system along Catalog > SAP_HANA_DBCC > Tables, as per the screenshot below. The tables we will be using are named Alerts.HistoryConfig, Alerts.HistoryData, and Site.PreferenceValues. To see the contents of these tables, right click on each table and select “Open Content” from the context menu. At this time, you should also open a SQL Console for your system by right clicking on the System name in the Systems view, and selecting “Open SQL Console” from the context menu.
Enable Historical Alerts in DCC
1. Navigate to your DCC System's Launchpad, using the URL format below, and provide credentials at the login prompt. Ensure that your user has the sap.hana.dbcc.roles::DBCCConfig role (without this role, the user cannot use the DCC Setup app).
http://<host_name>:<port>/sap/hana/dbcc/
2. Click on the SAP DCC Setup tile. You should see a list of DCC settings as the default tab.
Info: By Default,Data History should be Disabled and History in days should be 30 days. If Data History is Enabled on your DCC System, skip Step 3.
3. Click the Edit button in the bottom-right corner. Check the Enabled checkbox and choose a Length of history (Default 30 days). Click the Save button.
At this point, we have enabled the collection of historical system health data for the systems registered in DCC. We will confirm that this operation was successful in the next two steps.
4. In SAP HANA Studio, open/refresh the Site.PreferenceValues table. If the previous steps were performed correctly, you will notice an entry under name “apca.historical.enabled” with v_int value of 1, and under name “apca.historical.purge.max_age” with v_int value of 43,200 (in minutes, equivalent to 30 days).
Note: If the Length of history in DCC was never changed from the default value, the “apca.historical.purge.max_age” may not be present. In this case SAP HANA will use the default value of 30 days. To generate this record, either adjust the Length of history in DCC (You may set it back to default and the record will remain) or execute the following SQL statement:
upsert "SAP_HANA_DBCC"."sap.hana.dbcc.data::Site.PreferenceValues" ("name", "v_int")
values ('apca.historical.purge.max_age', 43200) with primary key;
5. (Optional) As a check to ensure the previous steps were performed correctly, you can execute the following SQL statement to ensure system health data (Availability/Capacity/Performance) is being collected correctly. The result should be historical system health data populated to the current minute, as shown below:
select top 1000 * from "SAP_HANA_DBCC"."sap.hana.dbcc.data::APCA.Historical" order by "timestamp" desc;
Complete Setup of Historical Alerts in SAP HANA Studio
Now that Historical system health data has been enabled, we must enable the Historical Alert data. To prepare for this step, open a SQL console for your system, as shown in the Preparing Your System step.
1. Generate two new records in the Site.PreferenceValues table. These records will enable the collection of Historical Alerts data. To do this, execute the following SQL Statements:
upsert "SAP_HANA_DBCC"."sap.hana.dbcc.data::Site.PreferenceValues" ("name", "v_int")
values ('alert.historical.enabled', 1) with primary key;
upsert "SAP_HANA_DBCC"."sap.hana.dbcc.data::Site.PreferenceValues" ("name", "v_int")
values ('alert.historical.purge.max_age', 43200) with primary key;
Info: The “alert.historical.enabled” record acts as a master toggle switch for Historical Alert collections. When it is set to 1, DCC allows Historical Alert Collections to occur. As we will see later in this document, Historical Alerts can still be turned off for an individual system registered in DCC. However, if the “alert.historical.enabled” record is set to 0, no Historical Alert collection will occur, whether or not it is enabled on each system.
Info: The “alert.historical.purge.max_age” acts as a global purge age default. As we will see later in this document, the purge age can be overridden for an individual system registered in DCC.
2. (Optional) In order to check the values in the Site.PreferenceValues table have been entered correctly, execute the following SQL Statement:
select top 1000 * from "SAP_HANA_DBCC"."sap.hana.dbcc.data::Site.PreferenceValues";
Enable Historical Alerts on Registered Systems
At this point, we have configured DCC to allow Historical Alert collection to occur. Now, all that is left to do is configure the individual systems registered in DCC to allow or block Historical Alert collection, according to our landscape requirements. For the purposes of this document, we will enable Historical Alert Collection for all but one registered system, and we will override the purge age for another system.
For this step, we will need to open the content of the Alerts.HistoryConfig table. This table contains one record for each registered system, with a variety of associated alert collection parameters.
1. You can identify each system by its historyUrl, which contains the system host name. For any systems that require additional configuration, it is recommended that you note the resourceId value to avoid using the long historyUrl in your SQL statements. In our example, we will be disabling alerts for the system with resourceId = 132, and overriding the purge age for the system with resourceId = 140.
2. To enable Historical Alerts for all registered systems, execute the following SQL Statement:
update "SAP_HANA_DBCC"."sap.hana.dbcc.data::Alerts.HistoryConfig"
set "isEnabled" = 1;
3. After opening/refreshing the Alerts.HistoryConfig table, we notice that the isEnabled field is set for each system. If you wish to have Historical Alert data collected for all systems, and you do not require any additional system-specific configuration, you may skip to Step 6.
4. To disable Historical Alert collection for the “mdc-tn2” system only (with resourceId = 132)¸ we will run the following SQL Statement:
update "SAP_HANA_DBCC"."sap.hana.dbcc.data::Alerts.HistoryConfig"
set "isEnabled" = 0 where "resourceId" = 132;
5. To set the purge age for the “dewdflhana2314” system only (with resourceId = 140) to 60 days (86400 minutes), we will run the following SQL Statement:
update "SAP_HANA_DBCC"."sap.hana.dbcc.data::Alerts.HistoryConfig"
set "maxAge" = 86400 where "resourceId" = 140;
6. Finally, refresh the Alerts.HistoryData table (you may need to order by “collectTimestamp” DESC). You should now see the Historical Alerts data populated this table, for the systems on which you have enabled Historical Alert collection.
Turn off Historical Alerts and Undo Changes
The simplest way to turn off Historical Alerts collection is to navigate back to the SAP DCC Setup tile in SAP DB Control Center, and uncheck the Enabled box. This action will set “v_int” = 0 in the “apca.historical.enabled” record of the Site.PreferenceValues table, causing DCC to stop collecting Historical System Health data. However, if you wish to undo the changes made in this document, you can execute the following SQL Statements:
delete from "SAP_HANA_DBCC"."sap.hana.dbcc.data::Site.PreferenceValues"
where "name" = 'alert.historical.enabled';
delete from "SAP_HANA_DBCC"."sap.hana.dbcc.data::Site.PreferenceValues"
where "name" = 'alert.historical.purge.max_age';
update "SAP_HANA_DBCC"."sap.hana.dbcc.data::Alerts.HistoryConfig"
set "isEnabled" = 0, "maxAge" = 0;
Conclusion
Historical Alerts can prove useful for deeper insight and analysis of system health and performance. SAP HANA provides the capability to fine tune Historical Alert collection and storage over your landscape of systems, using SAP DB Control Center and SAP HANA Studio. For a collection of all the SQL Statements used in this document, please refer to the available files, enableAlerts.sql and disableAlerts.sql.
It is also possible to move the historical alerts into extended storage using SAP HANA Dynamic Tiering. For complete details on this topic, please refer to this document: http://scn.sap.com/docs/DOC-69205.
- SAP Managed Tags:
