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 in two different ways:

• By creating WLM rules based on a condition: w.moveToResourcePool('name');
• By using the SQL MOVE query command

Administrators can move queries explicitly but not restart them; however, the WLM system will restart queries as needed. A query can only be moved to a pool (or restarted in a pool) that is part of the active WLM configuration.

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.

The following conditions also apply to moving queries:

• The query must be of type 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 an error.
• 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 can be moved to the ctas_pool pool:

if (w.type === 'ctas') {
	w.moveToResourcePool('ctas_pool');
}

A SQL command to create this rule would look like this:

premdb=# create wlm rule ctas_move 
(javascript $$ if(w.type === 'ctas') {w.moveToResourcePool('ctas_pool')}$$) ;
CREATE WLM RULE

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.

Parent topic:Creating Resource Pools