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
  1. TEST PERIOD – How often should the test be executed
  2. HostThe IP address of the DB2 server
  3. PortThe 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).