sp_modify_resource

Syntax

sp_modify_resource_limit {name, appname} 
	rangename, limittype, limitvalue, enforced, action, scope

Parameters

name – is the SAP ASE login to which the limit applies. You must specify either a name or an appname or both. To modify a limit that applies to all users of a particular application, specify a name of null.
appname – is the name of the application to which the limit applies. You must specify either a name or an appname or both. If the limit applies to all applications used by name, specify an appname of null. If the limit governs a particular application, specify the application name that the client program passes to the SAP ASE server in the login packet.
rangename – is the time range during which the limit is enforced. You cannot modify this value, but you must specify a non-null value to uniquely identify the resource limit.
limittype – is the type of resource to which the limit applies. You cannot modify this value, but you must specify a non-null value to uniquely identify the resource limit. The value must be one of the following:
- row_count – limits the number of rows a query can return
- elapsed_time – limits the number of seconds in wall-clock time that a query batch or transaction can run
- io_cost – limits either the actual cost, or the optimizer’s cost estimate, for processing a query
- tempdb_space – limits the number of pages from a tempdb database that a single session can have
limit_value – is the maximum amount of the server resource that the login or application can use before the SAP ASE server enforces the limit. This must be a positive integer less than or equal to 2³¹ or null to retain the existing value. The following table indicates what value to specify for each limit type:
- row_count – the maximum number of rows a query can return before the limit is enforced
- elapsed_time – the maximum number of seconds in wall-clock time that a query batch or transaction can run before the limit is enforced
- io_cost – a unitless measure derived from optimizer’s costing formula
- tempdb_space – limits the number of pages from a temporary database that a single session can have.
enforced – determines whether the limit is enforced prior to or during query execution. You cannot modify this value. Use null as a placeholder.
action – is the action to take when the limit is exceeded. The following codes apply to all limit types:
- 1 – issues a warning
- 2 – aborts the query batch
- 3 – aborts the transaction
- 4 – kills the session
- null – retains the existing value
scope – is the scope of the limit. You cannot modify this value. You can use null as a placeholder.

Examples

Example 1 – Modifies a resource limit that applies to all applications used by “robin” during the weekends time range. The limit issues a warning when a query is expected to return more than 3000 rows:
```
sp_modify_resource_limit robin, NULL, weekends, row_count, 3000, NULL, 
    1, NULL
```
Example 2 – Modifies a resource limit that applies to the acctg application on all days of the week and at all times of the day. The limit aborts the query batch when estimated query processing time exceeds 45 seconds:
```
sp_modify_resource_limit NULL, acctg, "at all times", elapsed_time, 
    45, 2, 2, 6
```
Example 3 – This example changes the value of the resource limit that restricts elapsed time to all users of the payroll application during the tu_wed_7_10 time range. The limit value for elapsed time decreases to 90 seconds (from 120 seconds). The values for time of execution, action taken, and scope remain unchanged:
```
sp_modify_resource_limit NULL, payroll, tu_wed_7_10, elapsed_time, 90, null, null, 2
```
Example 4 – This example changes the action taken by the resource limit that restricts the row count of all ad hoc queries and applications run by “joe_user” during the saturday_nighttime range. The previous value for action was 3, which aborts the transaction when a query exceeds the specified row count. The new value is to 2, which aborts the query batch. The values for limit type, time of execution, and scope remain unchanged.
```
sp_modify_resource_limit joe_user, NULL,
saturday_night, row_count, NULL, NULL, 2, NULL
```

Usage

There are additional considerations when using sp_modify_resource_limit:

You cannot change the login or application to which a limit applies or specify a new time range, limit type, enforcement time, or scope.
The modification of a resource limit causes the limits for each session for that login and/or application to be rebound at the beginning of the next query batch for that session.
SAP ASE provides resource limits to help system administrators prevent queries and transactions from monopolizing server resources. Resource limits, however, are not fully specified until they are bound to a time range.

For more information, see the System Administration Guide.

Permissions

The permission checks for sp_modify_resource_limit differ based on your granular permissions settings.

Setting	Description
Enabled	With granular permissions enabled, you must be a user with `manage resource limit` privilege.
Disabled	With granular permissions disabled, you must be a user with sa_role.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – All input parameters Proxy information – Original login name, if set proxy in effect