Resource group example

As an example of how to use resource groups to partition system resources, imagine a system that is used by three types of users:

• Data analysts
• Users who produce query reports
• All other users

You can create a resource group for each type of user, and then either:

• Assign each user to the appropriate resource group.
• Create a scheduler rule to reassociate the plans of each user with the appropriate resource group.

You can create the analysts and rptquery groups and can alter the resource maximum of the public group by issuing the following nzsql commands:

CREATE GROUP analysts WITH RESOURCE MINIMUM 50;
CREATE GROUP rptquery WITH RESOURCE MINIMUM 30 RESOURCE MAXIMUM 60;
ALTER  GROUP public   WITH RESOURCE MAXIMUM 80;

You can then either:

• Assign user accounts to each resource group. For example, the following command assigns the user bob to the analysts resource group:

  ALTER USER bob IN RESOURCEGROUP analysts;

• Create a scheduler rule to reassociate the plans of each user with the appropriate resource group. For example, the following command creates a rule that causes all plans for jobs submitted by bob to be assigned to the analysts resource group:

  CREATE SCHEDULER RULE bob1 AS IF USER IS bob THEN EXECUTE AS
    RESOURCEGROUP analysts;

When all three resource groups are running jobs on the system, the GRA scheduler works to balance resource utilization as shown in Figure 1:

A

User requests

B

Request queues

C

Minimum resource guarantees