WLM_ENABLE_ADMISSION_CTRL procedure - Enabling the adaptive workload manager

The WLM_ENABLE_ADMISSION_CTRL procedure is used to enable or disable the adaptive workload manager on a connected database.

Authorization

One of the following authorities is required to execute the routine:

DBADM authority
WLMADM authority

Default PUBLIC privilege

None

Syntax

There are two valid syntax to run WLM_ENABLE_ADMISSION_CTRL. The difference between the two invocations is an additional parameter adapativeWLMEnabled that allows user to disable admission control.

Method 1:

Method 2:

Parameters

newEnv

An optional input argument of type VARCHAR(3) . Accepts the following options 'YES', 'NO' or NULL. If 'YES' is passed in, preparation for the enablement of the adaptive workload manager is initiated. If 'NO' is passed in, preparation for the disablement of the adaptive workload manager is initiated. If NULL is passed in, the default is 'YES'.

adapativeWLMEnabled (Only for method 2)

An optional input argument of type VARCHAR(3). Accepts the following options 'YES', 'NO' or NULL. If 'YES' is passed in, it enables adaptive workload manager. If 'NO' is passed in, it disables adaptive workload manager. A deactivation and then a reactivation of the database is required to complete the process. Default value is 'YES' if newEnv is set to 'YES' and 'NO' if newEnv is set to 'NO'.

Note: We do not allow WLM_ENABLE_ADMISSION_CTRL ('NO', 'YES') as preparation to disable adaptive workload manager as it disallows us to enable it. In this case, the WLM_ENABLE_ADMISSION_CTRL ('NO', 'YES') scenario fails with error message SQL0171.

Usage notes

The adaptive workload manager can only be enabled on databases where sheapthres_shr or sortheap is not set to AUTOMATIC.. If these conditions are not satisfied the stored procedure will fail with SQL1668, reason code 12.

After invocation of the WLM_ENABLE_ADMISSION_CTRL stored procedure the database must be reactivated before any changes take effect. Until the database is reactivated, no changes are made to the active database.

If the stored procedure is used to enable the adaptive workload manager the following changes will occur when the database is next reactivated:

The SYSDEFAULTMANAGESUBCLASS service subclass is dropped. Any WORKLOAD objects pointing to the SYSDEFAULTMANAGEDSUBCLASS will be modified to point to the SYSDEFAULTUSERCLASS service superclass. Any monitor settings defined on this service class (for example activity collection) are implicitly removed since the subclass is dropped and must be manually redefined on the newly created service subclasses if required.
SYSMANAGEDQUERIES work class in the SYSDEFAULTUSERWCS work class set is dropped.
SYSMAPMANAGEDQUERIES work action in the SYSDEFAULTUSERWAS work action set is dropped.
The SYSDEFAULTCONCURRENT threshold is dropped
The SYSMEDIUMSUBCLASS, SYSCOMPLEXSUBCLASS and SYSLOAD subclasses under the SYSDEFAULTUSERCLASS superclass are created.
The SYSREMAPTOMEDIUM and SYSREMAPTOCOMPLEX ACTIVITYTOTALRUNTIMEINALLSC thresholds are created.
The SYSSIMPLEDML,SYSMEDIUMDML,SYSCOMPLEXDML & SYSLOAD work classes added to the SYSDEFAULTUSERWCS work class set.
The SYSMAPCOMPLEXDML, SYSMAPMEDIUMDML, SYSMAPSIMPLEDML & SYSMAPLOAD work actions are added to SYSDEFAULTUSERWAS work action set.
The wlm_admission_ctrl database configuration parameter is set to 'YES' unless adapativeWLMEnabled parameter is set to 'NO'.
Note: If you set either sortheap or sheapthres_shr to automatic, system fails with -1668 reason code 12.

If the stored procedure is used to disable the adaptive workload manager the following changes will occur when the database is next reactivated:

The SYSMAPSIMPLEDML, SYSMAPMEDIUMDML, SYSMAPCOMPLEXDML & SYSMAPLOAD work actions under SYSDEFAULTUSERWAS are removed.
The SYSSIMPLEDML, SYSMEDIUMDML, SYSCOMPLEXDML & SYSLOAD work classes under SYSDEFAULTUSERWCS are removed.
The SYSREMAPTOMEDIUM & SYSREMAPTOCOMPLEX thresholds are removed.
The SYSDEFAULTSUBCLASS, SYSMEDIUMSUBCLASS, SYSCOMPLEXSUBCLASS & SYSLOADSUBCLASS subclasses under SYSDEFAULTUSERCLASS are removed. Any workload objects pointing to these subclasses are moved to point to SYSDEFAULTUSERCLASS.
Create SYSDEFAULTSUBCLASS under SYSDEFAULTUSERCLASS.
Create SYSDEFAULTMANAGEDSUBCLASS service subclass under SYSDEFAULTUSERCLASS.
Create the work class SYSMANAGEDQUERIES under SYSDEFAULTUSERWCS.
Create the work action SYSMAPMANAGEDQUERIES under SYSDEFAULTUSERWAS.
Create the SYSDEFAULTCONCURRENT concurrency threshold.
The wlm_admission_ctrl database configuration parameter is set to 'NO'.
Note: You cannot set adapativeWLMEnabled as 'YES' if you are disabling the adaptive workload manager.

When enabling the adaptive workload manager on a database where it is already enabled, or disabling on a database where it is already disabled, no action will be taken and no error will be returned from the WLM_ENABLE_ADMISSION_CTRL procedure.

Note: Note: Installations of Integrated Analytics System, Db2® and Db2 Warehouse on Cloud are already running adaptive workload manager and are unable to migrate away from it as it is always running on those configurations.

Example 1:

Initiate preparation for the enablement of the adaptive workload manager. adapativeWLMEnabled is NULL and thus this call enables admission control (Method 1).

db2 "CALL WLM_ENABLE_ADMISSION_CTRL('YES')"

  Return Status = 0

Example 2:

Initiate preparation to enable the adaptive workload manager. Both newEnv and adapativeWLMEnabled are set to NULL. When newEnv is set to 'YES', default value for newEnv is 'YES' and default value for adapativeWLMEnabled is 'YES' (Method 1).

db2 "CALL WLM_ENABLE_ADMISSION_CTRL()"

  Return Status = 0

Example 3:

Initiate preparation to disable the adaptive workload manager (Method 1).

db2 "CALL WLM_ENABLE_ADMISSION_CTRL('NO')"

  Return Status = 0

Example 4:

Initiate preparation to enable the adaptive workload manager, but disable admission control (Method 2).

db2 "CALL WLM_ENABLE_ADMISSION_CTRL('YES', 'NO')"

  Return Status = 0