Using workload types to simplify service class creation

To prioritize one application over another or protect an application from interference by others (for example, in noisy neighbor type scenarios), it is necessary to separate out the resources used by different applications. To do this, you can create and route work to one or more service classes and configure the resources assigned to those service classes. Service class creation can be complex, involving creation and configuration of one or more subclasses (for example, to further differentiate work from an input source by size or type). The WORKLOAD TYPE clause of a CREATE SERVICE CLASS statement simplifies the creation of service superclasses that are preconfigured for certain types of work.

When the WORKLOAD TYPE clause is used, one or more service subclasses are automatically created along with any work class set and work action sets required to map work to the subclasses. The subclasses are preconfigured with an appropriate resource share, minimum resource share, and admission queue order. This lets you work entirely at the superclass level, leaving it to the database manager to handle the creation and management of any additional child objects such as subclasses. You need to specify only the workload type, resource shares, and, if needed, a minimum resource share for the service superclass.

The following workload types can be used for service superclasses:

MIXED: The service superclass is optimized for a mix of statements of a variety of sizes and resource requirements. The superclass is preconfigured with multiple subclasses to ensure more complicated, resource intensive work does not hold up simpler, shorter work.
BATCH: The service superclass is optimized for a batch workload consisting of larger pieces of work where there is a higher tolerance for latency.
INTERACTIVE: The service superclass is optimized for an interactive workload consisting of short queries requiring a high degree of responsiveness.
CUSTOM: The service class has the default configuration. The user is expected to create/modify additional child objects as necessary.

Creating a service superclass for a specified workload type results in the creation of child objects with certain pre-configured settings. If any of these settings do not meet your needs, you can modify them. Similarly, dropping a service superclass automatically drops any its child objects.

Note that the workload type influences only the scheduling behavior of a service superclass, for example, by subdividing work across different subclasses, or by using different queue orderings. There is no implicit prioritization based on the workload type. The resource priority of a service class is dictated only by the resource shares that are assigned to that service class. For example, if you have two service superclasses of workload type INTERACTIVE, where one has 1000 resource shares, and another has 500 resource shares, the service class with 1000 shares has greater access to the resources, because it has twice the resource entitlement of the other service class. Similarly, if you wish to prioritize batch work, you can assign a service superclass of workload type BATCH a higher share of resources than a service superclass of workload type INTERACTIVE.

Example

You want to create two service classes:

The service class MIXEDSC is to be optimized for a reporting application, and is to have 70% of the resources.
The service class BATCHSC is to be optimized for batch work, and is to have 30% of the resources. Because you do not want the work of BATCHSC to affect the queries of MIXEDSC, you use hard shares for BATCHSC.

The following statements create these service classes:

CREATE SERVICE CLASS MIXEDSC FOR WORKLOAD TYPE MIXED SOFT RESOURCE SHARES 7000
CREATE SERVICE CLASS BATCHSC FOR WORKLOAD TYPE BATCH HARD RESOURCE SHARES 3000

The following statements route work from user REPORTS to the mixed service class and work from user BACKGROUND to the batch service class.

CREATE WORKLOAD MIXEDWL SESSION_USER(‘REPORTS’) SERVICE CLASS MIXEDSC
CREATE WORKLOAD BATCHWL SESSION_USER(‘BACKGROUND’) SERVICE CLASS BATCHSC