Db2 Long Running Queries Test

Nothing can degrade the performance of the server like a resource-hungry or a long-running query! When such queries execute on the server, they either hog almost all the available CPU, memory, and disk resources or keep the resources locked for long time periods, thus leaving little to no resources for carrying out other critical database operations. This can significantly slowdown the database server and adversely impact user experience with the server. To ensure peak performance of the DB2 UDB server at all times, such queries should be rapidly identified and quickly optimized to minimize resource usage. This can be done using the Db2 Long Running Queries test.

This test auto-discovers the DB2 UDB database instances, tracks the currently executing queries on each database and determines the number of queries that have been running for a long time.

Target of the test : A DB2 database server

Agent deploying the test : An internal agent

Outputs of the test : One set of results for every database on the DB2 database server that is currently active

Configurable parameters for the test
Parameter	Description
Test Period	How often should the test be executed.
Host	Specify the IP address of the DB2 server in this text box.
Port	Specify the port at which the target host is listening. The default port is 50000.
Username, Password and Confirm Password	To monitor Db2 UDB database server, the eG agent should be configured with the credentials of a user with any of the following privileges SYSADM or SYSCTRL or SYSMAINT or SYSMON. Specify the credentials of such a user in the User and Password text boxes. Confirm the Password by retyping it in the Confirm Password text box.
Database	The test uses a database on the monitored Db2 UDB server. Specify the name of the database in the Database text box.
Include DB	Specify the list of databases that you wish to monitor in a comma-seperated list in the Include DB text box. By default this is set to ALL.
Exclude DB	Specify the list of databases that you wish to exclude from monitoring in a comma-seperated list in the Exclude DB text box. By default this is set to NONE.
Elapsed Time (Seconds)	There is a specified duration (in seconds) for which the query should have executed to be regarded as long running query. Specify the duration in Elapsed Time (Seconds) text box. The default value is 10.
SSL	If the target database server monitored is SSL-enabled, then set the SSL flag to Yes. If not, then set the SSL flag to No.
DD Row Count	The number of rows to be displayed in DD page is entered in the DD Row Count text box. By default the value is set to 5.
Detailed Diagnosis	To make diagnosis more efficient and accurate, the eG Enterprise embeds an optional detailed diagnostic capability. With this capability, the eG agents can be configured to run detailed, more elaborate tests as and when specific problems are detected. To enable the detailed diagnosis capability of this test for a particular server, choose the On option. To disable the capability, click on the Off option. The option to selectively enable/disable the detailed diagnosis capability will be available only if the following conditions are fulfilled: The eG manager license should allow the detailed diagnosis capability Both the normal and abnormal frequencies configured for the detailed diagnosis measures should not be 0.

Measures made by the test:
Measurement	Description	Measurement Unit	Interpretation
Number of long run query	Indicates the number of queries currently executing on this database that have been running for more time than the configured ELAPSED TIME.	Number	The detailed siagnosis of this measure shows the name and ID of application, name of the user, time takne in seconds, CPU time in seconds, query cost estimation, number of reads and writes and SQL text.
Maximum elapsed time	Indicates the maximum time taken by this database for executing the queries.	Seconds	Ideally, a low value is desired for this measure.