Migrating workload management settings

PureData® System for Analytics offers the following workload management features:

• Guaranteed resource allocation (GRA)
• Prioritized query execution (PQE)
• Scheduler rules
• Short query bias (SQB)

Db2 Warehouse uses service classes, which are analogous to Netezza resource groups. Db2 Warehouse provides the following pre-defined service classes:

• SYSDEFAULTMAINTENANCECLASS
• SYSDEFAULTSYSTEMCLASS
• SYSDEFAULTUSERCLASS

In addition, you can issue CREATE SERVICE CLASS statements to define your own service classes.

A service class can specify the type of the workloads (mixed, interactive, or batch) for which it is to be used. Each service class can be assigned a resource share and a minimum resource share. A resource share is conceptually similar to a guaranteed resource allocation (GRA). For example, the following statement creates a service class that assigns 2000 resource shares for mixed workloads:

CREATE SERVICE CLASS SC1 FOR WORKLOAD TYPE MIXED RESOURCE SHARES 2000

You can use a workload to group similar work items, to apply thresholds, or to route work to a particular service class. You can use the console to monitor a workload. For more information about query history and workload monitoring, see Query history and workload monitoring. For example, the following statement creates a workload that runs on the service class SC1:

CREATE WORKLOAD MONTHLYSALES APPLNAME('monthlyrpt') SERVICE CLASS SC1

Use thresholds to define and enforce rules on the database to detect and control rogue queries. A threshold is conceptually similar to a scheduler rule. For example:

• Create a threshold for a workload:

  CREATE THRESHOLD FORCELONGUOW FOR WORKLOAD MONTHLYSALES ACTIVITIES ENFORCEMENT DATABASE WHEN UOWTOTALTIME > 10 MINUTES FORCE APPLICATION

• Create a threshold for a service class:

  CREATE THRESHOLD BIGQUERIESLONGRUNNINGTIME FOR SERVICE CLASS SC1 ACTIVITIES ENFORCEMENT DATABASE WHEN ACTIVITYTOTALTIME > 10 HOURS COLLECT ACTIVITY DATA WITH DETAILS AND VALUES

• Create a threshold for a database:

  CREATE THRESHOLD DBMAX1HOURRUNTIME FOR DATABASE BLUDB ENFORCEMENT DATABASE WHEN ACTIVITYTOTALTIME > 1 HOUR STOP EXECUTION

Use session priority to influence the scheduling of work within a service class. Session priority is conceptually similar to prioritized query execution in PureData System for Analytics.

• Specify the priority of all work from a particular user or application by using the PRIORITY property of a WORKLOAD object. This method is similar to specifying the priority on a resource group. For example, to identify all work that is submitted by user NEWTON as high priority, you can create the following WORKLOAD object:

  CREATE WORKLOAD PAYROLL SESSION_USER('NEWTON') PRIORITY HIGH

• Specify the priority of a particular connection by using the WLM_SET_SESSION_PRIORITY stored procedure. This method is similar to specifying a priority for an application by using the
  nzsession command or the NzAdmin tool. For example, to reduce the priority of an application with handle 2361, use the following stored procedure call:

  call SYSPROC.WLM_SET_SESSION_PRIORITY(2361, 'LOW')