DB2 agent does not collect SQL performance information or Top SQL statements (4227133)

Return

Feedback Submitted

Did this article solve an issue for you?

Select Rating

Title

DB2 agent does not collect SQL performance information or Top SQL statements
Description

DB2 SQL Performance panel does not display data. The user set the Agent Status Property "SQL text size (bytes)" to very large numbers (include 32768, 51240, and 100000) and the data still did not display.
An error message similar to the following appears in the DB2 agent log file
ERROR [HOSTNAME-INSTANCE-lowPriorityPool-2-[DB2_Top_SQLs][]] com.quest.qsi.fason.core.collection.processor.InstanceProcessor - Failed to run DB2TopSQLRetrieveTextProcessor processor of DB2_Top_SQLs collection.
java.lang.RuntimeException: Failed to execute collection [DB2_Top_SQLs], reason=[Foglight][DB2 JDBC Driver][DB2]COLUMN DEFINITION SPECIFIES INVALID LENGTH/PRECISION/SCALE ATTRIBUTE
Cause

When DB2 processes a query it uses work file table spaces.
Some SQL operations, such as joins, can create a resulting row that does not fit in a 4 KB page. Therefore it needs a larger lwork file that has 32 KB pages.
The length of a single row(including all columns) in the work table, cannot exceed 32KB
The maximum length is 32672 and only if a 32KB tablespace (and a related buffer pool) exists.
Resolution

The maximum size of the ASP (Agent Status Property) value in the DB2 agent for "SQL text size (bytes)" should be no larger than 32672.

Feedback Submitted

Did this article solve an issue for you?

Select Rating

Title