IBM Support

DB2 agent - display worst performing SQLs

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

[{"Product":{"code":"SS3JRN","label":"Tivoli Composite Application Manager for Applications"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"ITCAM Agent for DB2 - 5724B96DO","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"7.1","Edition":"","Line of Business":{"code":"LOB45","label":"Automation"}}]

Document Information

Modified date:
17 June 2018

UID

swg21652550