Db2 SQL Activity Test

This test, executed by an internal agent, tracks various statistics pertaining to the SQL activities happening in a DB2 UDB database. The details of the test are provided below:

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 being monitored

Configurable parameters for the test
TEST PERIOD – How often should the test be executed Host – The IP address of the DB2 server Port – The port number through which the DB2 server communicates. The default port is 50000.

Measurements made by the test:
Measurement	Description	Measurement Unit	Interpretation
Dynamic SQL rate	Indicates the number of dynamic SQL statements that were attempted.	Stmts/Sec	This is an indication of throughput of the system during the monitoring period. A high value of dynamic SQLs and low value of failed SQLs indicate good throughput.
Select rate:	Indicates the number of SELECT SQL statements that were executed.	Stmts/Sec	This measure can be used to determine the level of database activity at the application or database level. This measure is useful for analyzing the application activity and throughput.
Failed SQL rate:	Indicates the number of SQL statements that were attempted, but failed.	Stmts/Sec	This measure helps in determining reasons for poor performance, since, failed statements means resource wastage and lower throughput for the database.
UID SQL rate:	Indicates the number of SQL UPDATE, INSERT, and DELETE statements that were executed	Stmts/Sec	This information can be useful for analyzing application activity and throughput.
SQL failures:	Indicates the percentage of SQL statements that failed during the interval.	Percent	A relatively high value indicates a problem. The percentage of SQL statements that received a negative SQL code indicates a possible cause of poor performance. Failed SQL statements waste system resources. Hence, this value of this measure should be very low.
Percentage of DDL SQLs:	Denotes the percentage of SQL statements that are DDL during a specific interval.	Percent	This value should normally be low.
Percentage of UID SQLs:	Percentage of SQL statements that are update/insert/delete during the interval.	Percent	This measure can be used to determine the level of database activity at the application or database level.

Note:

The values for these measures are dependent on the type of application that the database is supporting. For example, in an On-Line Transaction Processing System (OLTP), we can expect a relatively high number of Updates, Inserts, and Deletes compared to a Decision Support System (DSS).