Limiting resources for SQL statements reactively
You can use the resource limit facility to set limits for reactive governing, which means that Db2 stops SQL statements from specified contexts that overuse system resources.
Before you begin
About this task
When you specify reactive governing, the resource limit facility stops a currently running SQL statement that meets the conditions in a resource limit table row when the SQL statement uses more than the maximum amount of resources specified by the value of the ASUTIME column in that row. When a statement exceeds a reactive governing limit, the application program receives SQLCODE -905. The application must include code that performs the appropriate action based on this situation.
Resource limits apply only to the following types of SQL statements:
- SELECT
- INSERT
- UPDATE
- MERGE
- TRUNCATE
- DELETE
For statements that contain external user defined functions, the resource time used by the user defined functions is not counted as part of the resource time for the statement. No limits apply to primary or secondary authorization IDs that have installation SYSADM or installation SYSOPR authority.
Procedure
To specify reactive resource limits:
- '2'
- Limit dynamic SQL statements by package name, authorization ID, collection ID, the location name of the requester, or a combination of them. (RLST)
- '8'
- Limit dynamic SQL statements by client information (RLMT)
- 'A'
- Limit static SQL statements by package name (RLST)
- 'B'
- Limit static SQL statements by client information (RLMT)
Results
Db2 resets the accumulated ASUTIME at the following events:
- After the execution completes for a statement that does not use a cursor.
- After the close of a cursor for a statement.
- During the PREPARE for dynamic statements.
Any statement that reaches or exceeds a limit that you set in a resource limit table terminates with SQLCODE -905 and the corresponding SQLSTATE '57014' . You can establish a single limit for all users, different limits for individual users, or both. Limits do not apply to primary or secondary authorization IDs with installation SYSADM or installation SYSOPR authority. For queries that enter Db2 from a remote site, the local site limits are used.
If the failed statement involves an SQL cursor, the cursor's position remains unchanged. The application can then close that cursor. All other operations with the cursor do not run and the same SQL error code occurs.
If the failed SQL statement does not involve a cursor, then all changes that the statement made are undone before the error code returns to the application. The application can either issue another SQL statement or commit all work done so far.