Setting limits for system resource usage by using the resource limit facility
The Db2 resource limit facility (governor) enables you to limit resource usage, bind operations, and parallelism modes in the processing of certain SQL statements. You can specify resource limits for dynamic SQL statements and static SQL statements.
Before you begin
About this task
You can use the resource limit facility for following activities:
- Set warning and error thresholds for SQL statements. The resource limit facility can inform users (through your application programs) that a processing limit might be exceeded for a particular SQL statement. These limits are sometimes called predictive governing. The resource limit facility provides an estimate of the processing cost of SQL statements before they run. To predict the cost of an SQL statement, you execute EXPLAIN to put information about the statement cost in DSN_STATEMNT_TABLE. If the statement exceeds a predictive governing limit, it receives a warning or error SQL code.Predictive governing applies only to dynamic SQL statements.
- Stop SQL statements that exceed the processor limit that you specified. These limits are sometimes called reactive governing.
- Restrict bind and rebind activities to avoid performance impacts on production data.
You can use reactive or predictive governing separately, or in combination.
You can specify resource limits for dynamic SQL statements and static SQL statements. Resource limits apply to SQL statement regardless of whether they are issued locally or remotely. The resource limit facility does not apply to primary or secondary authorization IDs that have installation SYSADM or installation SYSOPR authority.
Resource limits apply only to the following types of SQL statements:
- SELECT
- INSERT
- UPDATE
- MERGE
- TRUNCATE
- DELETE
Procedure
To set limits for system resource usage:
- Create a DSNRLSTxx table, a DSNRLMTxx table, or one of each.
- Populate the content of the table or tables.
- Use the START RLIMIT command to activate the resource limit facility.