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.

Before you begin

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

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 rows for reactive governing for static SQL statements, specify 'A' in the RLFFUNC column and the amount of processor limit in ASUTIME column. The PLANNAME must contain blank. The following table shows an example of reactive governing for static SQL statements:

     Table 2. Qualifying rows for reactive governing for static SQL statements

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

     
   • 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 3. 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.