IBM Support

Enabling query history for individual executions in DSM v2.1

Troubleshooting


Problem

How do I enable Data Server Manager to record the execution of individual queries?

Symptom

In previous versions of Data Server Manager, users could see individual queries as they were executing, and view an aggregate history for the top N queries. Users couldn't, however, view a history of individual query executions.

Resolving The Problem

With IBM Data Server Manager Version 2.1, you can now configure your DB2 database to capture individual statement executions and later view them in their historical context. By enabling activity event monitoring, you can capture historical detail about each executed statement and save those details to tables in DB2. You can then explore that information using IBM Data Server Manager Version 2.1.

To configure Data Server Manager for collecting execution history for individual queries:

  1. Log onto your monitor database and open your DB2 terminal interface, or open Data Server Manager and launch the SQL Editor.
  2. Enter one or both of the following:
    • Alter the workload manager (WLM) service class to capture heavyweight queries:

      alter service class SYSDEFAULTMANAGEDSUBCLASS under sysdefaultuserclass collect activity data on all with details;

    • Alter the WLM service class to capture light, unmanaged queries:

      alter service class SYSDEFAULTSUBCLASS under sysdefaultuserclass collect activity data on all with details;
    • Alter both to capture all queries

    1. Enter the following to turn on the Administrative Task Scheduler (ATS):

    db2set DB2_ATS-ENABLE=YES;




    2. Enter the following to create a table space (DSMSPACE):

    create bufferpool DSMPOOL all DBPARTITIONNUMS size 1000 automatic pagesize 32768;
    create database partition group DSMGROUP on all DBPARTITIONNUMS;
    create tablespace DSMSPACE in DSMGROUP pagesize 32768 managed by automatic storage autoresize yes initialsize 100M maxsize 500m bufferpool DSMPOOL;
    create TEMPORARY TABLESPACE TEMPSPACE2 PAGESIZE 32K managed by AUTOMATIC STORAGE EXTENTSIZE 4 bufferpool DSMPOOL;


    3. Log on to Data Server Manager and go to Settings > Manage Connections.


    4. Scroll through the list of database connections, select the connection to the database you want to monitor, and then click Edit from the command bar.


    5. From the pop-up dialog box, click the Event Monitor tab, and then, from the drop-down list, select DSMSPACE.


    6. Go to Settings > Monitoring Profiles, select the profile you are using to monitor this database and then click Edit.


    7. Ensure that Repository Persistence is set to ON. Expand SQL statement execution data, and click Individual statement data.

    Optionally, you can configure the settings for collecting individual statement data:

    1. Click Use administrative task scheduler (ATS) to disable event monitors in failure cases to enable the watchdog. If Data Server Manager stops or can no longer reach the monitor database, ATS will eventually disable the event monitors.
    2. Click Capture in-progress query with event monitor to capture some running queries as well as those that have executed. Analyzing these running queries can help you identify what was happening before a crash occurred.
    3. Click Normalize captured SQL statements if you want to capture separate records for statements that include parameters. By default, text for statements that differ only in the values of constants embedded in the text are normalized to consolidate them and save space in the historical repository.

    8. Ensure In-Memory Monitoring is set to ON. Accept the default settings and click Save.

    Event monitor data is captured during the RtmonEngine collection cycle. The default collection interval is five minutes, but you can change this in the associated monitoring profile. Each row that has accumulated in the activity event monitor tables is copied to the repository, and then deleted from the monitored database.

    Pruning of the query history is done each collection cycle.

    9. Select Monitor > Database, and then click Statements. Click Real Time on the time slider control and select a past time frame to enter history mode. You can then manipulate the highlighted area on the slider control to filter the results that appear in the Individual Executions pane.

[{"Product":{"code":"SS5Q8A","label":"IBM Data Server Manager"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"--","Platform":[{"code":"PF016","label":"Linux"},{"code":"PF033","label":"Windows"},{"code":"PF002","label":"AIX"}],"Version":"2.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21984962