Resource Limit Examples

The following are examples of setting resource limits.

Example 1– This example creates a resource limit that applies to all users of the payroll application because the name parameter is NULL.
```
sp_add_resource_limit NULL, payroll, tu_wed_7_10, elapsed_time, 120, 2, 1, 2
```
The limit is valid during the tu_wed_7_10 time range. The limit type, elapsed_time, is set to a value of 120 seconds. Because elapsed_time is enforced only at execution time, the enforced parameter is set to 2. The action parameter is set to 1, which issues a warning. The limit’s scope is set to 2, query batch, by the last parameter. Therefore, when the elapsed time of the query batch takes more than 120 seconds to execute, SAP ASE issues a warning.
Example 2 – This example creates a resource limit that applies to all ad hoc queries and applications run by “joe_user” during the saturday_night time range:
```
sp_add_resource_limit joe_user, NULL, saturday_night, 
    row_count, 5000, 2, 3, 1
```
If a query (scope = 1) returns more than 5000 rows, SAP ASE aborts the transaction (action = 3). This resource limit is enforced at execution time (enforced = 2).
Example 3 – This example also creates a resource limit that applies to all ad hoc queries and applications run by “joe_user:”
```
sp_add_resource_limit joe_user, NULL, "at all times", 
    io_cost, 650, 1, 3, 1
```
However, this resource limit specifies the default time range, “at all times.” When the optimizer estimates that the io_cost of the query (scope = 1) would exceed the specified value of 650, SAP ASE aborts the transaction (action = 3). This resource limit is enforced at preexecution time (enforced = 1).

Note: Although SAP ASE terminates the current transaction when it reaches its time limit, you receive no 1105 error message until you issue another SQL command or batch; in other words, the message appears only when you attempt to use the connection again.