DB2 9.7: Using new Workload Manager features

Get the most from your DB2 for Linux, UNIX, and Windows database

This tutorial provides a series of exercises using IBM® DB2® Workload Manager (WLM). By following these exercises, you should become familiar with the DB2 WLM feature and start to see the immediate value you can obtain from its use. For those new to WLM, the tutorial serves as an easy starting point for you to begin using WLM right out of the box. For experienced WLM users, the tutorial gives you a hands-on introduction to new WLM functions introduced in DB2 9.7.

Burt L. Vialpando, Certified I/T Specialist - DB2 Migrations, IBM

Burt VialpandoBurt Vialpando has been an IBM employee since 1998, with professional database experience since 1984. Burt is a Senior Certified IT specialist currently working for the DB2 migration team performing pre-sales Oracle to DB2 migration support. He holds numerous DB2, Oracle, and other certifications and currently serves on the IT Certification Board.



Vikram S. Khatri, Certified Consulting I/T Specialist, IBM

Vikram S Khatri works for IBM in the Sales and Distribution Division and is a member of the DB2 Migration team. Vikram has 23 years of IT experience and specializes in enabling non-DB2 applications to DB2. Vikram supports the DB2 technical sales organization by assisting with complex database migration projects as well as with database performance benchmark testing.



13 August 2009

Also available in

Before you start

Learn what to expect from this tutorial, and how to get the most out of it.

About this tutorial

This tutorial takes a simple approach to implementing WLM. The intent is to make the basic functionality easy to understand.

Objectives

This tutorial teaches you how to:

  • Understand the DB2 WLM objects and their relationships
  • Use the new DB2 9.7 BUFFERPOOL PRIORITY in a service class definition
  • Use the new DB2 9.7 connection attribute wildcards in a workload definition
  • Use the new DB2 9.7 ADDRESS connection attribute in a workload definition
  • Use a threshold with the new DB2 9.7 CPUTIME metric
  • Use a service subclass threshold to do new DB2 9.7 service subclass remapping (also known as priority aging)
  • Use a new DB2 9.7 WLM sample script to build a tiered service class environment
  • Use a WLM procedure to set client information
  • Use WLM functions to monitor your environment — short term
  • Use WLM event monitors to monitor your environment — long term
  • Understand remapping metrics

Prerequisites

The tutorial is intended to be used by Data Base Administrators (DBAs) who are experienced using DB2 but may be new to the topic of using the DB2 WLM functions.

System requirements

To perform the WLM exercises, you need to have access to DB2 Enterprise Edition for Linux®, UNIX®, and Windows® (DB2 ESE) Version 9.7. See the Resources section for a link you can use to download a free trial version of IBM DB2 for Linux, UNIX, and Windows. The trial version includes DB2 ESE.

The exercises are described and shown in a Windows environment. This is done simply for ease of use purposes; you can apply the same concepts using a Linux or UNIX system.

The exercises use the files contained in the WLMLab.zip download file included with this tutorial. Download WLMLab.zip from the download section and extract the files onto your Windows C:\ drive.


WLM features overview

This section provides simple definitions of WLM features and descriptions of the features that are new for DB2 9.7.

Definition of WLM features — In simple terms

DB2 workload
Serves as the primary point of control, based on the source of submitters of work, and routes work to service classes via connection attributes
DB2 service class
Serves as the primary point of resource control for all executing work activities
DB2 threshold
Provides limits to control behaviors of database activities based on predictive and reactive elements for all activities occurring in a database or service class
DB2 work action set (work action > work class set > work class)
Provides ability to discriminate between different types of database activities (read, write, call, DML, DDL and load) occurring in a database or service class
DB2 WLM monitor and control capabilities
db2pd utility, table functions, stored procedures and event monitors provide real-time as well as historical monitoring capabilities
Figure 1. Summary Diagram of WLM Features in DB2 9.5
Summary Diagram of WLM Features in DB2 9.5

New WLM Features in DB2 9.7

Wildcards in connection attributes

New in DB2 9.7

You can avoid having to code for db2user01, db2user02, db2user03, db2user04, db2user05, db2user06, etc. by using db2user*

  • You can define workloads by providing mapping values for a set of connection attributes
  • To minimize scripting requirements, you can now use wildcards with the following connection attributes:
    • APPLNAME
    • CLIENT_USERID
    • CLIENT_APPLNAME
    • CLIENT_WRKSTNNAME
    • CLIENT_ACCTNG
    CREATE WORKLOAD "work1" CLIENT_USERID('db2user*');
  • You cannot use wildcards with the following connection attributes:
    • SYSTEM_USER
    • SESSION_USER
    • SESSION_USER GROUP
    • SESSION_USER ROLE

New ADDRESS connection attribute

In addition to the connection attributes shown above, DB2 9.7 introduces a new one called ADDRESS. Following are examples of using the attribute with various types of syntax:

  • Secure domain syntax:
    CREATE WORKLOAD "IPADDR1" ADDRESS(‘mydomain.ibm.com’);
  • IPv4 syntax:
    CREATE WORKLOAD "IPADDR2" ADDRESS('9.26.53.111');
  • IPv6 long syntax:
    CREATE WORKLOAD "IPADDR3" ADDRESS('2002:91a:519:13:204:acff:fe57:6135');
  • IPv6 short syntax:
    CREATE WORKLOAD "IPADDR4" ADDRESS('fe80::202:55ff:fe9a:6eee');

Aggregate activity

  • You can now accumulate aggregate activity data at the workload level. (This is in addition to the service class and work class levels available in DB2 9.5.)
  • This new function facilitates the following:
    • The priority of work can be lowered over time by remapping the work between service subclasses.
    • Aggregate activity data can be collected at the workload level to help determine appropriate maximum values for thresholds defined on the workload domain.
    • Monitoring granularity is increased along with a decrease in storage requirements.
CREATE EVENT MONITOR DB2Activities FOR ACTIVITIES WRITE TO TABLE;
SET EVENT MONITOR DB2Activities STATE 1;
CREATE WORKLOAD WORKLD1 APPLNAME('DB2APP1')
COLLECT AGGREGATE ACTIVITY DATA EXTENDED;  <--- New in DB2 9.7

Buffer pool I/O priority

  • You can use this new DB2 9.7 function to influence the proportion of pages in the buffer pool that may be occupied by activities in a given DB2 service class.
  • You can specify the following values for the buffer pool priority assigned to a service class:
    • HIGH
    • MEDIUM
    • LOW (this is the superclass default value)
  • These other DB2 9.5 service class priorities remain in WLM:
    • Agent (CPU) priority
      • Values -20 to 20 for UNIX
      • Values -6 to 6 for Windows
    • Prefetch (I/O) priority with possible values of:
      • HIGH
      • MEDIUM
      • LOW
CREATE SERVICE CLASS “Managers”            
UNDER "Marketing"
AGENT       	PRIORITY 5
PREFETCH 	PRIORITY HIGH
BUFFERPOOL 	PRIORITY HIGH; <--- New in DB2 9.7

Linux WLM integration

  • DB2 9.5 WLM service classes integrated DB2 processing with AIX WLM service classes. DB2 9.7 now offers the same level of integration for the Linux operating system.
  • Each DB2 service class can be associated with a specific Linux WLM service class:
    • DB2 automatically associates all agents working in a DB2 service class with the identified Linux WLM service class.
    • This adds chargeback capability based on CPU consumption.
    ALTER SERVICE CLASS "Marketing"
    OUTBOUND CORRELATOR "_DB2_Marketing"; <-- Linux WLM definition
  • Linux WLM provides sophisticated management of CPU to maximize utilization of that resource while respecting any explicit allocations:
    • Allocation of CPU is done via CPU shares assigned to Linux WLM service classes.
    • CPU shares can be adjusted dynamically using Linux WLM interfaces.
    • Provides the ability to borrow unused CPU shares from other service classes.
    • Provides Operating System (OS) level statistics per Linux service class.
Figure 2. Linux WLM integration
Linux WLM Integration

Priority aging

  • Priority aging is also known as aging of in-process activities.
  • Priority aging can automatically raise or lower the priority of in-progress activities over time. For example, you can use priority aging to control longer-running activities, so that throughput for shorter-running activities is improved.
  • Priority aging is required to deprecate Query Patroller functionality.
  • Priority aging is accomplished with service class thresholds that can remap activities from service subclasses to other service subclasses.
Figure 3. New priority aging
Linux WLM Integration

Priority aging step 1: Create service class hierarchy

CREATE SERVICE CLASS WLM_TIERS;
CREATE SERVICE CLASS WLM_SHORT UNDER WLM_TIERS;
CREATE SERVICE CLASS WLM_MEDIUM UNDER WLM_TIERS;
CREATE SERVICE CLASS WLM_LONG UNDER WLM_TIERS;

Priority aging step 2: Create remapping thresholds

CREATE THRESHOLD WLM_TIERS_REMAP_SHORT_TO_MEDIUM FOR
SERVICE CLASS WLM_SHORT UNDER WLM_TIERS ACTIVITIES
ENFORCEMENT DATABASE PARTITION WHEN
CPUTIMEINSC > 10 SECONDS CHECKING EVERY 5 SECONDS <--- New in DB2 9.7
REMAP ACTIVITY TO WLM_MEDIUM; <--- New in DB2 9.7

CREATE THRESHOLD WLM_TIERS_REMAP_MEDIUM_TO_LONG FOR
SERVICE CLASS WLM_MEDIUM UNDER WLM_TIERS ACTIVITIES
ENFORCEMENT DATABASE PARTITION WHEN
CPUTIMEINSC > 10 SECONDS CHECKING EVERY 5 SECONDS <--- New in DB2 9.7
REMAP ACTIVITY TO WLM_LONG; <--- New in DB2 9.7

Priority aging step 3: Create work class set (to identify which actions to map)

CREATE WORK CLASS SET WLM_TIERS_WCS
( WORK CLASS WLM_DML_WC      WORK TYPE DML,
WORK CLASS WLM_CALL_WC     WORK TYPE CALL,
WORK CLASS WLM_OTHER_WC  WORK TYPE ALL );

Priority aging step 4: Create work action set (to do the primary activity mapping)

CREATE WORK ACTION SET WLM_TIERS_WAS FOR SERVICE CLASS WLM_TIERS
USING WORK CLASS SET WLM_TIERS_WCS
( WORK ACTION WLM_DML_WA 	ON WORK CLASS WLM_DML_WC
MAP ACTIVITY TO  WLM_SHORT,
WORK ACTION WLM_CALL_WA 	ON WORK CLASS WLM_CALL_WC
MAP ACTIVITY TO  WLM_SHORT,
WORK ACTION WLM_OTHER_WC 	ON WORK CLASS WLM_OTHER_WC
MAP ACTIVITY TO  WLM_MEDIUM );

New thresholds

AGGSQLTEMPSPACE controls the maximum amount of system temporary table space that can be used across all activities in a service subclass.

CREATE THRESHOLD "Detect High Temp" 
FOR SERVICE CLASS "Marketing" ACTIVITIES 
ENFORCEMENT DATABASE 
WHEN AGGSQLTEMPSPACE > 100 M	<--- New in DB2 9.7
COLLECT ACTIVITY DATA WITHOUT DETAILS
CONTINUE;

CPUTIME controls the maximum amount of processor time that an activity can use on a particular database partition during execution.

SQLROWSREAD controls the maximum number of rows that an activity can read on a particular database partition.

CREATE THRESHOLD DBMAXCPU 
FOR DATABASE ACTIVITIES 
ENFORCEMENT DATABASE PARTITION
WHEN CPUTIME > 30 SECONDS CHECKING EVERY 5 SECONDS <--- New in DB2 9.7
STOP EXECUTION;

Additional thresholds available at the workload domain

  • Activity-based thresholds on the workload domain have been added to some existing thresholds and included with some new thresholds to provide greater control over resources.
  • These thresholds serve to avoid having to isolate applications from each other in separate service classes.
  • The thresholds are:
    • ESTIMATEDSQLCOST specifies the maximum estimated cost for DML activities.
    • SQLROWSRETURNED specifies the maximum number of rows that the data server can return to the client.
    • ACTIVITYTOTALTIME specifies the maximum lifetime of an activity.
    • SQLTEMPSPACE specifies the maximum amount of system temporary table space that a DML activity can use on a particular database partition.
    • SQLROWSREAD is a new threshold that specifies the maximum number of rows that a DML activity can read on any database partition.
    • CPUTIME is a new threshold that specifies the maximum amount of combined user and system processor time that an activity can use on a particular database partition while the activity is running.

New high water marks

  • The following new high water marks make it easier for you to determine what threshold values you should use with the new CPUTIME and SQLROWSREAD thresholds:
    • act_cpu_time_top is the high water mark for processor time used by activities at all nesting levels in a workload, service class, or work class.
    • act_rows_read_top is the high water mark for the number of rows read by activities at all nesting levels in a workload, service class, or work class.
  • The new lock_wait_time_top high water mark lets you determine the highest lock wait time (in milliseconds) of any request on a partition for a workload during a time interval.

New monitor element for remapped activities

  • You can use three new monitor elements to identify remapped activities and affected service subclasses:
    • num_remaps indicates how many times an activity has been remapped.
    • act_remapped_in counts the number of activities to be remapped into a service subclass.
    • act_remapped_out counts the number of activities to be remapped out of a particular service subclass.

New DB2 9.7 sample scripts

The following are new WLM sample scripts in DB2 9.7 to demonstrate a tiered service class configuration for a database.

  • wlmtiersdefault.db2
    • Oriented towards the elapsed execution time of incoming activities
    • Remaps activities from one service subclass to another service subclass DURING the activity execution based on elapsed time
    • Demonstrates the use of service classes, workloads, and thresholds
  • wlmtierstimerons.db2
    • Oriented towards the estimated cost of incoming activities
    • Remaps activities from one service subclass to another service subclass BEFORE the activity executes based on cost
  • wlmtiersdrop.db2
    • Drops the WLM objects created in the above scripts

Setting up a custom workload management environment

Follow the steps in this section to set up your custom workload management environment.

  1. Open a Windows command prompt and navigate to the directory where you extracted the contents of the WLMLab.zip file. By default this is C:\COBRA_LAB_SCRIPTS\WLM.

  2. Enter the command db2cmd (Figure 4) to start the DB2 Command Line Processor (CLP) (Figure 5).
    Figure 4. Start the DB2 CLP
    Screen shot showing db2cmd being entered in a Windows command prompt.
    Figure 5. DB2 CLP window
    Screen shot of the DB2 Command Line Processor
  3. Locate and review the commands named SETUP_COBRA_LABS.CMD and WLM01.CMD that you extracted from the WLMLab.zip download file (Figure 6). Notice that WLM01.CMD calls WLM02.DDL. Review WLM02.DDL also.
    Figure 6. SETUP_COBRA_LABS.CMD and WLM01.CMD in Windows Explorer
    Windows Explorer screen shot highlighting the ETUP_COBRA_LABS.CMD and WLM01.CMD commands
  4. From the CLP, enter SETUP_COBRA_LABS.CMD to run the command.

    Note: The WLM exercises in this tutorial do not work with any version of DB2 prior to 9.7. If you notice that GRANTS fail while running the SETUP_COBRA_LABS.CMD, it is an indication that you are running a version of DB2 prior to 9.7.

  5. From the CLP, enter WLM01.CMD to run the command.

  6. The WLM01.CMD command creates a file named WLM01_OUTPUT.TXT in the same directory as the command and writes its output to the file. Review the output that results from this initial run of the command (Figure 7).

    Figure 7. Default WLM environment created by initial run of WLM01.CMD
    Screen shot showing a listing of the WLM01_OUTPUT.TXT file after execution of WLM01.CMD command

    Notice that no specific workload, serviceclasses, etc. have been defined for your database. This is what WLM looks like by default.

  7. Close the WLM01_OUTPUT.TXT file.

  8. Review the WLM03.CMD and WLM04.DB2 files. WLM03.CMD calls WLM04.DB2, which creates a customized WLM environment.

    WLM04.DB2 creates two workload / service class configurations as shown in the following two tables:

    Workload #1
    Name:CLP_Workload_Admin
    Identify by:Client userid - logged-in user
    Service Class:CLP_Serv_Admin
    Let prefetch, agent and bufferpool priority default
    Sub Srv Class1:CLP_Serv_Admin_HI
    Give a high prefetch, agent and bufferpool priority
    Service Class:CLP_Serv_Admin
    Let prefetch, agent and bufferpool priority default
    Sub Srv Class2:CLP_Serv_Admin_MED
    Give a medium prefetch, agent and bufferpool priority
    Work Act. Set:Admin_Actions
    Map WRITE work to the high sub service class
    Map READ work to the medium sub service class
    Workload # 2
    Name:CLP_Workload_User1
    Identify by:Client userid db2user*
    Service Class:CLP_Serv_User
    Give a low prefetch, agent and bufferpool priority
    Figure 8 highlights the parts of the WLM04.DB2 script related to the WLM features that are new for DB2 9.7.
    Figure 8. New DB2 9.7 features for WLM
    Screen shot showing a partial listing of WLM04.DB2 with code related to new features highlighted
  9. From the CLP, enter WLM03.CMD to run the command and create the customized WLM environment.

  10. From the CLP, enter WLM01.CMD to run this command again.

  11. Look again at the WLM01_OUTPUT.TXT file. Now it reflects the customized WLM environment created by WLM03.CMD. Notice the following things about the customized WLM environment:

    • There is a hierarchy of workload to service superclass, to service subclass, to work action set, and so on.
    • There is a service superclass called CLP_Serv_User (Figure 9), which does not have any defined service subclasses. Its only service subclass is SYSDEFAULTSUBCLASS.
    • There is a workload IPADDR that does not have any service classes (it will use defaults).
    • There are now thresholds and event monitors that were created by the script.
    Figure 9. Customized WLM environment created by WLM03.CMD
    Screen shot showing a listing of the WLM01_OUTPUT.TXT file after execution of WLM03.CMD command
  12. Close the WLM01_OUTPUT.TXT file.


Setting up a tiered service class to show priority aging

Follow the steps in this section to set up a tiered service class that shows priority aging.

  1. Review the WLM05.CMD and wlmtiersdefault.db2 files that you extracted from the WLMLab.zip download file. WLM05.CMD calls wlmtiersdefault.db2, which is a new DB2 9.7 sample script (Figure 10).

    Figure 10. WLM05.CMD calling new DB2 9.7 sample script wlmtiersdefault.db2
    Screen shot of WLM05.CMD highlighting call to wlmtiersdefault.db2

    You may find it beneficial to spend some time reviewing the entire wlmtiersdefault.db2 script to better understand what it is doing. Some things to note:

    • The script creates a tiered WLM service class environment and thresholds that REMAP one service subclass to another service subclass (Figure 11).
      Figure 11. Service superclass and subclass definitions in wlmtiersdefault.db2
      Screen shot showing the part of wlmtiersdefault.db2 that defines the superclass and subclasses
    • The script does not create a workload, but rather, it alters the default workload to use the new tiered service classes.
    • The script uses thresholds to REMAP from the WLM_SHORT subclass to the WLM_MED subclass (Figure 12).
      Figure 12. Threshold mapping in wlmtiersdefault.db2
      Screen shot showing part of wlmtiersdefault.db2 that defines a threshold for wlm_short and remaps to wlm_medium
  2. From the CLP, enter WLM05.CMD to run the command and create the tiered service environment.

  3. From the CLP, enter WLM01.CMD to run the command again.

  4. Look again at the WLM01_OUTPUT.TXT file, which now reflects the customized WLM environment created by WLM05.CMD. The customized WLM environment now includes the WLM_TIERS service superclass along with its subclasses, and it also now has new service subclass thresholds (Figure 13).

    Figure 13. Customized WLM environment after running WLM05.CMD
    Screen shot showing a listing of the WLM01_OUTPUT.TXT file after execution of WLM05.CMD command
  5. Review the WLM10.CMD command file. It uses the db2pd utility to explore each WLM object in the customized environment you have built. WLM10.CMD also creates a file named WLM10_OUTPUT.TXT in the same directory as the command and writes its output to the file.

  6. From the CLP, enter WLM10.CMD to run the command.

  7. Review the WLM10_OUTPUT.TXT file to see the db2pd report for WLM objects. Most of the statistics currently show as 0 (Figure 14) because you are not currently running workload on your environment. Later in the tutorial you will run the WLM10.CMD script again and the statistics in the db2pd report will reflect that you have workload running.

    Figure 14. Output from WLM10.CMD currently showing zeros for statistics
    Screen shot showing WLM10_OUTPUT.TXT with zeros for local partition workload statistics
  8. Close the WLM10_OUTPUT.TXT file.


Setting up to simulate a workload

Follow the steps in this section to set up a tiered service class that shows priority aging.

  1. Locate and review the WLM20.CMD and WLM21.DDL script files that you extracted from the WLMLab.zip download file. Notice that WLM20.CMD calls WLM21.DDL. These scripts create a table named WLM_SAMPLE_TABLE (Figure 15). The scripts then insert 200,000 records into the table. After the table is created, you can run workload against it.

    Figure 15. Creating a sample table to do work against
    Screen shot showing the part of the WLM21.DDL script that creates the WLM_SAMPLE_TABLE
  2. From the CLP, enter WLM20.CMD to run the command. The command will take several minutes to complete. While it is running, you can continue with the next steps of the tutorial.

Data Studio

If you prefer, you can use a Data Studio product (such as, Data Studio Administrator) to perform the exercises that use the Command Editor. The results will be the same.

  1. Enter db2ce to start the DB2 Command Editor.

    Note: The Command Editor is part of the set of Control Center tools that have been deprecated in DB2 Version 9.7. The tools are still supported and available for use however, so rather than forcing you to first learn how to use the Data Studio products (see sidebar), the exercises in this tutorial use the Command Editor tool. You should consider moving towards use of the new suite of GUI tools in place of the Control Center tools.

  2. In the Command Editor, import the contents of the WLM30.SQL that you extracted from the WLMLab.zip download file (Figure 16). Do this by going to the menu bar and choosing Selected -> Open. By default, the file is located in the C:\COBRA_LAB_SCRIPTS\WLM directory.

    Figure 16. Command Editor after importing WLM30.SQL
    Screen shot of Command Editor after contents of WLM30.SQL have been imported

    For this exercise you will be executing each SQL statement one at a time or in groups. Be careful not to run the entire script all at once. To run individual lines of the script, highlight the lines in the Command Editor and then click the green arrow icon to run the statements.

  3. If you are not yet connected to the SAMPLE database, highlight the CONNECT statement at the top of WLM30.SQL and click on the green arrow to run it (Figure 17).

    Figure 17. Connecting to SAMPLE database
    Screen shot of Command Editor with CONNECT statement highlighted
  4. Highlight the first query under the heading #1 Workload -> service class occurrences and click on the green arrow to run it (Figure 18).

    Figure 18. Running the #1 Workload query in the Command Editor
    Screen shot of Command Editor with statements under #1Workload heading highlighted
  5. Review the results of the query by using going to the Query Results page of the Command Editor (Figure 19).

    Figure 19. Results of #1 Workload query
    Screen shot showing Query Results page after running #1 Workload query

    Notice the following about the results:

    • Because you have no real workload running, the results only show your connection to the Command Editor (or if you are using Data Studio Administrator, possibly a JDBC connection).
    • You are using the default workload SYSDEFAULTUSERWORKLOAD, which maps to the WLM_TIERS service superclass you created earlier using the new wlmtiersdefault.db2 sample script.

    In the next section you will start some real workload and see how this changes things.


Starting a first simulated workload

Follow the steps in this section to start the first simulated workload.

  1. Locate and review the WLM22.CMD, WLM28.BAT, and WLM29.DML script files that you extracted from the WLMLab.zip download file.

    WLM22.CMD is the first script you will run that simulates workload on your database. It uses the WLM environment you created earlier. WLM22.CMD connects as user db2user1 and a stored procedure sets the client information so that the client user is db2user1 (Figure 20). There are other ways to set the client information, but in this case you have to use the stored procedure because you are submitting this work through the CLP.

    Figure 20. WLM22.CMD connecting as and setting client as user db2user1
    Screen shot of the WLM22.CMD script with the lines highlighted that connect as user db2user1 and sets db2user1 as the client user

    The end of the WLM22.CMD script runs WLM28.BAT 2OO times (Figure 21).

    Figure 21. WLM22.CMD running the workload simulation script 200 times as user db2user1
    Screen shot of the WLM22.CMD script with the lines highlighted that run WLM28.BAT 200 times

    The WLM28.BAT script calls WLM29.DML (Figure 22).

    Figure 22. WLM28.BAT calling WLM29.DML
    Screen shot of WLM28.BAT script, which has one line calling WLM29.DML

    The WLM29.DML script contains the workload that is run 200 times. It consists of various utility calls such as SELECT (Figure 23), UPDATE, INSERT, DELETE, and others.

    Figure 23. WLM29.DML SELECT statements
    Screen shot of SELECT statements in part of WLM29.DML script
  2. Go to the CLP and enter WLM22.CMD to have the db2user1 user run this workload 200 times.

    Note: Make sure the WLM20.CMD script you ran in the previous section has completed before you attempt to run WLM22.CMD.

    Once you start WLM22.CMD, the CLP window is tied up as it runs the workload 200 times as user db2user1 (Figure 24).

    Figure 24. db2user1 running a workload 200 times
    Screen shot of CLP indicating that user db2user1 is in process of running a workload 1 of 200 times
  3. Return to the Command Editor, run the #1 Workload -> service class occurrences query, and review the Query Results page (Figure 25).
    Figure 25. Results from second run of #1 Workload query
    Screen shot of Query Results highlighting db2user1 user mapped to CLP_Workload_User1 workload

    Notice that the db2user1 user is mapped to the CLP_Workload_User1 workload. This is because when you created the workload with the WLM04.DB2 script, you used the wildcard feature on the CLIENT_USERID connection attribute as follows:
    CURRENT CLIENT_USERID('db2user*')

  4. From the Command Editor, run the #2 Service class activity counts - In-memory statistics function query and review the Query Results page (Figure 26).
    Figure 26. Results from #2 Service class activity counts query
    Screen shot of Query Results highlighting activities completed for CLP_Serv_User

    The CLP_Workload_User1 workload is assigned to the CLP_Serv_User superclass, so the total activities completed for CLP_Serv_User represents the activities being run by the WLM22.CMD script. (The actual numbers in your results will likely be different than that shown in the figure.)

    The total activities for the WLM_TIERS service superclass represents any SQL you are running from the Command Editor (or in Data Studio). This is because your default workload maps to the WLM_TIERS service superclass and you signed into the Command Editor as a user assigned to the default workload. Each time you run a query in the Command Editor, this activity number goes up.

Note: Keep the CLP window that is running the WLM22.CMD script open and running for the remainder of the tutorial. Do not close it.


Starting a second simulated workload

Follow the steps in this section to start the second simulated workload.

  1. Locate and review the WLM23.CMD script file that you extracted from the WLMLab.zip download file. WLM23.CMD is the second script you will run to simulate workload on your database. It runs the same 200 loop workload as WLM22.CMD, but with a different userid. WLM23.CMD runs with the userid db2cobra (Figure 27), which is the userid you are logged in with.

    Figure 27. WLM23.CMD simulates another workload with the current logged-in user id of db2cobra
    Screen shot of the part of WLM23.CMD script logging in as db2cobra
  2. Open a second CLP window and enter WLM23.CMD to run this command that starts the second simulated workload. You should now have two CLP windows running simulated workloads (Figure 28).

    Figure 28. Two CLP windows running simulated workloads
    Screen shot showing two CLP windows running simulated workloads as two different users: db2user1 and db2cobra

    Because some time elapsed between when you started the first and second workloads, the number of runs for the db2cobra user will be less than the runs for the db2user1 user.

  3. Return to the Command Editor, run the #1 Workload -> service class occurrences query, and review the Query Results page.

    The results now include the db2cobra userid, which is mapped to the CLP_Workload_Admin workload. This is because when you created the workload with the WLM04.DB2 script, you explicitly specified db2cobra (the user you are currently logged in as) on the CLIENT_USERID connection attribute as follows:
    CURRENT CLIENT_USERID('db2cobra')

  4. From the Command Editor, run the #2 Service class activity counts query again and review the Query Results page (Figure 29).

    Figure 29. Results from #2 Service class activity counts query
    Screen shot of Query Results showing CLP_Serv_Admin activities split between CLP_Serv_Admin_HI and CLP_Serv_Admin_Med subclasses

    Notice that some activities for the CLP_Serv_Admin superclass map to the CLP_Serv_Admin_HI subclass while others map to the CLP_Serv_Admin_MED subclass. This is because of the way you defined the work action set for CLP_Serv_Admin with the WLM04.DB2 script. The work action set maps write actions to the CLP_Serv_Admin_HI subclass and read actions to the CLP_Serv_Admin_MED subclass. The next step shows this in more detail.

  5. From the Command Editor, run the #3 Work Action Set Activity Count - In-memory statistics function query and review the Query Results page (Figure 30).

    Figure 30. Results from #3 Work Action Set Activity Count query
    Screen shot of Query Results highlighting different activity levels for Read_Work and Write_Work classes

    You can now see the differentiation of activities between the Read_Work and Write_Work classes of the Admin_Actions work action set. The results also show how activities from other workload and service classes are being mapped to other work action sets.

Note: Keep the second CLP window that is running the WLM23.CMD script open and running for the remainder of the tutorial. Do not close it.


Starting a third simulated workload

Follow the steps in this section to start the third simulated workload.

  1. Locate and review the WLM24.CMD script file that you extracted from the WLMLab.zip download file. WLM24.CMD is the third script you will run to simulate workload on your database. It runs the same 200 loop workload as WLM22.CMD and WLM23.CMD but with a third userid. WLM24.CMD runs with the userid db2default.

  2. Open a third CLP window and enter WLM24.CMD to run this command that starts the third simulated workload. You should now have three CLP windows running simulated workloads as three different users (Figure 31).

    Figure 31. Three CLP windows running simulated workloads
    Screen shot showing three CLP windows running simulated workloads as three different users: db2user1, db2cobra, and db2default
  3. From the Command Editor, run the #1 Workload -> service class occurrences query again and review the Query Results page.

    The results now include the db2default userid, which is mapped to the default workload SYSDEFAULTADMWORKLOAD that you created with the WLM04.DB2 script.

  4. From the Command Editor, run the #2 Service class activity counts query again and review the Query Results page (Figure 32).

    Figure 32. Results from third #2 Service class activity counts query
    Screen shot of Query Results showing user db2default using WLM_TIERS

    Activities for the db2default user are mapping to the WLM_SHORT service subclass of the WLM_TIERS service superclass that you defined with the wlmtiersdefault.db2 script. The WLM_SHORT service subclass is starting to get many more activities completed because the db2default user is now also completing most of its work in that service subclass.

    It is possible that you may also see some activities aborted. This is because those activities are locking against other activities and timing out.

  5. From the Command Editor, run the #4 long query to test activity remapping query.

    The query is purposefully written to be inefficient so that it takes longer than 5 seconds to complete. This is greater than the threshold defined in the wlmtiersdefault.db2 script, so the activity is remapped from the WLM_SHORT service subclass to the WLM_MEDIUM service subclass.

  6. After the #4 long query to test activity remapping is complete, run the #2 Service class activity counts query again and review the Query Results page (Figure 33).

    Figure 33. Results from #2 Service class activity counts query
    Screen shot of Query Results showing one activity remapped from the WLM_SHORT service subclass to WLM_MEDIUM

    The results should show that one activity was remapped from the WLM_SHORT to the WLM_MEDIUM service subclass. The remap happened because the activity reached the WLM_SHORT threshold. This is a new feature in DB2 9.7.

Note: Keep the third CLP window that is running the WLM24.CMD script open and running for the remainder of the tutorial. Do not close it.


Working with event monitors

Follow the steps in this section to learn about using the WLM event monitors.

  1. From the Command Editor, highlight and run the CALL WLM_COLLECT_STATS() stored procedure (Figure 34).

    Figure 34. Stored procedure to collect event monitor statistics
    Screen shot of Command Editor with the line CALL WLM_COLLECT_STATS() highlighted
  2. To see what effect the CALL WLM_COLLECT_STATS() stored procedure had on your in-memory statistics, go to the Command Editor, run the #2 Service class activity counts query again, and review the Query Results page.

    Notice that the procedure reset your in-memory counters to 0.

  3. To see what effect the CALL WLM_COLLECT_STATS() stored procedure had on your active WLM event monitors, go to the Command Editor, run the #5 Workload event monitor summary table select query, and review the Query Results page.

    The results show the first event monitor table WLSTATS_DB2STATISTICS and indicate that the stored procedure wrote the current statistics to all of your active WLM event monitors.

  4. From to the Command Editor, run the #6 Workload aggregate event monitor summary table select query, and review the Query Results page (Figure 35).

    Figure 35. Results from #6 Workload aggregate event monitor summary table select query
    Screen shot of Query Results showing aggregate workload statistics for two workloads

    This query demonstrates the new DB2 9.7 function that enables you to accumulate aggregate activity data at the workload level. Notice that the results show aggregate information for only two workloads. This is because these were the only two workloads you defined to collect these kinds of statistics when you created your WLM environment with the WLM04.DB2 script (Figure 36).

    Figure 36. Defining aggregate activity accumulation for workloads in WLM04.DB2 - defined
    Screen shot of WLM04.DB2 script highlighting the part of workload definitions that enable accumulation of aggregate activity
  5. From the Command Editor, run the #7 Service Class event monitor summary table select query and review the Query Results page.

  6. From the Command Editor, run the #8 Work Action Set event monitor summary table select query and review the Query Results page.

  7. From the Command Editor, run the #9 Threshold violations event monitor summary table select query and review the Query Results page (Figure 37).

    Figure 37. Results from #9 Threshold violations event monitor summary table select query
    Screen shot of Query Results showing threshold violation in event monitor table leading to remap actions

    The query shows a threshold violations event monitor where remap actions have been taken. This is a new function for DB2 9.7.


Working with the db2pd utility

Follow the steps in this section to learn about using the db2pd utility and full table queries.

  1. Open a fourth CLP window and enter WLM10.CMD to run the command that calls the db2pd utility. Open the WLM10_OUTPUT.TXT file to review the output from the db2pd utility (Figure 38).

    Figure 38. Output from WLM10.CMD currently showing workload statistics
    Screen shot showing WLM10_OUTPUT.TXT with workload statistics

    WLM10.CMD is the same command you ran earlier while setting up your tiered service class. At that time, WLM10_OUTPUT.TXT indicated that you had no workload. Contrast that with the statistics you see now.

  2. Return to your Command Editor and run the queries provided at the end of the WLM30.SQL file (Figure 39) and review the Query Results page for each. These provide you an opportunity to see results from full table queries.

    Figure 39. Full table queries at end of WLM30.SQL
    Screen shot of part of WLM30.SQL script highlighting the full table queries at the end of the file

Conclusion

The DB2 9.7 LUW Workload Manager is a powerful, easy-to-use, and easily customizable feature that you can use in your own environment to ensure that you meet service level agreements and other executive and user requirements. It allows you to control the usage of complex database workloads without changing your program code. This tutorial showed you how to take advantage of many of the new features in DB2 9.7 for LUW, such as service class priority aging, connection attribute wild cards, address connection attributes, and service class buffer pool I/O priority.


Download

DescriptionNameSize
WLM Lab ScriptsWLMLab.zip21KB

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=419836
ArticleTitle=DB2 9.7: Using new Workload Manager features
publish-date=08132009