Limiting resource usage for packages

You can specify limits for the amount of processor resources that are used by a specific group of SQL statements.

1. Insert rows into a DSNRLSTxx resource limit table with values that identify the context of the governed statements, the type of governing, thresholds for predictive governing and limits for reactive governing.
   • To insert a row for reactive governing for dynamic SQL statements, specify '2' in RLFFUNC column and the amount of processor limit in ASUTIME column. PLANNAME must contain blank. The following table shows an example of reactive governing for dynamic SQL statements:

     Table 1. Qualifying rows for reactive governing for dynamic SQL statements

     RLFFUNC	AUTHID	PLANNAME	RLFCOLLN	RLFPKG	LUNAME	ASUTIME
     2	JOE	(blank)	COL1	(blank)	(blank)	(null)
     2	JOE	(blank)	COL2	PKG1	(blank)	15000
     2	(blank)	(blank)	(blank)	PKG2	PUBLIC	10000

     The first row in the table above shows that when a user, JOE, runs any package in a collection, COL1, at the local location, no limits restrict any dynamic statement in the package. The second row shows that when a user, JOE, runs a package, PKG1, in collection, COL2, at the local location, each dynamic statement in the package is restricted to 15,000 SUs. The third row shows that when any user runs a package, PKG2, in any collection from any location in the network, including the local location, a processor limit of 10,000 SUs is applied for each dynamic statement in the package.

   • To insert a row for predictive governing of dynamic SQL statements, specify '7' in RLFFUNC column and the limit threshold values in RLFASUERR and RLFASUWARN columns. PLANNAME must contain blank. The following table shows an example of predictive governing:

     Table 2. Qualifying rows for predictive governing

     RLFFUNC	AUTHID	PLAN NAME	RLF COLLN	RLFPKG	LUNAME	RLF ASU WARN	RLF ASU ERR	RLF_ CATEGORY_ B
     7	JOE	(blank)	COL1	PKG1	(blank)	7000	12000	W
     7	(blank)	(blank)	(blank)	PKG2	PUBLIC	5000	9000	Y
     7	PETER	(blank)	(blank)	(blank)	0	0	0	N

     The first row in the table shows that when the user whose authorization ID is JOE runs a package at the local location, that a warning threshold is specified at 7000 SUs and an error threshold is specified at 12,000 SUs. The warning and error are applied when DB2® estimates that the amount of processor time consumed by a dynamic statement exceeds the specified thresholds. That row also specifies that a warning issues when for cost category B estimates.

     The second row shows that when any user runs dynamic statements from the PGK2 package in any collection from any location in the network, including at the local location, that a warning threshold is specified at 5000 SUs and that an error threshold is specified at 9000 SUs. The statement is allowed to run if the estimate is based on cost category B.

     The third row shows that when the user whose authorization ID is PETER runs any package in any collection at the local location, no dynamic statement is allowed to run even when the estimate is based on cost category B.

2. Issue the following command, where xx is the two character identifier that you specified when you created the table:
   

   -START RLIMIT ID=xx

   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:

1. Exact match
2. Authorization ID
3. Collection ID and package name
4. LU name
5. No row match

When multiple rows that contain the same values in all other columns, the best matching row is chosen based on LU name in the following order for reactive governing, if any row exists:

Local agents

1. Matching LU name value
2. Blank value
3. 'PUBLIC'

Distributed agents

1. Matching LU name value
2. 'PUBLIC'

For predictive governing, the qualified row that appears first in the index order is chosen.

When no row in the resource limit table matches the currently executing statement, DB2 uses the default values that are specified by certain subsystem parameters. For information about the subsystem parameters that apply, see Setting default resource limits for SQL statements. The default limits apply to reactive governing only. For predictive governing, when no row matches, no predictive governing occurs.

When an SQL statement contains an external user-defined function, the execution time for the user-defined function is not included in the ASUTIME of the statement execution. The ASUTIME for an external execution of a user-defined functions is controlled based on the ASUTIME specified for the user-defined function in the CREATE FUNCTION statement.