DB2 profiles and system profile monitoring

What a Db2 profile is

A system profile is a named set of criteria plus one or more actions. The criteria answer “which threads or connections?” The actions answer “what should Db2 do when those threads or connections show up?” Classic uses include protecting DDF from a runaway JDBC pool, giving a batch warehouse tool a longer idle timeout than OLTP, and warning operators before you start failing new connections.

Profiles are not a replacement for RACF, BIND options, or Resource Limit Facility (RLF), but they sit in the same toolbox. RLF often caps CPU for dynamic SQL. Profiles often cap concurrency and idle time for remote (and, in newer releases, local) work. Many shops use both.

Profile tables

IBM creates the profile objects in job DSNTIJSG. The two tables you edit every day are:

• SYSIBM.DSN_PROFILE_TABLE — one row per profile; holds PROFILEID, filters, and PROFILE_ENABLED (Y or N)
• SYSIBM.DSN_PROFILE_ATTRIBUTES — one or more rows per PROFILEID; holds KEYWORDS and ATTRIBUTE1 / ATTRIBUTE2 / ATTRIBUTE3

After -START PROFILE, Db2 writes acceptance status into history tables such as DSN_PROFILE_HISTORY and DSN_PROFILE_ATTRIBUTES_HISTORY. If STATUS does not start with ACCEPTED, the filter combination or keyword was rejected—fix the INSERT before you trust production behavior.

Filtering columns on DSN_PROFILE_TABLE

Not every column combination is legal for every keyword. IBM documents exclusive-OR style filter groups: for many monitor functions you pick LOCATION, or PRDID, or ROLE/AUTHID, or COLLID/PKGNAME, or one CLIENT_* column—not a free mix of everything. Db2 13 also expanded support for local threads with similar exclusive filter options.

1
2
3
4
5
6
7
8
9
INSERT INTO SYSIBM.DSN_PROFILE_TABLE
  (PROFILEID, LOCATION, PROFILE_ENABLED)
VALUES
  (100, '10.1.2.50', 'Y');
 
INSERT INTO SYSIBM.DSN_PROFILE_ATTRIBUTES
  (PROFILEID, KEYWORDS, ATTRIBUTE1, ATTRIBUTE2)
VALUES
  (100, 'MONITOR THREADS', 'WARNING', 50);

Connection profiles

A connection profile uses keywords such as MONITOR CONNECTIONS (and related ALL CONNECTIONS forms). ATTRIBUTE2 is the threshold for how many remote connections matching the filter may exist. When the queue or suspension count hits the threshold, ATTRIBUTE1 decides the outcome:

• WARNING (and DIAGLEVEL variants) — console messages; work continues
• EXCEPTION (and DIAGLEVEL variants) — fail further connection requests (commonly SQLCODE -30041) and message

Think of this as a finer-grained cousin of CONDBAT. Global ZPARMs still apply; the profile adds a per-application ceiling so one chatty middleware tier cannot exhaust the DDF connection budget for everyone else.

Thread profiles

A thread profile uses MONITOR THREADS. ATTRIBUTE2 is the allowed count of active server threads from the matching remote application. ATTRIBUTE3 can further limit how many of those threads may sit suspended waiting under the profile. Again, WARNING versus EXCEPTION in ATTRIBUTE1 controls soft versus hard enforcement.

This is the per-app cousin of MAXDBAT. When a microservice suddenly opens hundreds of JDBC connections that each want a DBAT, MONITOR THREADS is how you contain the blast radius without lowering MAXDBAT for the entire data sharing member.

Monitoring profiles and idle threads

Monitoring profiles is the umbrella term for using profile tables to watch and optionally enforce thresholds. Beyond threads and connections, MONITOR IDLE THREADS times out idle DBATs for matching work. ATTRIBUTE2 is seconds of idle time. Matching threads are not limited by the global IDTHTOIN subsystem parameter—so you can allow a reporting tool to stay idle longer than OLTP, or cancel a forgotten connection sooner.

Important design detail: if ATTRIBUTE1 is only WARNING, a matching idle thread can remain idle indefinitely from the profile’s point of view. IBM recommends adding another attributes row with the same PROFILEID and an EXCEPTION style value so you still have a hard stop. EXCEPTION for idle threads can abort the thread, pool the DBAT, and terminate the connection. EXCEPTION_ROLLBACK variants exist for special cases—read IBM’s notes carefully, because some rollback events are hidden from the remote client and can look like mysterious loss of cursors or declared temporary tables.

Exception profiles and profile-based thresholds

People say exception profiles when ATTRIBUTE1 uses EXCEPTION, EXCEPTION_DIAGLEVEL1/2/3, or related EXCEPTION_ROLLBACK forms. Those values are how profile-based thresholds become governors instead of dashboards. DIAGLEVEL choices change message granularity: summary messages for all profiles, messages for a specific profile, or a message on every exception event (for example DSNT774I plus DSNT772I patterns).

Threshold numbers live mainly in ATTRIBUTE2 (counts or seconds) and sometimes ATTRIBUTE3 (for example suspended-thread caps under MONITOR THREADS). Always document the intended ATTRIBUTE1 action next to the number in your runbook—50 WARNING and 50 EXCEPTION are very different production experiences.

Other profile attributes

Monitoring is the most common story, but KEYWORDS go further. Profiles can set special registers or built-in global variables for remote applications, influence accelerator EXPLAIN behavior, model buffer pool sizes for EXPLAIN on a test system, and participate in dynamic SQL stabilization workflows. Each keyword has its own ATTRIBUTE1/2/3 contract in the IBM documentation for DSN_PROFILE_ATTRIBUTES. Treat the keyword list as a catalog of governors and helpers, not only as “connection limiting.”

For remote-only attributes such as GLOBAL_VARIABLE, remember that an explicit SET in the application overrides the profile. Profiles fill defaults; they do not freeze application code that later changes the same register or variable.

START PROFILE, STOP PROFILE, DISPLAY PROFILE

1
2
3
4
-DISPLAY PROFILE
-START PROFILE
-DISPLAY PROFILE
-STOP PROFILE

Operational checklist after every change:

• Confirm PROFILE_ENABLED = 'Y' on the rows you intend to activate
• Issue -START PROFILE (or rely on PROFILE_AUTOSTART if your shop sets that subsystem parameter to YES at Db2 start)
• Query history STATUS columns; fix rejected rows; START again
• For DDF-related monitoring, ensure DDF is up—even some local-thread profile uses still expect the DDF address space loaded
• Use -DISPLAY PROFILE in health checks so you know profiling is active after IPLs and maintenance

-STOP PROFILE turns the function off. Definitions remain in the tables; you are not dropping PROFILEID rows. That makes STOP useful for incident windows when a bad threshold is causing mass -30041 failures and you need relief before you can edit SQL.

Profile-based monitoring in practice

Start with WARNING profiles and watch console traffic and application retries for a week. Promote hot spots to EXCEPTION only when the business agrees that failing excess work is better than letting one client starve the member. Keep LOCATION filters accurate when clients move behind new load balancers—stale IP profiles silently protect nothing.

Combine profiles with ordinary DDF monitoring: DISPLAY THREAD, accounting classes, and statistics for DBAT reuse. Profiles answer “how many may this app have?” Accounting answers “what is that app doing with the threads it already has?”

1
2
3
4
5
6
7
8
9
10
11
-- Example: stricter idle timeout for one client application name
INSERT INTO SYSIBM.DSN_PROFILE_TABLE
  (PROFILEID, CLIENT_APPLNAME, PROFILE_ENABLED)
VALUES
  (200, 'BADPOOLAPP', 'Y');
 
INSERT INTO SYSIBM.DSN_PROFILE_ATTRIBUTES
  (PROFILEID, KEYWORDS, ATTRIBUTE1, ATTRIBUTE2)
VALUES
  (200, 'MONITOR IDLE THREADS', 'WARNING', 120),
  (200, 'MONITOR IDLE THREADS', 'EXCEPTION', 300);

Explain It Like I'm Five

Imagine Db2 is a busy playground. Global rules say “only 100 kids on the swings.” A profile is a special note on the gate: “Kids from the blue bus may only take 10 swings,” or “If someone sits on a swing without moving for three minutes, ask them to get off.” The note lives on a clipboard (the profile tables). When the teacher blows the whistle (START PROFILE), the notes count. WARNING is a polite reminder. EXCEPTION is taking the swing away. STOP PROFILE puts the clipboard in the desk without throwing the notes out.

Exercises

1. Insert a DSN_PROFILE_TABLE row filtered by LOCATION and a MONITOR THREADS WARNING attribute with ATTRIBUTE2 = 25. List which history table you would check after START PROFILE.
2. Explain the difference between MONITOR CONNECTIONS and MONITOR THREADS in one short paragraph each.
3. Design an idle-thread profile with both WARNING and EXCEPTION rows for the same PROFILEID. Pick ATTRIBUTE2 values and justify them.
4. Write the three console commands to display, start, and stop profiling.
5. Given SQLCODE -30041 during a traffic spike, describe how you would decide whether a profile EXCEPTION threshold is involved.