DB2 Resource Limit Facility (governor)

Why Db2 needs a governor

Static SQL in well-bound packages is predictable. Ad-hoc query tools, JDBC dynamic statements, and “SELECT * FROM huge_table” from a BI user are not. Without controls, one bad query can burn CPU, fill workfiles, and delay production threads. RLF lets you say: this authid, package, or location may only spend so many service units—or must not BIND during the day—before Db2 intervenes.

Limits can apply to dynamic SQL and, depending on configuration and row setup, aspects of static SQL and bind control. They apply whether the statement arrives locally or remotely. Authids that hold installation SYSADM or installation SYSOPR are not governed—keep those IDs tightly controlled.

Subsystem parameter RLFENABLE (and related install options) influence which statement categories participate. Always align table design with the RLFENABLE setting your shop chose.

Resource limit tables: DSNRLSTxx and friends

You create a DSNRLSTxx table (resource limit specification table) owned under the resource authid from install panel DSNTIPP. The xx suffix is the ID used on START RLIMIT. Some environments also use DSNRLMTxx formats for additional limit styles—follow the IBM table layouts for your Db2 version and prefer current formats when migrating.

Matching is specific: blank wildcards broaden a row; package collection qualifiers only help when the statement truly comes from a package. RLFFUNC values select behaviors such as governing disable/enable patterns and bind restrictions (for example RLFBIND used when RLFFUNC indicates bind control). Insert rows deliberately and keep a change log—an over-broad blank AUTHID row can surprise an entire location.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Illustrative shape only; use IBM DDL for your release
CREATE TABLE authid.DSNRLST01 (
  AUTHID        VARCHAR(128) NOT NULL WITH DEFAULT,
  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,
  LUNAME        VARCHAR(8)   NOT NULL WITH DEFAULT,
  -- plus ASUTIME / other limit columns per version
  ...
);
 
CREATE UNIQUE INDEX authid.DSNARL01
  ON authid.DSNRLST01
  (RLFFUNC, AUTHID DESC, PLANNAME DESC,
   RLFCOLLN DESC, RLFPKG DESC, LUNAME DESC)
  CLUSTER CLOSE NO;

Query CPU limits and dynamic SQL limits

Query CPU limits (often expressed in service-unit style ASUTIME values in the limit tables) are the heart of reactive governing. When a statement exceeds the limit, Db2 terminates it with a resource-limit SQLCODE so one query cannot run forever.

Dynamic SQL limits matter most for SPUFI, QMF, JDBC/ODBC dynamic statements, and application dynamic SQL. You can also use RLF to restrict BIND/REBIND during peak periods so package changes do not steal CPU from OLTP. Predictive governing adds another layer: Db2 estimates cost using EXPLAIN information (for example in DSN_STATEMNT_TABLE) and can return a warning or error before the statement burns the CPU budget. Predictive governing applies to dynamic SQL.

• Reactive — stop expensive statements that already crossed the line
• Predictive — warn or fail early based on estimated cost
• Bind control — refuse binds that would hurt production windows

START, STOP, and DISPLAY RLIMIT

1
2
3
4
5
6
7
-START RLIMIT ID=01
-START RLIMIT ID=01 SCOPE(GROUP)   /* data sharing: all members */
-DISPLAY RLIMIT
-STOP RLIMIT
 
/* ID characters match the DSNRLSTxx suffix */
/* SCOPE(LOCAL) is the default when omitted */

You can issue START RLIMIT even if RLF is already active. Db2 reads the tables into memory for governing. When you update active resource limit tables, Db2 can detect changes and refresh the in-memory copy—still, treat production row changes as a controlled change. In data sharing, decide whether you need SCOPE(GROUP) so every member enforces the same governor ID.

Resource limit violations

A resource limit violation means a statement hit a governor threshold. For reactive limits the SQL fails when the CPU budget is exhausted. For predictive limits the application may see a warning SQLCODE (continue with caution) or an error SQLCODE (do not run). Train developers and tool users to recognize these codes instead of retrying forever.

Operational response:

1. Confirm RLF is active and which ID is loaded (DISPLAY RLIMIT)
2. Find the matching DSNRLSTxx row (authid, package, location)
3. Decide whether the limit is correct or the SQL needs a rewrite/index
4. Avoid “fixing” production by granting installation SYSADM to the user—that bypasses RLF but creates a larger security problem
5. For bind failures under RLFBIND control, move binds to the approved window or adjust the row intentionally

RLF complements—not replaces—WLM goals, profile monitoring, and application timeouts. Profiles can watch threads and connections; RLF specifically targets statement resource rules you encode in tables.

Practical design tips

• Start with limits on known ad-hoc authids and query tools, then widen carefully
• Keep a test ID= table for experiments; promote rows to the production ID
• Document every blank-column wildcard row—those are the dangerous ones
• Coordinate with DDF teams: remote reporting users are frequent runaway sources
• Pair predictive warnings with application UX so users see “query too expensive” instead of a generic failure

1
2
3
4
5
/* Example thinking for a reporting authid */
/* Row: AUTHID=REPORT1, package collection for the BI package, */
/*      ASUTIME set to a sane ceiling, predictive thresholds as needed */
/* Activate: -START RLIMIT ID=01 */
/* Verify:   -DISPLAY RLIMIT */

RLF and modern client traffic

Distributed applications multiply dynamic SQL. A connection pool may present one technical authid for hundreds of end users unless you use trusted contexts or unique identities. If RLF rows key only on that shared authid, you govern the whole pool as one beast—which may be exactly what you want for a reporting ID, or exactly wrong if interactive users share an ID with batch extract jobs. Align RLF design with how clients authenticate.

Package-based matching helps when the client always runs through known DRDA packages or shop packages. Location-based matching helps when a particular remote LOCATION or network partner is the risk. Combine qualifiers carefully: too many blanks and you govern everyone; too many literals and a renamed package silently escapes the limit.

Working with profiles and timeouts

Db2 profiles can monitor threads, connections, and idle behavior. Application and middleware timeouts can cancel conversations. RLF specifically encodes statement-level resource rules in tables you START into the governor. Use profiles to spot abnormal threading, RLF to cap SQL cost, and middleware timeouts to protect client experience. Overlapping controls are fine if owners understand which layer fires first and what SQLCODE or network error the client will see.

After a violation spike, do not only raise ASUTIME. Ask whether the access path regressed, statistics are stale, or the user wrote a Cartesian join. Raising the limit without fixing the SQL teaches the system to accept waste.

Change management for governor tables

Treat DSNRLSTxx like production code. Keep DDL and INSERTs in version control, review changes, and test on a non-production ID first. When you START RLIMIT SCOPE(GROUP), every member enforces the same rules—great for consistency, painful if a bad row ships everywhere at once. Prefer a staged approach: local start on one member, validate, then group scope.

Document exception processes. Finance may need a quarterly query that legitimately exceeds normal limits. A temporary row with a clear end date beats granting installation SYSADM “just for today.” Remove expired exceptions; abandoned exception rows are how governors quietly die.

Measuring whether RLF is helping

After you activate a new governor ID, watch three signals for a week: count of RLF-related SQLCODES, CPU attributed to the governed authids or packages, and complaint volume from legitimate users. A healthy rollout shows fewer runaway threads without a surge of false positives. If false positives dominate, narrow rows or raise limits for known good packages instead of disabling RLF entirely.

Share dashboards with application owners. When they can see which statement shapes get limited, they improve SQL faster than when the database team only says “you hit the governor.” Pair that with EXPLAIN education so predictive warnings become a coaching tool, not just a locked door.

Remember that RLF does not replace sound grants, good indexes, or workload management. It is a safety net. The goal is fewer net activations over time because queries got better—not because everyone learned to use an exempt authid.

For bind-time controls, communicate windows clearly to developers. If RLFBIND rejects daytime binds, publish the approved bind hours and an emergency break-glass process that still avoids handing out installation SYSADM. Emergency rows with expiration dates beat permanent holes in the governor.

In mixed local and distributed estates, keep separate mental models for allied threads and DBATs, but one RLF table can cover both if your matching columns include the right location and package information. Test with a representative JDBC user and a representative TSO user before calling the change done. What looks governed in SPUFI may still escape through a different collection name used only by the driver packages.

When you retire a reporting server or rename a package collection, schedule an RLF row cleanup the same day. Orphaned rows that no longer match anything are harmless; orphaned wildcards that suddenly match a new collection after a rename are not. Include governor tables in your application migration checklist next to GRANTs and BIND plans.

Explain It Like I'm Five

The Resource Limit Facility is a babysitter for hungry queries. Dynamic SQL is a kid in a candy store (your CPU). The DSNRLST table is the rule sheet: “ReportUser may have this many candies.” START RLIMIT pins the rule sheet on the wall. If someone grabs too much, the babysitter says stop (reactive) or “that bag is too big before you open it” (predictive). Installation SYSADM is the grown-up who is not limited—so you do not hand that badge out for fun. DISPLAY RLIMIT checks that the babysitter is awake.

Exercises

1. Explain the difference between reactive and predictive governing with one example query each.
2. Why might a blank AUTHID row be riskier than a row for a single reporting ID?
3. Write the commands to activate ID=02 on one member versus the whole data sharing group.
4. A user hits an RLF error in JDBC but not in a batch package under a service ID. What do you compare first in DSNRLSTxx?
5. How would you use RLF to block daytime BINDs without blocking SELECT?