DSNRLSTxx resource limit tables

Resource limit tables can be used to limit the amount of resource used by certain SQL statements. Statements can be limited based on information about the SQL statement, including the collection ID, package name, authorization ID, and location name of the query.

Begin program-specific programming interface information.

Resource limits apply only to the following 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.

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.

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 broadly by leaving the AUTHID column blank, which means that the row applies to all authorization IDs. Or, you can specify that the limit applies more narrowly by specifying a different row for each authorization ID for which the function applies. Some columns are blank in each row that specifies a valid limit. Db2 tries to find the most exact match when it determines which row to use for a particular function. The search order depends on the type of governing that is specified. The search order is described under each of those functions.

Table 1. Columns of a DSNRLSTxx resource limit specification table
Column name Data Type Description
AUTHID VARCHAR(128) NOT NULL WITH DEFAULT The resource specification limits apply to this primary authorization ID. A blank means that the limit specifications in this row apply to all authorization IDs for the location that is specified in LUNAME. No limits apply to primary or secondary authorization IDs with installation SYSADM or installation SYSOPR authority.
PLANNAME CHAR(8) NOT NULL WITH DEFAULT The value of the PLANNAME column must be blank.
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. For information about how to calculate service units, see Calculating service unit values for resource limit tables.
LUNAME CHAR(8) NOT NULL WITH DEFAULT The LU name of the location where the request originated. A blank value in this column represents the local location, not all locations. The value PUBLIC represents all local and remote DBMS locations in the network; these locations do not need to be Db2 subsystems. PUBLIC is the only value for TCP/IP connections.
RLFFUNC CHAR(1) NOT NULL WITH DEFAULT
'1'
The row reactively governs bind operations.
'2'
The row reactively governs dynamic SELECT, INSERT, UPDATE, MERGE, TRUNCATE, or DELETE statements by package or collection name.
'4'
The row disables query CP parallelism.
'7'
The row predictively governs dynamic SELECT, INSERT, UPDATE, MERGE, TRUNCATE, or DELETE statements by package or collection name.
'A'
The row reactively governs static SELECT (cursor and singleton), INSERT, UPDATE, MERGE, TRUNCATE, or DELETE statements by package or collection name

All other values are ignored.
RLFBIND CHAR(1) NOT NULL WITH DEFAULT Shows whether bind operations are allowed. An 'N' implies that bind operations are not allowed. Any other value means that bind operations are allowed. This column is used only if RLFFUNC is set to '1'.
RLFCOLLN VARCHAR(128) NOT NULL WITH DEFAULT Specifies a package collection. A blank value in this column means that the row applies to all package collections from the location that is specified in LUNAME. Qualify by collection name only if the statement is issued from a package; otherwise Db2 does not find this row. If RLFFUNC='1', RLFCOLLN must be blank.
RLFPKG VARCHAR(128) NOT NULL WITH DEFAULT Specifies a package name. A blank value in this column means that the row applies to all packages from the location that is specified in LUNAME. If RLFFUNC='1', RLFPKG must be blank.
RLFASUERR INTEGER Used for predictive governing (RLFFUNC= '7'), 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 dynamic 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 dynamicSELECT, INSERT, UPDATE, MERGE, TRUNCATE, or DELETE statements receive SQLCODE -495.
RLFASUWARN INTEGER Used for predictive governing (RLFFUNC= '7'), and only for statements that are in cost category A. The warning threshold number of processor service units that are allowed for a single 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= '7'). Tells the resource limit facility 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.

Create index statement

The following statement creates an index that is required for use with RLST resource limit tables:
CREATE UNIQUE INDEX authid.DSNARLxx
       ON authid.DSNRLSTxx
         (RLFFUNC, AUTHID DESC, PLANNAME DESC,
          RLFCOLLN DESC, RLFPKG DESC, LUNAME DESC)
         CLUSTER CLOSE NO;
Note: The value of xx in the index name must match the xx in the table name (DSNRLSTxx), and the index must be a descending index.
End program-specific programming interface information.