WLM and DB2: application environments and stored procedures

WLM meets Db2

Db2 itself runs in MSTR, DBM1, DIST, and related spaces. That is not where your COBOL stored procedure load module executes. For external routines, Db2 asks z/OS WLM to schedule work into a WLM-managed stored procedure address space (often called WLM-SPAS). WLM starts those servers using a JCL procedure you define—commonly based on IBM's DSNX9WLM sample.

WLM is also the z/OS component that classifies general CPU work (service classes, goals, and velocity). For this tutorial the focus is the application environment used for Db2 routines, not a full RMF course—though the same WLM policy still influences how those servers compete for CPU.

Native versus external stored procedures

Native SQL procedures are created with a SQL procedure body. They execute under the task of the caller. When the caller is a DDF DBAT (preemptible SRB path), much of that work can be zIIP-eligible. They do not occupy a WLM SPAS TCB the way an external COBOL procedure does.

External stored procedures are programs in a host language. You CREATE PROCEDURE ... EXTERNAL NAME ... WLM ENVIRONMENT name. At CALL time Db2 finds a free TCB in a server for that environment (or asks WLM to start another server). External procedures that run in WLM-established spaces use RRSAF implicitly—do not code your own attachment CONNECT, and do not call SRRCMIT/SRRBACK yourself or you can poison the transaction state.

WLM application environments and address spaces

A WLM application environment is a named definition with:

• Subsystem type — typically DB2 for Db2 routines
• Procedure name — the JCL PROC WLM starts (for example DB2AWLM1)
• Start parameters — often DB2SSN=&IWMSSNM, NUMTCB=n, APPLENV=name
• Limits on how many server address spaces WLM may create (including single-server options for routines that need exclusive resources)

Each started server is an address space with STEPLIB concatenations for SDSNLOAD, Language Environment, and your application load libraries. APF-authorize the concatenation when routines must run authorized. In data sharing, the SPAS must match the Db2 release of the member that received the CALL—use &IWMSSNM and aliases so one environment definition still picks the right libraries per member.

1
2
3
4
5
6
7
8
9
//* Conceptual WLM-SPAS procedure
//DB2AWLM1 PROC RGN=0M,DB2SSN=,NUMTCB=8,APPLENV=DB2APPL1
//IEFPROC EXEC PGM=DSNX9WLM,REGION=&RGN,TIME=NOLIMIT,
//            PARM='&DB2SSN,&NUMTCB,&APPLENV'
//STEPLIB  DD  DISP=SHR,DSN=DSNx.&DB2SSN..SDSNEXIT
//         DD  DISP=SHR,DSN=DSNx.&DB2SSN..SDSNLOAD
//         DD  DISP=SHR,DSN=DSNx.RUNLIB.LOAD
//CEEDUMP  DD  SYSOUT=*
//SYSPRINT DD  SYSOUT=*

NUMTCB and WLM scheduling

NUMTCB is the maximum number of concurrent task control blocks in that server available for end-user routine work. Each active external procedure needs one. Set it too low and CALL statements wait. Set it too high and you risk storage pressure or over-commit against Db2 thread and CPU capacity. Typical LE language guidance often lands in a moderate band (for example on the order of tens, tuned per shop)—measure rather than copy a rumor.

WLM scheduling decides when to start additional servers for the application environment based on queueing and policy. Db2 selects a TCB inside a server after catalog lookup of the routine. If you change NUMTCB, quiesce and resume the application environment so new spaces start with the new limit; a soft refresh may not rebuild TCB capacity the way you expect.

IBM also provides helpers such as DSN_WLM_APPLENV to define, install, activate, or remove application environments programmatically. That is useful for automation, but many shops still maintain environments through WLM ISPF panels.

WLM-managed procedures, isolation, and startup

WLM-managed procedures are the external routines assigned to an environment. You can isolate noisy or fragile programs into their own environment so a loop or leak does not starve payroll procedures sharing another SPAS. Separate environments also let you use different STEPLIB sets and NUMTCB values.

Stored procedure isolation here means operational isolation (separate servers), not SQL isolation levels—though each procedure still runs with whatever bind isolation its package uses. For sensitive authorized routines, use a dedicated environment with fully APF-authorized STEPLIB.

Procedure startup: first CALL after IPL or after quiesce may pay a cold start cost while WLM creates the address space and LE initializes. STAY RESIDENT and related CREATE PROCEDURE options influence whether the program stays loaded between calls. Warm environments with steady traffic hide that cost; sparse traffic feels it.

1
2
3
4
5
6
7
8
9
10
11
CREATE PROCEDURE PAYROLL.CALC_BONUS (
    IN EMPNO CHAR(6),
    OUT BONUS DECIMAL(9,2)
  )
  LANGUAGE COBOL
  EXTERNAL NAME BONUSCB
  WLM ENVIRONMENT DB2APPL1
  PARAMETER STYLE GENERAL WITH NULLS
  PROGRAM TYPE MAIN
  -- plus COLLID, SECURITY, DYNAMIC RESULT SETS, etc. as required
  ;

Procedure failure and WLM monitoring

Procedure failure ranges from a clean SQL error return to a system abend in the SPAS. Application SQLCODE/SQLSTATE go back to the caller for logic errors. Abends produce dumps (CEEDUMP, SYSUDUMP) and Db2/WLM messages. Severe failures can make WLM stop or replace a server. After repeated failures, check STEPLIB, LE options, unauthorized modules in an authorized library list, and whether NUMTCB=1 single-server routines are serialized behind a stuck call.

WLM monitoring should answer: Are servers up? Are CALLS queued? Is CPU delayed in the service class? Db2 accounting shows stored procedure elapsed and CPU; WLM/RMF shows server counts and delays; console messages show start failures. For DDF callers, also watch DBAT waits separately—WLM queueing and DBAT shortage are different bottlenecks.

• Confirm the CREATE PROCEDURE WLM ENVIRONMENT name matches the WLM definition
• Confirm the PROC starts and STEPLIB resolves for that DB2SSN
• Watch NUMTCB saturation versus number of servers
• Separate unstable routines into their own application environment
• Prefer native SQL procedures when the workload fits and zIIP matters

CREATE PROCEDURE options that interact with WLM

Several CREATE PROCEDURE clauses shape runtime behavior even after WLM is configured.PROGRAM TYPE SUB or MAIN affects how the load module is invoked.STAY RESIDENT YES or NO influences whether the program remains in memory between calls. RUN OPTIONS pass Language Environment options that can change storage and messaging behavior inside the SPAS. ASUTIME on the procedure can limit CPU for that routine independently of subsystem RLF rows.COMMIT ON RETURN changes whether Db2 commits when the procedure returns—dangerous if callers expected to keep an open unit of work.

SECURITY options control how the procedure's authorization ID is established (Db2, user, definer-style models depending on release and definition). Misaligned security plus a shared WLM environment is a frequent audit finding: one environment runs both low-privilege and high-privilege modules. Split environments when privilege levels differ sharply.

Java, REXX, and special NUMTCB cases

Not every language likes a high NUMTCB. Some Java or otherwise constrained routines are documented to need low concurrency—sometimes NUMTCB=1—because of JVM or resource exclusivity. If you place such a routine in a busy multi-TCB environment, you can see baffling failures that look like “Db2 is broken” but are really environment design. Read the routine's language requirements before copying NUMTCB=20 from a COBOL payroll environment.

Similarly, routines that allocate exclusive z/OS resources (certain files, ENQs, or external connections) may need the WLM single-server style limit so only one address space exists for that application environment per system. That serializes throughput by design; do not “fix” slowness by raising NUMTCB when the real requirement was exclusivity.

Refresh, quiesce, and migration discipline

After you change STEPLIB contents, LE options, or NUMTCB, refresh discipline matters. Quiescing the application environment stops new work from landing on old servers and allows WLM to start fresh spaces. A half-refreshed environment—some old servers, some new—creates intermittent bugs that waste days. During Db2 version coexistence in data sharing, mismatched SPAS code versus DBM1 level is a hard error; plan member rolling upgrades with library aliases and &IWMSSNM so each member keeps a coherent stack.

Monitor first CALL latency after IPL or after quiesce. If business processes depend on a morning spike of CALL statements, consider warming the environment with a harmless health-check procedure so users do not pay cold-start cost on the critical path.

UDFs and the same WLM machinery

External user-defined functions often ride the same WLM application environment model as external stored procedures. A scalar UDF that fires per row can multiply CALL traffic into the SPAS far beyond what a single stored procedure CALL would do. That is why a “harmless” UDF in a SELECT against a million-row table can exhaust NUMTCB and queue deeply. Prefer native SQL functions or set-oriented SQL when possible; when you must use an external UDF, size the environment for row-level invocation rates, not for occasional procedure calls.

Document which routines share an environment. Capacity planning fails when nobody knows that the new scoring UDF landed in the same APPLENV as overnight batch procedures.

Explain It Like I'm Five

Db2 is a library desk. A native SQL procedure is a librarian who answers at the same desk. An external stored procedure is a specialist in another room. WLM is the building manager who unlocks those rooms (address spaces) and decides how many chairs (NUMTCB) each room gets. Application environments are the room name tags. If every chair is full, new visitors wait in the hallway. If a specialist knocks over a shelf, that room may need a cleanup (failure) while other rooms keep working—especially if you gave noisy specialists their own room (isolation).

Exercises

1. Write one sentence each for when you would choose a native versus an external procedure.
2. Given NUMTCB=4 and one server, how many of those external procedures can run at once?
3. Why does data sharing care about &IWMSSNM in the start parameters?
4. List three signals that an application environment is undersized.
5. Explain why changing NUMTCB usually needs quiesce/resume of the environment.