Create DB2 Customized SQL Result workspace

Body

DB2 monitoring agent allows you to monitor columns returned by your own SQLs through the Customized SQL feature. If you are new to this feature, then check the blog entry DB2 Monitoring customized for the DBA to get started.

This blog is to show you how to create a workspace for directly viewing result of a customized SQL.

Viewing the result set (rows returned by SQL) of Customized SQL in TEP:

Once you have defined your custom SQLs (in ITM_HOME/config/kudcussql.properties file), the Customized SQLs workspace lists all the SQL definitions. Select the link for individual SQL ID, which prompts to specify the database alias. You can either enter a specific database name in the prompt, or enter * to run the SQL for all databases.This leads to the Customized SQL Result workspace which shows output rows resulting from running the SQL.

In this example,  SQL ID "ten_worst_sqls" is used, which is defined in /opt/IBM/ITM/config/kudcussql.properties file as follows.

[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

The screen print below shows the steps required to access the Customized SQL result workspace.

If you don't need to view the SQL results often, then this default method of accessing Customized SQL Result workspace should suffice. However, if you (as a monitoring team member) or the DBA, need to run some SQLs more frequently, then it is desirable to have a direct view of the result set of such SQLs.

The steps below show how to create a workspace to display the result rows of a custom SQL.

Step 1. Log into TEP and enter workspace admin mode by using Administer Users window as shown in the screen print below:

Step 2. Create a workspace to display the Result Set (rows returned by SQL) of the Custom SQL (using example of SQL ID 'ten_worst_sqls').

2.1    Create a new workspace.

Select the Customized SQLs navigator item for DB2 agent in TEP to bring up Customized SQLs workspace. Select File -> Save Workspace As option to save the workspace as "ten_worst_sqls_workspace".  In the subsequent steps, modify this new workspace.

2.2    Create a query for the Custom SQL ("ten_worst_sqls") Result Set.

Right click the "Customized SQL Definition" table view in  "ten_worst_sqls_workspace" and then select Properties menu option.

On Properties window, select “Click here to assign a query”, to bring up the Query Editor. Duplicate the Customized SQL Result query (Hint: select the query in the left panel, and then select 'Create Another Query' icon in the query editor). In this example, the duplicate query is named ten_worst_sqls_query.

2.3   Edit the new query (ten_worst_sqls_query).

Add the column values SQL ID (=='ten_worst_sqls') and DB Alias Filter Name (==*) as shown in the screen print below.

Click OK to save. This new query will provide the result of custom SQL ten_worst_sqls in the new workspace.

2.4    Change the Title text of the table view on the Style tab of Properties window.

Select OK to save the new query and title for the table view. Save the workspace ("ten_worst_sqls_workspace").

The customized SQL results workspace is now ready.

Just select Customized SQLs navigator item in TEP and right click to see available workspaces, and select ten_worst_sqls_workspace to see result rows of ten_worst_sqls customized SQL.

Customize the workspace as needed.

In the screen print below, the columns of the result table view of ten_worst_sqls_query have been reordered (by dragging and dropping) to bring the non-null columns to the beginning of the table view. You can use Filters and Thresholds tab in the Properties window to further customize this view.