Using custom SQL queries
PATROL for SAP HANA includes custom SQL queries that you can configure to monitor the health of a SAP HANA Database.
The following KM attributes can be used to generate events:
- Number of records - number of rows returned by the query
- Query status - success status of the query
- Response time - time (ms) of the custom query response time
- Value - value of a specific cell
You can use the events generated by these attributes to determine how efficiently the SQL queries are handled in the SAP HANA Database.
Number of records
Set a threshold for this attribute to get a notification if the query returns a high number of records (rows) that exceed the threshold. By default, this attribute does not generate an alarm or warning event. For more information about setting an alarm or warning event for this attribute, or changing the default attribute range, see Defining-a-monitoring-policy.
For example, you can use the Number of Records attribute of the Custom Query monitor type to monitor how many users are locked.
While configuring a policy in the TrueSight console, you add a custom query to the environment configuration through the Custom SQL queries panel. In the Custom SQL queries section, if you enable the number of records metric, the result is displayed as an annotation on the number of records metric.
Here is an example on how to sample the name of the users whose expiry date is less than seven days:
Custom SQL Query panel sample
Here is the complete query:
USER_NAME from "PUBLIC"."USERS" WHERE
USER_DEACTIVATED='TRUE'
The screenshots below show the output of the query with an annotation.
Locked users graph
Suggested action
A high number of return rows can indicate an over-utilized resource. A generated event indicates that you might want to update the number of rows returned by the query to avoid alarms or warning events.
Query status
Set a threshold for this attribute to get a notification if the query was successful or unsuccessful. By default, this attribute will not generate an alarm or warning event as the threshold is not set. For more information about setting an alarm or warning event for this attribute, or changing the default attribute range, see Defining-a-monitoring-policy.
To see more information about this attribute, see the Query status.
Query status graph
- ====
Suggested action
If an SQL query fails because of a bad SQL query definition, revise your query and correct the configuration in the TrueSight console.
Response time
Set a threshold for this attribute to monitor the response time, in milliseconds, required by a query to retrieve data. By default, this attribute will not generate an alarm or warning event. For more information about setting an alarm or warning event for this attribute, or changing the default attribute range, see Defining-a-monitoring-policy. To see more information about this attribute, see the Response time.
Response time graph
- ====
Suggested action
A high response time value can indicate delays occurring in the SAP HANA, check your database performance.
Value
This attribute is used to return a numeric value. If the query result is a string which can be converted to a numeric value, the Value attribute displays the converted value else the attribute will stay offline.
For example: Result = 123.456e+5, the numeric value would be 12345600.
While configuring a policy in the TrueSight console, you add a custom query to the environment configuration through the Custom SQL queries panel. If the query result is an output of multiple rows, the Value attribute will be in the offline state, and the output is displayed as an annotation in the number of records attribute.
By default, this attribute will not generate an alarm or warning event. For more information about this attribute, see the Value attribute.
Here is an example on how to set a threshold for the Value attribute to identify an increase in the number of inactive sessions in the database.
count(connection_id) from m_connections where
connection_status = 'RUNNING'
For more information about setting an alarm or warning event for this attribute, or changing the default attribute range, see Defining-a-monitoring-policy.
Value graph
- ====
Event generated by the KM
You can see the event in the TrueSight console in Monitoring > Events.
Suggested action
A higher value can indicate many inactive sessions in the database which can result in exhausting the max sessions limit.