Resource Limits (SYB ASE)

Go Up to Data, Schema, and Miscellaneous Utilities

Resource limits are sets of parameters that let you maximize your datasource assets. By setting resource limits, you ca n prevent transactions and queries from congesting server resources. This ensures that you do not:

• Exceed estimated or actual input/output demands determined by the optimizer.
• Return a number of rows beyond a set limit.
• Exceed a defined elapsed time.

DBArtisan includes the Resource Limits dialog that lets you perform the following tasks:

• Showing Resource Limits
• Adding Resource Limits
• Editing Resource Limits
• Dropping Resource Limits

Opening the Resource Limits Dialog Box

You must connect to a datasource running Sybase ASE before you can open the Resource Limits dialog To open the Resource Limits dialog, do the following:

1. On the Datasource Navigator/Explorer, select the target datasource node.
2. On the Utilities menu, select Resource Limits.

   DBArtisan opens the Resource Limits dialog.

The table below describes the columns in the Resource Limits table of the Resource Limits dialog

Column	Description
name	The login name for the resource limit.
appname	The application for the resource limit.
rangename	The time range in which the resource limit is enforced. The time range is a contiguous block of time.
rangeid	The value of the rangename.
limitid	The value and type of the resource limit.
limitvalue	The value of the resource limit.
enforced	The value and enforcement time for the resource limit.
action	The code number of the action for the resource limit. The codes numbers are: 1=Issue a warning 2=Abort the query batch 3=Abort the transaction 4=Kill the session
scope	The code number of the scope for the resource limit. The codes numbers are: 1=Query 2=Query batch 4=Transaction 6=Batch and transaction

Showing Resource Limits

DBArtisan displays all resource limits for the current datasource. In addition, DBArtisan lets you use the Resource Limits dialog to search for and display specific resource limits. In the Resource Limits dialog, you can specify search criteria for resource limits. DBArtisan displays the resource limits that meet your search criteria in the Resource Limits table of the Resource Limits dialog

Displaying Resource Limits

1. On the Datasource Navigator/Explorer, select the target datasource node.
2. On the Utilities menu, select Resource Limits.

   DBArtisan opens the Resource Limits dialog.

3. Specify the search parameters. The table below describes the search parameters in the Resource Limits dialog:

Parameter	Description
Login list	Lets you specify the target login or logins.
Application list	Lets you specify an application or applications for the limit criteria.
Limit Time check box	Lets you specify a time range in which to enforce the resource limit.
Limit Time list	Lets you specify a start time for the resource limit. The start time must be within the proceeding 24 hours. To specify the start time, you must select the Limit Time check box.
Limit Day list	Lets you specify the day or day(s) for the resource limit.
Scope list	Lets you specify the scope of the target action(s). On the Scope list, the numbers next to each scope display in the scope column of the Resource Limits table.
Action list	Lets you specify the action to take place when a resource limit is exceeded. On the Action list, the numbers next to each action display in the action column of the Resource Limits table.

4. When you finish specifying search parameters, click List Limits.

DBArtisan lists the resource limits meeting the target criteria in the Resource Limits table.

Tip: To sort the display of the Resource Limits list, click a column heading.

Adding Resource Limits

DBArtisan offers a simple graphical wizard that guides you through the process of creating a resource limit without requiring your to know any of the underlying commands.

1. On the Datasource Navigator/Explorer, select the target datasource node.
2. On the Utilities menu, select Resource Limits.

   DBArtisan opens the Resource Limits dialog.

3. In the Resource Limits dialog, click Add.

   DBArtisan opens the Resource Limit Wizard - Panel 1

Resource Limit Wizard - Panel 1

The first panel of the Resource Limit Wizard lets you identify the objects to which the resource limit will apply.

The table below describes the options and functionality on the first panel of the Resource Limit Wizard:

Option	Description
Add Button	Click to open the Add/Edit Resource Limit Object Dialog Box.
Edit Button	Click to open the Add/Edit Resource Limit Object Dialog Box.

Add/Edit Resource Limit Object Dialog Box

The table below describes the options and functionality on the Add/Edit Resource Limit Object dialog:

Option	Description
Login	Lets you click the list, and then click the target user login.
Application	Click the list, and then click the target application.

Resource Limit Wizard - Panel 2

The second panel of the Resource Limit Wizard lets you specify:

• The type of resource limit.
• The value of the resource limit type.
• The time during which the limit will be enforced.

The table below describes the options and functionality on the second panel of the Resource Limit Wizard:

Option	Description
Select the type of resource limit	Lets you specify the type of resource limit by clicking one of the following option buttons: Input/output cost, Elapsed time, or Row count.
Limit Value box	Note: You must type the value for the limit to continue.
Time Range	Lets you click the list, and then click the target named range.
Modify, Add, and Edit buttons	Click to add or edit a named time range. DBArtisan opens the Named Time Ranges Dialog Box.
Pre-execution	To specify the execution times during which the limit will be enforced, select the target check box(es):
Execution	To specify the execution times during which the limit will be enforced, select the target check box(es):

Named Time Ranges Dialog Box

A time range is a contiguous set of days. For example if you designate a Friday start day and a Monday end day, the time range includes the weekend.

The table below describes the options and functionality on the Named Time Ranges dialog:

Option	Description
Name	In the box, type a name for the new time range.
Start time	In the box, specify the start time.
End time	In the box, specify the end time.
Start day	In the box, specify the beginning of the time range.
End day	In the box, specify the end of the time range.

Resource Limit Wizard - Panel 3

The third panel of the Resource Limit Wizard lets you specify the scope of the resource limit and the action to perform when the limit is exceeded. The available scopes are dependent on the type of resource limit you selected on the previous panel of the wizard.

The table below describes the types and available scopes:

Resource Limit Type	Available Scope
Input/output cost	Query
Elapsed time	Query batch, Transaction, and Query batch and transaction
Row count	Query

The table below describes the options and functionality on the third panel of the Resource Limit Wizard:

Option	Description
Scope	Click one of the option buttons to specify the scope of the resource limit: Query Query batch Transaction Query batch and transaction
Action	Click one the following option buttons to specify the action to take when the resource limit is exceeded: Issue a warning, Abort the query batch, Abort the transaction, or Kill the session.

Resource Limit Wizard - Last Panel

The last panel of the Resource Limit Wizard lets you:

• Preview the SQL for function that adds the resource limit.
• Execute the SQL for function that adds the resource limit.

Completing the Last Panel

1. Review the SQL.
2. Click Execute.

Editing Resource Limits

DBArtisan lets you edit resource limits. To edit resource limits, do the following:

1. On the Datasource Navigator/Explorer, select the target datasource node.
2. On the Utilities menu, select Resource Limits.

   DBArtisan opens the Resource Limits dialog.

3. In the Resource Limits grid, select the target resource limit.
4. Click Edit.

   DBArtisan opens the Resource Limit Editor.

5. To modify the limit value, in the Limit Value box edit the value.
6. To change the type of action, click a different option button.
7. Click OK.

Dropping Resource Limits

DBArtisan lets you drop resource limits. To drop resource limits, do the following:

1. On the Datasource Navigator/Explorer, select the target datasource node.
2. On the Utilities menu, select Resource Limits.

   DBArtisan opens the Resource Limits dialog.

3. In the Resource Limits grid, select the target resource limit.
4. Click Drop.

   DBArtisan opens a confirm drop message.

5. Click Yes.