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.
Resource limits apply only to the following statements:
- SELECT
- INSERT
- UPDATE
- MERGE
- TRUNCATE
- DELETE
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.
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
CREATE TABLE authid.DSNRLSTxx
(AUTHID VARCHAR(128) NOT NULL WITH DEFAULT,
PLANNAME CHAR(8) NOT NULL WITH DEFAULT,
ASUTIME INTEGER,
-------3-column format --------
LUNAME CHAR(8) NOT NULL WITH DEFAULT,
-------4-column format --------
RLFFUNC CHAR(1) NOT NULL WITH DEFAULT,
RLFBIND CHAR(1) NOT NULL WITH DEFAULT,
RLFCOLLN VARCHAR(128) NOT NULL WITH DEFAULT,
RLFPKG VARCHAR(128) NOT NULL WITH DEFAULT),
-------8-column format --------
RLFASUERR INTEGER,
RLFASUWARN INTEGER,
RLF_CATEGORY_B CHAR(1) NOT NULL WITH DEFAULT)
-------11-column format --------
IN DSNRLST.DSNRLSxx;
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.
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. Important: When the DISALLOW_DEFATUL_COLLID subsystem
parameter us set to NO, DBRMs that were previously bound into plans are automatically rebound into
packages when executed in Version 10. Any row that contains a value for PLANNAME must be modified to
specify a blank value for PLANNAME and a value of '2' or '7' for the RLFFUNC column. The modified
row must specify the package name (the name of the DBRM) and collection ID
(DSN_DEFAULT_COLLID_plan-name) for the automatically bound
package.
|
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:
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 |
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:
|
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:
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:
|
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;