Using custom SQL queries

PATROL for IBM DB2 includes custom SQL queries that you can configure to monitor the health of a database.

The following KM attributes can be used to generate events:

• DB2 custom SQL query number of records–number of rows returned by the query
• DB2 custom SQL query status–status of the query
• DB2 custom SQL query response time–time of the custom query response time
• DB2 custom SQL query value–returns a numeric value.

You can use the events generated by these attributes to determine how efficiently the SQL queries are handled in the Database.

DB2 custom SQL query 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. 

While configuring a policy, you can add a custom SQL query configuration list to the environment. In case, if you select  Enable number of records annotation, an annotation will be displayed with the query output result.

For example, if you want to retrieve a specific set of DB2 application details, use the following query:

select DBPARTITIONNUM, AUTHID, APPL_NAME, CLIENT_PID, AGENT_ID,CLIENT_NNAME from SYSIBMADM.APPLICATIONS

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.

DB2 custom SQL 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 DB2 custom SQL query status (Status).

Suggested action

If a SQL query fails because of a bad SQL query definition, revise your query and correct the configuration in the TrueSight console.

DB2 custom SQL query 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 DB2 custom SQL response time (ResponseTime).

Suggested action

A high response time value can indicate delays occurring in the DB2 Database, check your database performance.

DB2 custom SQL query value

This attribute is used to return a numeric value. If the query result is a numeric or string which can be converted to a numeric value, the Value attribute displays the converted value else the attribute will be inactive.

For example: if the result = 123.456e+5, the numeric value would be 12345600.

While configuring a policy, you can add a custom SQL query configuration list to the environment.  If the query result is an output of multiple rows, the Value attribute will be inactive. By default, this attribute will not generate an alarm or warning event. For more information about this attribute, see the DB2 custom SQL query value (Value) attribute.

For more information about setting an alarm or warning event for this attribute, or changing the default attribute range, see Defining a monitoring policy.

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.