Periodic Batch Loads

Scenario

You do batch loads every night, or occasionally (infrequently) during the day. When loads are running, it is acceptable to reduce resource usage by queries, but at all other times you want all resources to be available to queries.

Solution

Create a separate resource pool for loads with a higher priority than the preconfigured setting on the build-in GENERAL pool.

In this scenario, nightly loads get preference when borrowing memory from the GENERAL pool. When loads are not running, all memory is automatically available for queries.

If you are using the WOS, tune the PLANNEDCONCURRENCY parameter of the WOSDATA pool to the number of concurrent loads. This ensures that AUTO spill to ROS is configured in an optimal fashion.

Example

Create a resource pool with the PRIORITY of the pool set higher than the GENERAL pool.

For example, to create a pool designated for loads that has a higher priority then the GENERAL pool, set load_pool with a priority of 10:

=> CREATE RESOURCE POOL load_pool PRIORITY 10;

Edit the WOSDATA pool PLANNEDCONCURRENCY:

=> ALTER RESOURCE POOL WOSDATA PLANNEDCONCURRENCY 6;

Modify the user's resource pool:

=> ALTER USER load_user RESOURCE POOL load_pool;