Moving Queries

Administrators can move and restart queries, either by defining WLM rules that are applied conditionally or by running SQL commands while a query is in flight. A query can only be moved to a pool, or restarted in a pool, that is part of the active WLM configuration. This section explains the move (or "pool hopping") case. See also Restarting Queries

Moving a Query to Another Pool

Queries are sometimes assigned to a pool that turns out not to be an optimal choice for some reason. For example, a query may be estimated to be a short-running query. If this estimate is wrong, administrators may want to move the query into another pool, rather than consume resources allocated to the short-query pool and hold up genuine short queries.

Administrators can move a query from one pool to another by creating WLM compile or runtime rules with the following action: 
w.moveToResourcePool('name')
A second Boolean argument to this property allows a query to be "moved" to the same pool where it started: 
w.moveToResourcePool('name', 'true')

You can use the SMC or the CREATE WLM RULE command to define these rules.

Alternatively, instead of relying on rule processing, you can use a MOVE query command to move a query while it is executing.

A query that is explicitly moved may either continue to execute in the new pool, or it may have to be restarted in the new pool. If a query is moved into a pool with less memory or spill space available per slot than the original pool, the query cannot continue in the new pool and has to be restarted. The query will either start again in the new pool immediately or wait in the queue for that pool until a slot becomes available.

Movable Queries

The following conditions apply to moving queries:
  • The query must be of type SELECT, INSERT (INTO SELECT), or CTAS.
  • The query must be running on the worker nodes (not on the front-end database).
  • The query must not have failed with a non-recoverable error.
  • The query must be restartable (in case the attempt to move the query results in an attempt to restart it). This may happen when the target resource pool does not have adequate resources to continue running the query.
  • The query must not be running in a transaction that has modified data (which effectively makes the query ineligible for restart).
  • The query must not have started sending data to the client.

Example of a Move Rule

For example, the following rule condition states that any CTAS type query that is running in a given resource pool can be moved to the ctas_pool pool:
if (w.type === 'ctas') {
	w.moveToResourcePool('ctas_pool');
}
A SQL command to create this runtime rule would look like this: 
premdb=# create wlm rule ctas_move
(type runtime, 
javascript $$ if(w.type === 'ctas') {w.moveToResourcePool('ctas_pool')}$$
);
CREATE WLM RULE

For another example, see Move a Query to the "Long" Pool.

Example of a SQL MOVE command

The following SQL command moves a query to another pool.
premdb# move 17831 to wlm resource pool flexpool10;
MOVE

where 17831 is the ID of a query and flexpool10 is the name of a resource pool in the active WLM configuration.