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

Read syntax diagramSkip visual syntax diagramWLM_ENABLE_ADMISSION_CTRL(newEnv )

Parameters

newEnv
An optional input argument of type VARCHAR(3) . If ‘YES’, NULL or no parameter is passed in, preparation for the enablement of the adaptive workload manager will be initiated. If ‘NO’ is passed in, preparation for the disablement of the adaptive workload manager will be initiated. A deactivation and then reactivation of the database is required to complete the process.

Usage notes

The adaptive workload manager can only be enabled on databases where the registry variable DB2_WORKLOAD has been set to ANALYTICS and sheapthres_shr is not set to AUTOMATIC.

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'.
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'.
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® Warehouse 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.