ALTER WLM
Valid in: SQL
The ALTER WLM statement allows changing the configuration of workload management while the system is running. It also lets you modify individual sessions to change their current queue, priority, and other settings.
Note: Autocommit should be set to on before executing ALTER WLM statements.
This statement has the following format:
ALTER WLM
HELP
| ENABLE
| DISABLE
| RECONFIGURE FROM TABLES
| CONFIG SET CONFIG = name
| AUDIT ENABLE
| AUDIT DISABLE
| SESSION session_id SET session_opt {,session_opt} [FOR CURRENT QUERY]
| QUEUE queue_name ADD WITH queue_opt {,queue_opt}
| QUEUE queue_name DROP [WITH CONFIG = config_name]
| QUEUE queuename SET queue_opt {,queue_opt} [WITH CONFIG =configname]
| QUEUE queue_name ADD [USER|GROUP|ROLE] name WITH PRIORITY=CRITICAL|HIGH|MED|LOW
| QUEUE queue_name DROP [USER|GROUP|ROLE] name
| QUEUE queue_name SET [USER | GROUP | ROLE] name PRIORITY=CRITICAL
HELP
Displays the ALTER WLM syntax.
ENABLE
Enables workload management.
DISABLE
Disables workload management.
RECONFIGURE FROM TABLES
Loads configuration from workload configuration system catalogs (as if you restarted the server), and then re-queues current sessions to the active configuration set in iiwlm_config.
CONFIG SET CONFIG=name
Changes the active configuration to the named configuration. All current queues are shut down. All queues for the specified configuration are made active. All existing sessions are moved to an appropriate queue in the new configuration.
AUDIT ENABLE
Enables auditing of workload management.
AUDIT DISABLE
Disables auditing of workload management.
SESSION session_id SET session_opt {,session_opt} [FOR CURRENT QUERY]
Changes characteristics for the specified session. The session_id can be entered as a single quoted hex string '0x0011111111' or as an integer like the session_id used in the ima_wlm_sessions table in imadb. If the FOR CURRENT QUERY clause is specified, the changes are made for the duration of the current query only, after which the session reverts to its originally configured settings.
The session_opt parameters can have any of the following values:
ACTIVE
Activates the session. The session is removed from the queue’s wait list, placed on the active list of sessions, and begins executing its query. This applies to the current query only; the next query will wait as normal (if needed).
PRIORITY=CRITICAL | HIGH | MED | LOW
Sets the wait list priority for the session.
QUEUE=queue_name
Moves this session to the named queue. Queue name can optionally be in double quotes.
Note: This queue can be any active queue, including queues for which this session does not have authorizations.
DEFAULT
Sets session back to default queue settings (undoes any previously issued ALTER WLM SESSION changes).
QUEUE queue_name ADD WITH queue_opt {,queue_opt}
Adds a queue to the configuration, where queue_name is the name of the queue.
Note: The $ character is reserved for system queues and cannot be used as the first character of a user defined queue.
The queue_opt list can have any of the following values:
ACTIVE_LIMIT=n
Specifies the maximum number of concurrent queries allowed. If set to 0, there is no concurrency limit.
CONFIG=config_name
Specifies the name of the configuration set for this queue. Defaults to active configuration if not specified. Config_name may not be all spaces, and must be specified if the current configuration name consists only of spaces.
[NO]DEFAULT
Specifies whether this queue should be the default queue for this configuration. Only one default queue is allowed per configuration.
Default: NODEFAULT
MAXWAIT=n
Specifies the maximum amount of time, in seconds, for a query to wait in a queue’s wait list before running. A value of 0 means queries will never be woken up due to clock time (that is, it will wait until the queue wakes them up normally).
PRIORITY=CRITICAL | HIGH | MEDIUM | LOW
Specifies the priority for execution. This priority is used when limiting resources through the ratio settings.
RATIO=n
Sets the ratio number for the queue. For more information, see Queue Ratios in the User Guide.
READWRITE=R | W | RW
Specifies the read/write setting. Valid values are:
R—Queues only SELECT queries.
W—Queues only non-SELECT (such as INSERT and UPDATE) queries.
RW—Queues any queries.
Note: DEFAULT queues must be RW.
QUEUE queue_name DROP [WITH CONFIG = config_name]
Removes queue from the active configuration, or the configuration specified by the WITH CONFIG parameter. Config_name may not be all spaces, and must be specified if the current configuration name consists only of spaces.
QUEUE queuename SET queue_opt {,queue_opt} [WITH CONFIG =configname]
Changes options for the specified queue. Config_name may not be all spaces, and must be specified if the current configuration name consists only of spaces.
Note: To update the queue settings, it is mandatory to use the "WITH CONFIG = configname" parameter. Otherwise, the new settings will be applied to the currently active configuration.
The queue_opt parameters can have any of the following values:
ACTIVE_LIMIT=n
Specifies the maximum number of concurrent queries allowed. If set to 0, there is no concurrency limit.
CONFIG=config_name
Specifies the name of the configuration containing the queue to change. (Queues may exist in multiple configurations.) Defaults to active configuration if not specified.
[NO]DEFAULT
Specifies whether this queue should be the default queue for this configuration. Only one default queue is allowed per configuration.
Default: NODEFAULT
MAXWAIT=n
Specifies the maximum amount of time, in seconds, for a query to wait in a queue’s wait list before running. A value of 0 means queries will never be woken up due to clock time. (That is, it will wait until the queue wakes them up normally.)
PRIORITY=CRITICAL | HIGH | MED | LOW
Specifies the priority for execution. This priority is used when limiting resources through the setting a ratio.
RATIO=n
Sets the ratio number for the queue. For more information, see Queue Ratios in the User Guide.
READWRITE=R | W | RW
Specifies the read/write setting. Valid values are:
R—Queues only SELECT queries.
W—Queues only non-SELECT (such as INSERT and UPDATE) queries.
RW—Queues any queries.
Note: DEFAULT queues must be RW.
QUEUE queue_name ADD [USER | GROUP | ROLE] name WITH PRIORITY=CRITICAL | HIGH | MED | LOW
Grants authorization for a specific user, group, or role to use the specified queue. Granting authorization for a queue will automatically grant authorization for any configuration that uses that queue.
The PRIORITY value determines where the user’s query will be inserted into the wait list should the query be added to a queue’s wait list. Higher priority insertion means the query will be executed before lower priority queries.
QUEUE queue_name DROP [USER | GROUP | ROLE] name
Removes authorization for the specified user, group, or role to use the specified queue.
QUEUE queue_name SET [USER | GROUP | ROLE] name PRIORITY=CRITICAL
Sets a new wait list priority for an existing queue authorization.