Question & Answer
Question
How can ITCAM agent for DB2 database be used to display ten worst performing SQLs?
Cause
The DB2 monitoring agent does not provide workspace for top ten worst performing SQL statements.
Answer
ITCAM DB2 agent, Version 7.1 provides "Customized SQL" monitoring feature which can be used to monitor data returned from any SELECT statement.
Locate the custom SQL definition file in the installation directory of the DB2 monitoring agent.
By default the customized SQL file is the following:
Unix/Linux: ITM_HOME/config/kudcussql.properties
Windows 32 bit agent: ITM_HOME\TMAITM6\kudcussql.properties
Windows 64 bit agent: ITM_HOME\TMAITM6_x64\kudcussql.properties
Edit the file and add following section for customized SELECT statement. This SQL queries SYSIBMADM.TOP_DYNAMIC_SQL view to return the ten worst performing SQLs in the DB2 instance.
[SELECTSQL]
SQL_ID=ten_worst_sqls
SQL_TEXT=SELECT NUM_EXECUTIONS, AVERAGE_EXECUTION_TIME_S, STMT_SORTS, SORTS_PER_EXECUTION, SUBSTR(STMT_TEXT,1,60) AS STMT_TEXT FROM SYSIBMADM.TOP_DYNAMIC_SQL ORDER BY NUM_EXECUTIONS DESC FETCH FIRST 10 ROWS ONLY
Edit the customized SQL as per your requirement.
Save changes and then refresh the "Customized SQLs" workspace in Tivoli Enterprise Portal (TEP) to view entry for SQL_ID=ten_worst_sqls.
Access the link next to the SQL_ID in Customized SQL Definition view.
Enter * when prompted to "Input database alias", or enter name of a database, then click OK.
"Customized SQL Result" view will display result of the SQL.
See the screen shots below.
Note: Columns have been reordered to display the columns with values to the beginning of the table view.
Related Information
Was this topic helpful?
Document Information
Modified date:
17 June 2018
UID
swg21652550