ALTER WLM PROFILE ​

Make a workload management (WLM) profile active, change its default pool, or rename it.

ALTER WLM PROFILE name
( DEFAULT_POOL name ) |
ACTIVATE [ seconds ] [ WITH CANCEL | WITHOUT CANCEL ] |
RENAME TO name

Only superusers and users who have been granted CONTROL WLM ON SYSTEM can create, alter, and drop WLM profiles.

PROFILE name

The profile name must match the name of an existing profile in the database.

( DEFAULT_POOL name )

Change the default pool for the named profile. This option must be enclosed in parentheses. The named pool may be an existing resource pool or a resource pool that has not yet been created. However, if the named default pool does not exist, you cannot activate the profile.

ACTIVATE [ seconds ]

When you activate a profile, the currently active profile is deactivated. You can activate a profile immediately, or after an interval specified as a number of seconds. If you activate immediately, any running queries are cancelled. If you specify an interval, active queries have some time to complete before the new profile is activated.

Note: The target of this operation is the current compute cluster, which must be running when the ACTIVATE command is run. This command does not persist between cluster restarts and will fail if the target cluster is suspended. See also Creating WLM Profiles.

WITH CANCEL | WITHOUT CANCEL

WITH CANCEL (the default): Cancel any running queries when the activation timeout expires. WITHOUT CANCEL: cancel the activation itself if queries are still running when the timeout expires. For example, activate 10 with cancel means that the system will wait for up to 10 seconds before cancelling queries, then it will activate the profile. However, activate 10 without cancel means that if queries are not complete after 10 seconds, the activation will fail.

RENAME TO name

Rename a profile.

Examples ​

For example, activate a profile immediately:

premdb=# alter wlm profile shortquerybias activate;
ALTER WLM PROFILE

Now activate the maintenance profile after a waiting period of 60 seconds (WITH CANCEL by default):

premdb=# alter wlm profile maintenance activate 60;
ALTER WLM PROFILE

Note: To activate the default profile, you must quote the profile name:

premdb=# alter wlm profile "default" activate;
ALTER WLM PROFILE

For example, change the default pool for a profile:

premdb=# alter wlm profile sqb (default_pool sqbpool);
ALTER WLM PROFILE

For example, rename a profile:

premdb=# alter wlm profile shortquery rename to shortquerybias;
ALTER WLM PROFILE

Query the sys.wlm_active_profile view to see the current profiles in the system and which profile is active.