DB2 10.5 for Linux, UNIX, and Windows

Exercise 10: Generating historical data and reports

This exercise demonstrates how to use the WLM Historical Analysis Tool sample.

Estimated time: 20-25 minutes

DB2® database products include Perl sample scripts that provide information captured by the WLM activities event monitor for historical analysis. You can review or even modify these scripts to produce additional historical analysis reports to suit your needs. The perl scripts are:

wlmhist.pl: generates historical data
wlmhistrep.pl: produces reports from the historical data.

Step 1: Create the explain tables

In order to generate some historical data, the explain tables must exist under the schema of the user running the tool. To create the explain tables, go to the /sqllib/misc directory and run the following:

db2 CONNECT TO SAMPLE

db2 -tvf EXPLAIN.DDL

Step 2: Alter the service class to collect activity data

Enable activity collection by specifying the COLLECT ACTIVITY DATA clause on the WLM object of interest. For this exercise, we want to generate historical data for activities run in the default service subclass of the default user service super class:

ALTER SERVICE CLASS SYSDEFAULTSUBCLASS UNDER SYSDEFAULTUSERCLASS
       COLLECT ACTIVITY DATA ON COORDINATOR WITH DETAILS

Step 3: Enable the activities event monitor

Since the activities event monitor was created in Exercise 1 Step 1, enable it now if it is not enabled already.

SET EVENT MONITOR DB2ACTIVITIES STATE 1

Step 4: Run some activities

Run some activities so that activity data is collected to generate historical data on.

db2 -tvf work1.db2
db2 -tvf work2.db2

Step 5: Disable the event monitor for activities

It is highly recommended that you turn off the event monitor for activities before generating historical data. If you do not do this, any DML activities that are run as a result of the historical data generator may also be captured and put into the DB2 event monitor activity tables, thereby dramatically increasing the number of actual activities for which activity data is generated.

CONNECT TO SAMPLE

SET EVENT MONITOR DB2ACTIVITIES STATE 0

Step 6: Generate historical data

Run the historical data generator script, wlmhist.pl, to generate historical data for activities that are captured in the activities event monitor tables. The format is as follows:

wlmhist.pl dbname user password [fromTime toTime workloadid 
        serviceClassName serviceSubclassName activityTable activityStmtTable]

Use a dash (-) to bypass any optional parameters.

Additional Information: The historical data generator (wlmhist.pl) script will generate only historical data for DML. If you have previously run the historical data generator (wlmhist.pl) script once or more, it is recommended that, before running it again, you clear the activityTable and activityStmtTable tables in order to avoid duplicating data. If you choose not to clear these two tables, be sure to use the fromTime and toTime input parameters to ensure you do not generate historical data for activities that have already had data generated for them.

For this exercise, generate historical data for all activities that have been captured in the activities event monitor.

Perl wlmhist.pl sample db2inst1 password

You may notice some errors similar to the following:

Error running explain [IBM][CLI Driver][DB2/LINUXX8664] SQL0418N  A
statement contains a use of a parameter marker that is not valid. SQLSTATE=42610
 for statement VALUES (TABLE_SCHEMA(:H00002 , :H00003   )) INTO :H00007

DBD::DB2::db do failed: [IBM][CLI Driver][DB2/LINUXX8664] SQL0418N  A
statement
contains a use of a parameter marker that is not valid. SQLSTATE=42610

When generating historical data, explain is run on the actual statement. In some cases, explain cannot be run on some statements with parameter markers and an error is returned. Any activity that shows such an error will not have historical data generated for it.

Once the tool has completed generating historical data, it will tell you how many activities it has successfully generated historical data for.

Step 7: Generate historical data reports

Run the historical data report script wlmhistrep.pl to generate reports based on the data that was generated in step 1. The format is as follows:

wlmhistrep.pl dbAlias userId passwd [outputFile report schemaName fromTime toTime submitter]

Use a dash (-) to bypass optional parameters.

The report parameter can be any combination from the following letters:

A: Tables hit
B: Tables not hit
C: Indexes hit
D: Indexes not hit
E: Submitters

If the userId parameter you specify is not the same as what was used to run the wlmhist.pl script when the wlmhist table was created, you must specify the correct schemaName. The fromTime and toTime parameters must be specified in timestamp format (for example 2007-06-06-17.00.00).

For this exercise, generate reports for tables hit and indexes not hit:

Perl wlmhistrep.pl sample db2inst1 password - AD

The output will look something such as the following:

                TABLES HIT REPORT FOR DATABASE sample
              _______________________________________________________


TABLE NAME                     TABLE SCHEMA         % HITS        TOTAL HITS
______________________         __________________   _____________ ____________

EMPLOYEE                        KARENAM              7.14285714            2
INVENTORY                       KARENAM             14.28571429            4

ORG                             KARENAM             28.57142857            8
SALES                           KARENAM             14.28571429            4
SYSROUTINES                     SYSIBM               7.14285714            2
SYSTABLES                       SYSIBM              21.42857143            6
SYSTABLESPACES                  SYSIBM               7.14285714            2


             INDEXES NOT HIT REPORT FOR DATABASE sample
            ___________________________________________________________


TABLE NAME         TABLE SCHEMA    INDEX NAME         INDEX SCHEMA    INDEX TYPE
__________________ _______________ __________________ _______________ __________
EXPLAIN_ARGUMENT   KARENAM         ARG_I1             KARENAM         REG
HMON_ATM_INFO      SYSTOOLS        ATM_UNIQ           SYSTOOLS        REG
CUSTOMER           KARENAM         CUST_CID_XMLIDX    KARENAM         XVIL
CUSTOMER           KARENAM         CUST_NAME_XMLIDX   KARENAM         XVIL
CUSTOMER           KARENAM         CUST_PHONES_XMLIDX KARENAM         XVIL
CUSTOMER           KARENAM         CUST_PHONET_XMLIDX KARENAM         XVIL
EXPLAIN_DIAGNOSTIC KARENAM         EXP_DIAG_DAT_I1    KARENAM         REG
HMON_COLLECTION    SYSTOOLS        HI_OBJ_UNIQ        SYSTOOLS        REG
ADVISE_INDEX       KARENAM         IDX_I1             KARENAM         REG
ADVISE_INDEX       KARENAM         IDX_I2             KARENAM         REG
SYSATTRIBUTES      SYSIBM          INDATTRIBUTES01    SYSIBM          REG
SYSATTRIBUTES      SYSIBM          INDATTRIBUTES02    SYSIBM          REG
:
:

Step 8: Reset for the next exercise

Disable activity collection for the default service subclass of the default user service super class, and clean up the activity tables.

ALTER SERVICE CLASS SYSDEFAULTSUBCLASS UNDER SYSDEFAULTUSERCLASS
       COLLECT ACTIVITY DATA NONE

DELETE FROM ACTIVITY_DB2ACTIVITIES
DELETE FROM ACTIVITYSTMT_DB2ACTIVITIES