Limiting resource usage by client information

You can limit the amount of processor resources that are used by a specific group of SQL statements based on the client information of the statements.

Before you begin

Provide client information to DB2® by using the appropriate application programming interface to set the values of the following special registers:

Start of change This action is required only when the client application does not use the default values. End of change

About this task

You can use a DSNRLMTxx resource limit tables to specify limits and thresholds for resource usage that apply to certain SQL statements that run on middleware servers based on the following types of client information:

Application name
End-user ID
Workstation ID
IP address

Start of change The values for the client information that are found at the start of a new unit-of-work are used to determine which row of the RLMT table controls the processing of dynamic requests for the duration of the unit-of-work. End of change

Resource limits apply only to the following types of SQL statements:

SELECT
INSERT
UPDATE
MERGE
TRUNCATE
DELETE

Start of change Resource limits apply only to dynamic SQL statements. Resource limits apply to SQL statement regardless of whether they are issued locally or remotely. The resource limit facility does not control static SQL statements regardless of whether they are issued locally or remotely, and no limits apply to primary or secondary authorization IDs that have installation SYSADM or installation SYSOPR authority. End of change

Start of change If both DSNRLMTxx and DSNRLSTxx tables exist, rows in the DSNRLMTxx table that match a statement take priority over any matching rows in the DSNRLSTxx table. End of change

Procedure

To limit the use of resources for middleware servers:

Insert values that identify the statements to limit, the type of governing, and the thresholds or limits, into a DSNRLMTxx resource limit table.

The following table shows example rows that specify client-based limits for dynamic SQL statements.

Table 1. Qualifying rows for dynamic SQL statements for middleware servers
RLFFUNC	RLFEUAN	RLFEUID	RLFEUWN	RLFIP	ASUTIME
8	APP1	PAUL	(blank)	9.30.72.223	12000
8	(blank)	(blank)	WORKSTA10	(blank)	7000

The first row in the table shows that when PAUL runs the APP1 application from the 9.30.72.223 IP address, the resource limit facility limits dynamic SQL statements run by APP1 to 12,000 SUs each. The last row shows that any dynamic SQL statements that are issued from work station 10 are limited to 7000 SUs each.

Issue this command:
```
-START RLIMIT ID=xx
```
xx is the two-character identifier that you specified when you created the table. You can start and stop different resource limit tables at different times. However, only one resource limit table of each type (DSNRLMTxx or DSNRLSTxx) can run at any one time.

Results

DB2 uses the following search order:

Exact match
Application name
User ID
Workstation name
IP address
No row match

What to do next

Consider setting default resource limits that apply when resource limit tables cannot be accessed or matching resource limit table rows do not exist.For detailed information about creating default resource limits, see Setting default resource limits for SQL statements.