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

Set the RLFENABLE subsystem parameter to an appropriate value for the types of SQL statements that you want to limit.

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

This action is required only when the client application does not use the default values.

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

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.

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

  • SELECT
  • INSERT
  • UPDATE
  • MERGE
  • TRUNCATE
  • DELETE

You can specify resource limits for dynamic SQL statements and static SQL statements. Resource limits apply to SQL statement regardless of whether they are issued locally or remotely. The resource limit facility does not apply to primary or secondary authorization IDs that have installation SYSADM or installation SYSOPR authority.

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.

Procedure

To limit the use of resources for middleware servers:

  1. 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
    Start of change'B'End of change Start of changeAPP1End of change Start of changePAULEnd of change Start of change(blank)End of change Start of change9.30.72.223End of change Start of change12000End of change
    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. Start of changeThe second row shows that the same limits apply to static SQL statements from the same context.End of change The last row shows that any dynamic SQL statements that are issued from work station 10 are limited to 7000 SUs each.

  2. Issue this command:

    Begin general-use programming interface information.

    -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.End general-use programming interface information.

Results

Db2 uses the following search order:
  1. Exact match
  2. Application name
  3. User ID
  4. Workstation name
  5. IP address
  6. 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.