Resource Classes

In XPS 8.50 and earlier releases, all PDQ queries, irrespective of the database on which they operate, can allocate from the common pool of memory that is specified as DS_TOTAL_MEMORY. This allows for the possibility of some of the queries using all the memory while they are running, forcing other queries to overflow to disk or wait until the memory becomes available. (In this chapter, the term query includes any DML operation that consumes shared memory resources, including DELETE, INSERT, or UPDATE statements of SQL that include a SELECT clause.)

This new resource class feature of Extended Parallel Server 8.51 provides DBAs with better control over the resource utilization on a per database level, through the ability to partition shared memory among databases, so that memory can be available on a database for certain queries, even while resource intensive queries are running on another database.

The basis of this functionality is the construct of a resource class. A DBA can partition of the shared memory among various resource class instances by setting ONCONFIG parameters that define resource classes. The DS_CLASS session environment variable can be set in the SET ENVIRONMENT statement, producing the following restrictions on the PDQ memory available to the current session:

• associate a user session with a specific resource class
• determine what portion of shared memory of the database server is available to a query
• define how many queries accessing that memory can be run concurrently.

Any database or user can be associated with a resource class at connection time by including a SET ENVIRONMENT DS_CLASS statement in a sysdbopen( ) procedure.

More generally, a user session can be associated with a resource class in any of several ways:

• By default, if the session is associated with the built-in "DEFAULT" class, as described later in this section
• By including the SET ENVIRONMENT DS_CLASS ds_class_name statement within a sysdbopen( ) procedure
• By executing SET ENVIRONMENT DS_CLASS ds_class_name during the session, before running a query
• By setting the DS_CLASS option of the IBM_XPS_PARAMS environment variable in the shell to some existing resource class before starting the session.

If the DBA specifies that DS_CLASS is a MUTABLE session attribute, and more than one resource class is defined for the database server, then any user can reset the current session to a different resource class. When DS_CLASS is an IMMUTABLE session attribute, however, the DBA can use user.sysdbopen( ) or public.sysdbopen( ) procedures (or both) to specify a resource class for the session at connection time, restricting the resources that are available to specific users or to PUBLIC.

With this feature, the DBSA can define resource classes and partition resources among up to eight (8) of these named classes. Every user session is associated with an explicit or default resource class. By reserving a specified proportion of total memory for the resource classes of sessions that execute critical operations, the DBSA or DBA can reduce the risk that insufficient resources will be available during critical DML operations. The Resource Grant Manager (RGM) remains the mechanism for allocating database server resources to queries, but to restrict the memory resources that a user session can consume, this feature specifies limits for each resource class, rather than for the database server.