DSNRLMTxx resource limit tables

Resource limit tables can be used to limit the amount of resources used by a certain group of SQL statements. Statements can be limited based on client information, including the application name, user ID, workstation ID, and IP address of the client.

Begin program-specific programming interface information.
Deprecated function: DSNRLMTxx table formats and related index formats from before Db2 11 are deprecated. In Db2 12, if Db2 detects DSNRLMTxx tables in a deprecated format, it issues message DSNT732I, processing for the START RLIMIT command continues, and the resource limit facility starts using the deprecated objects.

For more information, see Convert RLF tables to the current format.

Resource limits apply only to the following SQL statements:

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

Start of changeStart of changeYou can specify resource limits for dynamic SQL statements and static SQL statements.End of change 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. End of change

Start of changeIf 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

Create table statement

The DSNTIJSG installation job contains statements that create the database, table space, tables, and indexes for the resource limit facility. You can tailor those statements to create or update the format of resource limit tables.

In the table name, xx is any two-character alphanumeric value. Because the two characters xx must be entered as part of the START RLIMIT command, they must be alphanumeric. Special or DBCS characters are not allowed.

Column descriptions

The values in each row define a limit, including the function and scope of each limit. The function of a particular limit is defined by the value of the RLFFUNC column. Other columns specify the scope for the limit defined by the row. For example, you can specify that a limit applies broadly by leaving the RLFEUAN column blank, which means that the row applies to all user IDs, or you can specify limits narrowly by specifying a different row for each user ID for which the function applies. Db2 tries to find the most exact match when it determines which row to use for a particular function. The search order depends on whether reactive or predictive governing is specified. The search order is described under each of those functions.

Table 1. Columns of a DSNRLMTxx resource limit specification table
Column name Data type Description
RLFFUNC CHAR(1) NOT NULL WITH DEFAULT

Specifies how the row is used. The values that have an effect are:

'8'
The row reactively governs dynamic SELECT, INSERT, UPDATE, MERGE, TRUNCATE, or DELETE statements by client information (RLEUID, RLFEUAN, RLFEUWN, and RLFIP).
'9'
The row predictively governs dynamic SELECT, INSERT, UPDATE, MERGE, TRUNCATE, or DELETE statements by client information (RLEUID, RLFEUAN, RLFEUWN, and RLFIP).
Start of change'B'End of change
Start of changeThe row reactively governs static SELECT (cursor and singleton), INSERT, UPDATE, MERGE, TRUNCATE, or DELETE statements by client information (RLEUID, RLFEUAN, RLFEUWN, and RLFIP).End of change

All other values are ignored.
RLFEUAN VARCHAR(255) NOT NULL WITH DEFAULT 1 Specifies an application name. A blank value in this column means that the row applies to all application names from the location specified in RLFIP.
RLFEUID VARCHAR(128) NOT NULL WITH DEFAULT 2 Specifies an end user's user ID. A blank value means that the limit specifications in this row apply to every user ID for the location that is specified in RLFIP.
RLFEUWN VARCHAR(255) NOT NULL WITH DEFAULT 3 Specifies an end user's workstation name. A blank value in this column means that the row applies to all workstation names from the location that is specified in RLFIP.
RLFIP CHAR(254) NOT NULL WITH DEFAULT The IP address of the location where the request originated. A blank value in this column represents all locations.
ASUTIME INTEGER

The number of processor service units allowed for any single SELECT, INSERT, UPDATE, MERGE, TRUNCATE, or DELETE statement. Use this column for reactive governing.

Other possible values and their meanings are:

null
No limit
0 (zero) or a negative value
No SELECT, INSERT, UPDATE, MERGE, TRUNCATE, or DELETE statements are permitted.

A relative metric is used so that the resource limit table values do not need to be modified when processors are changed. However, in some cases, Db2 workloads can differ from the measurement averages. In these cases, resource limit table value changes might be necessary.
RLFASUERR INTEGER

Used for predictive governing (RLFFUNC= '9'), and only for statements that are in cost category A. The error threshold number of system resource manager processor service units allowed for a single SELECT, INSERT, UPDATE, MERGE, TRUNCATE, or DELETE statement. If the predicted processor cost (in service units) is greater than the error threshold, an SQLCODE -495 is returned to the application

Other possible values and their effects are:

null
No error threshold
0 (zero) or a negative value
All dynamic SELECT, INSERT, UPDATE, MERGE, TRUNCATE, or DELETE statements receive SQLCODE -495.
RLFASUWARN INTEGER

Used for predictive governing (RLFFUNC= '9'), and only for statements that are in cost category A. The warning threshold number of processor service units that are allowed for a single dynamic SELECT, INSERT, UPDATE, MERGE, TRUNCATE, or DELETE statement. If the predicted processor cost (in service units) is greater than the warning threshold, an SQLCODE +495 is returned to the application.

Other possible values and their effects are:

null
No warning threshold
0 (zero) or a negative value
All dynamic SELECT, INSERT, UPDATE, MERGE, TRUNCATE, or DELETE statements receive SQLCODE +495.
Important: Make sure the value for RLFASUWARN is less than that for RLFASUERR. If the warning value is higher, the warning is never reported. The error takes precedence over the warning.
RLF_CATEGORY_B CHAR(1) NOT NULL WITH DEFAULT

Used for predictive governing (RLFFUNC='9'). Tells the governor the default action to take when the cost estimate for a given statement falls into cost category B, which means that the predicted cost is indeterminate and probably too low. You can tell if a statement is in cost category B by running EXPLAIN and checking the COST_CATEGORY column of the DSN_STATEMNT_TABLE.

The acceptable values are:

blank
By default, prepare and execute the SQL statement.
Y
Prepare and execute the SQL statement.
N
Do not prepare or execute the SQL statement. Return SQLCODE -495 to the application.
W
Complete the prepare, return SQLCODE +495, and allow the application logic to decide whether to execute the SQL statement or not.
Notes:
  1. To start RLF with this definition, Db2 must be running in Db2 11 new-function mode or later. RLFEUAN created with CHAR(32) NOT NULL WITH DEFAULT is also supported. The table must be created with all column definitions from the current or prior release.
  2. To start RLF with this definition, Db2 must be running in Db2 11 new-function mode or later. RLFEUID created with CHAR(16) NOT NULL WITH DEFAULT is also supported. The table must be created with all column definitions from the current or prior release.
  3. To start RLF with this definition, Db2 must be running in Db2 11 new-function mode or later. RLFEUWN created with CHAR(18) NOT NULL WITH DEFAULT is also supported. The table must be created with all column definitions from the current or prior release.

Search order

Db2 tries to find the most exact match when it determines which row to use for a particular function. The search order depends on which function is being requested (reactive or predictive governing). The search order is described under each of those functions.

Create index statement

To use this table, you must also create an index named authid.DSNMRLxx, where xx represents the same two alphanumeric characters from the table name, in the DSNRLST database.

CREATE UNIQUE INDEX authid.DSNMRLxx
       ON authid.DSNRLMTxx
         (RLFFUNC, RLFEUAN DESC, RLFEUID DESC,
          RLFEUWN DESC, RLFIP DESC)
         CLUSTER CLOSE NO;
End program-specific programming interface information.