Example: Using service classes

The following example shows how to use service classes to control database workload.

This example occurs in the fictitious International Beer Emporium. International Beer Emporium is a medium-sized business made up of five major departments: Sales, Accounting, Engineering, Testing and Production. All five departments share the same product catalog database.

Initial implementation of a Db2 workload management solution

The product catalog database runs well most of the time. However, sometimes users complain that their applications cannot connect to the database because the maximum number of connections has been exceeded. After upgrading to Db2® Version 9.7, Bob, the database administrator, decides to try service classes. Bob wants to know the usage patterns of the product catalog database by each of the five departments and figure out why his database runs out of connections occasionally. Following are the steps Bob follows to set up the service classes:

First, Bob creates service superclasses for each of the departments (the default service subclass is also automatically created for each service superclass):
- SALES is created for the Sales department:
```
CREATE SERVICE CLASS SALES
```
- ACCOUNTING is created for the Accounting department:
```
CREATE SERVICE CLASS ACCOUNTING
```
- ENGINEERING is created for the Engineering department:
```
CREATE SERVICE CLASS ENGINEERING
```
- TESTING is created for the Testing department:
```
 CREATE SERVICE CLASS TESTING
```
- PRODUCTION is created for the Production department:
```
CREATE SERVICE CLASS PRODUCTION
```
Bob creates session user groups with appropriate authorization IDs for each of the departments:
- A session user group is created with the authorization ID SALESGRP. This group includes the authorization IDs of all users in the Sales department.
- A session user group is created with the authorization ID ACCTNGRP. This group includes the authorization IDs of all users in the Accounting department.
- A session user group is created with the authorization ID ENGINGRP. This group includes the authorization IDs of all users in the Engineering department.
- A session user group is created with the authorization ID TESTGRP. This group includes the authorization IDs of all users in the Testing department.
- A session user group is created with the authorization ID PRODGRP. This group includes the authorization IDs of all users in the Production department.
Bob creates workloads to map connections from each group to the associated service class:
- Workload WL_SALES is created with its session user group set to SALESGRP. WL_SALES maps its connections to the service superclass SALES:
```
CREATE WORKLOAD WL_SALES SESSION_USER GROUP ('SALESGRP')
SERVICE CLASS SALES
```
- Workload WL_ACCOUNTING is created with its session user group set to ACCTNGRP. WL_ACCOUNTING maps its connections to the service superclass ACCOUNTING:
```
CREATE WORKLOAD WL_ACCOUNTING SESSION_USER GROUP ('ACCTNGRP')
SERVICE CLASS ACCOUNTING
```
- Workload WL_ENGINEERING is created with its session user group set to ENGINGRP. WL_ENGINEERING maps its connections to service class ENGINEERING:
```
CREATE WORKLOAD WL_ENGINEERING SESSION_USER GROUP ('ENGINGRP')
SERVICE CLASS ENGINEERING
```
- Workload WL_TEST is created with its session user group set to TESTGRP. WL_TEST maps its connections to service class TESTING:
```
CREATE WORKLOAD WL_TEST SESSION_USER GROUP ('TESTGRP')
SERVICE CLASS TESTING
```
- Workload WL_PRODUCTION is created with its session user group set to PRODGRP. WL_PRODUCTION maps its connections to service class PRODUCTION:
```
CREATE WORKLOAD WL_PRODUCTION SESSION_USER GROUP ('PRODGRP')
SERVICE CLASS PRODUCTION
```

Bob uses the default service class and workload settings. He wants to observe the database usage patterns before placing any controls on the service classes. The resulting service superclass definitions are as follows:

SALES
ACCOUNTING
ENGINEERING
TESTING
PRODUCTION
SYSDEFAULTUSERCLASS
SYSDEFAULTMAINTENANCECLASS
SYSDEFAULTSYSTEMCLASS

With a Db2 workload management solution implemented as described previously, work from each department is routed to its own service superclass. Work from departments not specifically accounted for is mapped to the SYSDEFAULTUSERCLASS default service superclass. Using this configuration, Bob can monitor the work in each of the service classes to determine the database usage pattern of the departments.

First refinement of the Db2 workload management implementation

Following the most recent connection spike, Bob queries service superclass statistics using the WLM_GET_SERVICE_SUPERCLASS_STATS table function and examines the connection high-water mark value for each service superclass. Bob discovers that the connection high-water mark for all departments except Testing is close to 100. However, the statistic for the Testing department shows that at one time, the test team established over 800 connections

Once a month, the Testing department performs its monthly intensive product testing. At this time, the department establishes up to 1000 concurrent connections. Because the database manager configuration parameter max_connections is set to 1000, the Testing department uses most of the available connections to the database. When the system has 1000 connections, all subsequent connections are rejected.

Because of memory constraints on the system, the max_connections and maxagents configuration values cannot be increased on the data server to permit more connections.

To prevent the Testing department from using all the connections, Bob decides to limit the number of connections from the Testing department and ensure that each of the other four departments can obtain sufficient connections to the database to meet their business objectives.

The other four departments ordinarily do not require more than 150 concurrent connections each. In addition, Bob also notices that the default user, default maintenance, and default system service superclasses rarely contain any connections, so he decides that 100 connections should be sufficient for these default service superclasses. After 700 connections (600 for the four departments and 100 for the default classes) are allocated from the max_connections pool of 1 000 available connections, 300 connections are available for the Testing department. By limiting the Testing department to a maximum of 300 connections, users from other departments should not have their connection requests rejected.

To limit the Testing group to a maximum of 300 concurrent connections, Bob creates a MAXSERVICECLASSCONNECTIONS threshold of 300 for the TESTING service class.

CREATE THRESHOLD MAXSERVICECLASSCONNECTIONS FOR SERVICE CLASS TESTING ACTIVITIES
ENFORCEMENT DATABASE PARTITION
WHEN TOTALSCMEMBERCONNECTIONS > 300 STOP EXECUTION

After implementing this change, theDb2 workload management configuration is as follows:

Table 1. Configuration after adding threshold for the TESTING service superclass
Service class	MAXSERVICECLASSCONNECTIONS threshold
SALES	N/A
ACCOUNTING	N/A
ENGINEERING	N/A
TESTING	300
PRODUCTION	N/A
SYSDEFAULTUSERCLASS	N/A
SYSDEFAULTMAINTENANCECLASS	N/A

Because the TESTING service class can contain a maximum of only 300 concurrent connections, all connection requests above this threshold are rejected. A MAXSERVICECLASSCONNECTIONS threshold is not applied on the other service classes, so these service classes share the remaining 700 available connections to the data server. Because there is no contention for connections among these service classes, Bob does not place connection thresholds on them.

Second refinement of the Db2 workload management implementation

Although connections from the Sales, Accounting, Engineering, and Production departments are no longer being rejected, users from these departments still complain about poor performance when the Testing department performs intensive product testing. Bob examines the queries that the Testing department runs during its product test cycle and discovers that the queries contain complicated joins that involve large amounts of data. These queries generate considerable prefetch activity, which prevents connections from other departments having their prefetch requests processed. Bob decides to lower the prefetch priority of the connections from the Testing department and alters the TESTING service class to set its prefetch priority to LOW:

ALTER SERVICE CLASS TESTING PREFETCH PRIORITY LOW

The Db2 workload management configuration is as follows:

Table 2. Configuration after changing prefetch priority for the TESTING service superclass
Service class	MAXSERVICECLASSCONNECTIONS threshold	Prefetch priority
SALES	N/A	DEFAULT
ACCOUNTING	N/A	DEFAULT
ENGINEERING	N/A	DEFAULT
TESTING	300	LOW
PRODUCTION	N/A	DEFAULT
SYSDEFAULTUSERCLASS	N/A	DEFAULT
SYSDEFAULTMAINTENANCECLASS	N/A	DEFAULT

Setting the prefetch priority of the TESTING service class to LOW causes prefetch requests from connections issued from the Testing department to be serviced only after all prefetch requests from the other departments are processed. This change increases the query throughput of the other departments and decreases the throughput of the Testing department during its product testing phase.

Third refinement of the Db2 workload management implementation

After the prefetch problem is resolved, the Engineering department tells Bob that it needs a few connections for an experimental application called Brewmeister. Because the application is experimental, Bob wants to ensure that it does not consume too many database connections and that queries from the application will not compete for prefetchers when the system is busy. To accomplish these objectives, he creates a new service subclass under the ENGINEERING service superclass for the experimental application and a workload to map connections from the application to the new service subclass. Bob updates the service class and workloads as follows:

Service subclass EXPERIMENT is created under the service superclass ENGINEERING:
```
CREATE SERVICE CLASS EXPERIMENT UNDER ENGINEERING
```

Threshold MAXSERVICECLASSCONNECTIONS of 50 is created for the service subclass EXPERIMENT:

CREATE THRESHOLD MAXSERVICECLASSCONNECTIONS FOR SERVICE CLASS EXPERIMENT
UNDER ENGINEERING ACTIVITIES
ENFORCEMENT DATABASE WHEN TOTALMEMBERCONNECTIONS > 50 STOP EXECUTION

Workload WL_EXPERIMENT is created to map connections from the application BREWMEISTER to the service subclass EXPERIMENT:
```
CREATE WORKLOAD WL_EXPERIMENT APPLNAME ('BREWMEISTER') SERVICE CLASS EXPERIMENT
UNDER ENGINEERING
```
The prefetch priority for the EXPERIMENT service subclass is set to LOW:
```
ALTER SERVICE CLASS EXPERIMENT UNDER ENGINEERING PREFETCH PRIORITY LOW
```

The Db2 workload management configuration is as follows:

Table 3. Configuration with EXPERIMENT service subclass
Service class	MAXSERVICECLASSCONNECTIONS threshold	Prefetch priority
SALES	N/A	DEFAULT
ACCOUNTING	N/A	DEFAULT
ENGINEERING	N/A	DEFAULT
EXPERIMENT	50	LOW
TESTING	300	LOW
PRODUCTION	N/A	DEFAULT
SYSDEFAULTUSERCLASS	N/A	DEFAULT
SYSDEFAULTMAINTENANCECLASS	N/A	DEFAULT

With this configuration, the BREWMEISTER application can only maintain 50 concurrent connections to the database. In addition, prefetch requests from this application are sent to the low priority prefetch queue. The Engineering department can now safely experiment with the application, knowing that it cannot accidentally overwhelm the database system.