WLM_SET_SESSION_PRIORITY procedure - Set the priority of a given session

The WLM_SET_SESSION_PRIORITY stored procedure is used to permanently set the priority of a given session to the indicated session priority level.

Authorization

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

DBADM authority
WLMADM authority

Default PUBLIC privilege

None

Syntax

Parameters

application_handle: An input argument of type BIGINT that specifies the application handle for the connection for which the session priority is to be set. You can use a value of NULL to indicate the connection on which the procedure was invoked.
priority: An input argument of type VARCHAR(32) that specifies the session priority level. Valid levels are 'LOW', 'MEDIUM', 'HIGH', 'CRITICAL' or you can set the session back to its default setting by specifying 'DEFAULT'.

Example

An application submits a mix of regular and high priority queries. When the high priority queries are submitted it is desirable to minimize the chance of those queries being queued behind other lower priority work. Before the high priority queries are submitted, the application issues the following stored procedure call so that the activities will be run as high priority.

CALL WLM_SET_SESSION_PRIORITY( MON_GET_APPLICATION_HANDLE(), 'HIGH')

After the high priority queries have completed execution, the application calls the stored procedure again to lower the priority of the remaining work submitted back to the default level.

CALL WLM_SET_SESSION_PRIORITY( MON_GET_APPLICATION_HANDLE(), 'DEFAULT')

This example assumes that the application has sufficient authority to invoke the WLM_SET_SESSION_PRIORITY stored procedure.

Usage notes

Use the WLM_SET_SESSION_PRIORITY stored procedure to raise or lower the priority of work submitted by a connection. Higher priority work will receive a greater share of admission resources by the adaptive workload manager. Priority changes made by the stored procedure take effect immediately and will elevate or reduce the priority of any existing queued activities for the connection.

Any session priority value set by the WLM_SET_SESSION_PRIORITY stored procedure has a higher precedence than a priority set on WORKLOAD object. For example, if an application is mapped to a workload with a LOW priority, and the stored procedure is called with a priority value of HIGH, any current and future activities submitted by the connection will run with a HIGH priority.