Service class resource assignment

Different database workloads compete for the same limited set of database resources. It might be necessary or desirable to distribute resources across different workloads to prioritize or to isolate workloads.

By default, all user queries execute in the SYSDEFAULTUSERCLASS service class, which is assigned all available resources. The default system and maintenance service classes do not participate in adaptive workload management admission control. User defined service classes can be created using the CREATE SERVICE CLASS statement, and application workloads can be routed to these service classes using the CREATE WORKLOAD statement. Any user defined service class reduces the amount of resources available to the SYSDEFAULTUSERCLASS service class.

The adaptive workload manager provides two controls on a service class to assign resources to the service class:

Resource shares: Resource shares are used to assign a resource entitlement (share of resources) to a service class. The resource entitlement can either be soft (additional work is admitted beyond the resource entitlement when there is spare capacity) or hard (no additional work might be admitted if allowing the work to run results in usage that exceeds the entitlement). Note that for soft entitlements the resource utilization might fluctuate above and below the entitlement. It is rare that the resource requirements for queries fit perfectly into the entitled resources. When soft resource entitlements are used, expect the resource usage to be both higher and lower than the entitlement, such that over time the average resource utilization trends towards the entitlement.
Minimum resource share: The minimum resource share indicates the percentage of the entitled resources that are reserved for use by the service class even when no work is present.

Use resource shares to assign a maximum amount of resources that a service class might consume under contention and the minimum resource share to assign a minimum resource allocation.

Table 1. Usage of a CREATE SERVICE CLASS or ALTER SERVICE CLASS clause
CREATE or ALTER SERVICE CLASS clause	Usage
`SOFT RESOURCE SHARES value`	Specifies the soft resource entitlement. The soft entitlement is the maximum resources allowed when resources are under competition. A soft entitlement might be exceeded when there is spare resource capacity (that is, when other service classes are not using their full resource entitlement).
`HARD RESOURCE SHARES value`	Specifies the hard resource entitlement. The hard entitlement is the maximum amount of resources allowed.
`MINIMUM RESOURCE SHARE value PERCENT`	Specifies the percentage of the entitled resources that are to be held in reserve for the service class (that is, the minimum resource allocation).

Examples

Example 1: Issue the following DDL statements to create the following service classes:
- Service class A, with entitlement to up to 50% of the resources (500 of 1000 shares) when there is not any spare capacity, and up to 100% of the resources when there is spare capacity (because the entitlement is soft)
- Service class B, with entitlement to up to 30% of the resources (300 of 1000 shares) when there is not any spare capacity, and up to 100% of the resources when there is spare capacity
- Service class C, with entitlement to at least 15% of the resources (75% of 200 shares, which is 15% of 1000 shares), up to 20% of the resources (200 of 1000 shares) when there is not any spare capacity, and up to 100% of the resources when there is spare capacity
```
CREATE SERVICE CLASS A SOFT RESOURCE SHARES 500
CREATE SERVICE CLASS B SOFT RESOURCE SHARES 300
CREATE SERVCIE CLASS C SOFT RESOURCE SHARES 200 MINIMUM RESOURCE SHARE 75 PERCENT
```
Example 2: Issue the following DDL statements to create the following service classes:
- Service class A, with entitlement to up to 25% of the resources (2500 of 10000 shares) regardless of whether there is spare capacity (because the entitlement is hard)
- Service class B, with entitlement to up to 75% of the resources (7500 of 10000 shares) regardless of whether there is spare capacity
```
CREATE SERVICE CLASS A HARD RESOURCE SHARES 2500
CREATE SERVICE CLASS B HARD RESOURCE SHARES 7500
```
Example 3: Issue the following DDL statements to create the following service classes:
- Service class A, with entitlement to up to 25% of the resources (25 of 100 shares) regardless of whether there is spare capacity (because the entitlement is hard)
- Service class B, with entitlement to up to 75% of the resources (75 of 100 shares) when there is not any spare capacity in service class A, and up to 100% of the resources when there is (because the entitlement is soft)
```
CREATE SERVICE CLASS A HARD RESOURCE SHARES 25
CREATE SERVICE CLASS B SOFT RESOURCE SHARES 75
```