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, Adaptive Server 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, Adaptive Server 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, Adaptive Server aborts the transaction (action = 3). This resource limit is enforced at pre-execution time (enforced = 1).
