Altering a service class

If you want to change a service class definition, use the ALTER SERVICE CLASS statement.

Before you begin

To alter a service class, you require SQLADM, WLMADM, or DBADM authority. To specify any clause other than a COLLECT clause, the authorization ID must include WLMADM or DBADM authority.

See Workload management DDL statements for more information about prerequisites.

About this task

Activities that have already acquired resources and are running are usually not affected by the ALTER statement. These activities will hold their resources and run until completion. (Note that changes to the CPU shares or CPU limits of a service class that are made through the ALTER SERVICE CLASS statement take effect immediately, even in the case of activities that are already running.) However, if a subagent request is sent to a remote member during the ALTER SERVICE CLASS operation, the service class definition seen by the coordinator agent and the subagent can differ. Consider the following example in which the prefetch priority for the service class is initially set to MEDIUM:
Table 1. Differences between the views of a coordinator agent and subagent of an altered service class
Event order Connection 1 Connection 2
1 Coordinating agent sends a request to remote member (prefetch priority of service class was previously set to MEDIUM)  
2   ALTER SERVICE CLASS issued; set prefetch priority to HIGH
3   COMMIT is issued (the altered service class property is committed at the catalog member and loaded to memory at all database members)
4 Remote subagent receives the request. At this time, the subagent sets the new prefetch priority of HIGH for the service class definition  
This situation described in the previous table is temporary, and only affects connections that issue subagent requests during the ALTER SERVICE CLASS operation. All new connections will see the updated service class definition with the prefetch priority of HIGH.

Procedure

To alter a service class:

  1. Specify one or more of the following properties for the service class on the ALTER SERVICE CLASS statement:
    • Specify whether the service class is enabled or disabled. If you change a service class from enabled to disabled, existing connections or activities remain with the service class and continue to use previously allocated resources until complete. You can disable a service class if the work coming to the service class is overwhelming the system, or you want to reject all work coming to the service class.
      When a service superclass is disabled the following happens:
      1. The service superclass is disabled.
      2. Its service subclasses are disabled.
      The service subclasses are only disabled while their service superclass is disabled. When the service superclass is enabled, the service subclasses return to their previous states as defined in the catalog table.

      When a service subclass is disabled, its service superclass is not affected, nor other service subclasses associated with the service superclass.

      You cannot explicitly disable a default service subclass. To prevent new requests from running under a default service subclass, you must disable the associated service superclass.

    • Specify the prefetch priority. You can specify the priority with which agents in the service class can submit their prefetch requests. Depending on the value specified, the prefetch requests are routed to the high, medium, or low priority prefetch queues. The default prefetch priority is medium. If the prefetch priority is altered after a prefetch request is submitted, the request will not change its priority.
    • Specify the buffer pool priority for the service class which affects how likely pages fetched by activities in the service class are to be swapped out. For service superclasses, the DEFAULT value internally maps to LOW. Service subclasses set to DEFAULT inherit the buffer pool priority from their parent superclasses.
    • Specify the outbound correlator string if you want to associate the Db2® service class with an AIX® class or a Linux® class. A null value indicates no operating system workload manager association.

      If the outbound correlator is changed from a non-null value to a null value, all threads in the Db2 service class will disassociate with the operating system workload manager when the next activity begins.

      If the outbound correlator is set to NONE for a service subclass and the outbound correlator is specified for the associated service superclass, the service subclass inherits the outbound correlator specified for its service superclass.

    • Specify the adding or dropping of a system temporary table space to or from the list of preferred temporary table spaces for the specified service subclass.
    • Specify the activity data to collect. When activity data collection is enabled, information about an activity is sent from the coordinator member to the applicable event monitor at the end of the activity. You can write data to the event monitor that includes information about the statement that was run, its compilation environment, and any applicable input data values. You can also specify that no activity data is collected. By default, no activity data is collected.
    • Collected aggregate activity information. The aggregate activity information used for the service class only changes after the ALTER SERVICE CLASS operation is committed.
    • The monitoring request metrics collection level for requests submitted by connections mapped to a subclass under the specified service superclass. Note that the effective collection setting for requests running under a service superclass is the combination of both the service class collection level and the mon_req_metrics database configuration parameter.
    • Whether to alter the histogram templates used by a service subclass that has enabled aggregate activity data collection using COLLECT AGGREGATE ACTIVITY DATA or aggregate request data collection using COLLECT AGGREGATE REQUEST DATA. Updating the histogram templates used by a service subclass will update the corresponding rows in the SYSCAT.HISTOGRAMTEMPLATEUSE view which displays the histogram templates referenced by a service class or work action. For more information about histograms and histogram templates, see Histograms in workload management.
  2. Commit your changes.
    When you commit your changes the service class is updated in the SYSCAT.SERVICECLASSES view.