Other DB2 subsystem parameters

How to read this parameter family

A subsystem parameter establishes a Db2-wide value, limit, or default. Administrators generate the parameter module through installation jobs and use documented online commands for parameters that IBM allows to change dynamically. Never assume that editing an installation value changes a running member immediately. Check the parameter's update scope, active function level, and whether it is member-specific or expected to be consistent across a data sharing group.

You can inspect active values with your site's administration tooling or the IBM-supplied SYSPROC.ADMIN_INFO_SYSPARM stored procedure. Treat the resulting report as the truth for the running subsystem. An old installation worksheet can describe what somebody intended years ago, not necessarily what is active today.

1
2
3
4
5
6
7
8
9
-- Typical administrative pattern; exact result columns depend on Db2 level.
CALL SYSPROC.ADMIN_INFO_SYSPARM(
  'STORMXAB',
  ' ',
  ' '
);
 
-- Also inspect generated DSNZPARM reports and change history.
-- Do not infer an active value from a sample DSNTIJUZ member.

Data sharing parameters and group identity

DSHARE and GROUP_NAME awareness

DSHARE identifies whether a subsystem is enabled to participate as a member of a Db2 data sharing group. It is not a switch to experiment with on a running standalone subsystem. Enabling data sharing is an installation and migration project involving shared catalog and directory data, member logs, BSDS updates, coupling facility structures, and Parallel Sysplex definitions.

The group name is the durable identity shared by the members. Be aware of names such as GROUP_NAME on reports and installation material, but do not treat the group name as an everyday tuning ZPARM. It is coordinated through data sharing installation metadata and the bootstrap data sets. Every member has its own subsystem parameter module. Some settings can legitimately differ by member; settings that describe shared behavior should follow IBM's consistency guidance.

RETLWAIT and retained locks

If a member fails while holding locks, the group cannot simply forget them. Some locks become retained locks until recovery determines that they can be released. RETLWAIT controls how long a request is willing to wait for an incompatible retained lock. Its numeric value is a multiplier applied to the normal timeout for the connection type. With the default value of zero, Db2 rejects the lock request immediately and returns a resource-unavailable condition instead of waiting.

Raising RETLWAIT can hide a brief recovery interval from an application, but it also makes transactions wait longer. The right choice depends on recovery objectives, application retry design, and typical peer-recovery duration. It is not a substitute for fast member restart or tested retry logic.

Peer recovery and coupling facility concurrency

Peer recovery means another member helps recover the failed member's work. Db2 releases retained locks as recovery progresses, allowing group work to resume safely. Controls and defaults around peer recovery have changed across Db2 releases and maintenance. Treat PEER_RECOVERY-style settings as recovery-policy controls: coordinate them with automation, restart-light procedures, log capacity, and operational runbooks rather than changing one field in isolation.

Coupling facility concurrency also has subsystem-level influences: lock and list handling, castout and write behavior, structure rebuild and duplexing support, and thresholds can affect how members cooperate. At this level, remember the boundary. A ZPARM can influence how a member uses shared services, but the CF structures themselves are defined and sized in CFRM policies and related Sysplex configuration. Structure placement, duplexing, failure isolation, and CF capacity are not created by a single Db2 parameter.

GBPCACHE illustrates the other side of that boundary. It is an object-level table space or partition attribute that controls what pages participate in group buffer pool caching. It is not a subsystem ZPARM. A group buffer pool is a CF structure; the object's GBPCACHE choice affects use of that structure; subsystem settings influence Db2 member behavior around it. Keep those three layers separate during diagnosis.

1
2
3
4
5
6
7
8
9
10
11
12
Data sharing configuration layers
---------------------------------
Db2 member ZPARMs
  DSHARE, RETLWAIT, recovery and concurrency controls
        |
Db2 object definition
  GBPCACHE attribute on a table space or partition
        |
Parallel Sysplex / CFRM
  Lock, list, SCA, and group buffer pool structures
 
One layer can influence another, but none replaces the others.

Stored procedure protection parameters

STORMXAB: stop repeated failures

STORMXAB specifies how many abnormal terminations are allowed for a stored procedure or an external user-defined function invocation before Db2 rejects later CALLs. The documented range is 0 through 255. A value of zero means the first abend causes subsequent calls to be rejected. That behavior can feel strict, but it protects the subsystem from repeatedly dispatching known-broken code.

When this threshold is reached, inspect the routine's job log, language environment, LE run-time options, authorized libraries, and WLM address space. Raising the threshold without correcting the fault turns containment into an abend loop.

STORTIME: bound the wait

STORTIME is the time Db2 waits for an SQL CALL or applicable external function invocation before failing the SQL request. Current documentation describes values from 5 through 1800 seconds plus NOLIMIT, with 180 seconds as the traditional default. NOLIMIT is dangerous: if the environment is unavailable or routine code never completes, the caller can wait until the thread is canceled.

A timeout does not prove that the routine itself is slow. The delay can occur while WLM starts an address space, while requests queue behind a concurrency cap, during language initialization, or inside the routine. Monitor each phase before tuning the global timeout.

MAX_NUM_CUR, not MAX_NUM_CUR_OPT

The accurate subsystem parameter is MAX_NUM_CUR. It limits the number of open cursors, including allocated cursors, for each thread at a Db2 site. The documented default is commonly 500, with a range up to 99999. The name MAX_NUM_CUR_OPT is not the standard Db2 for z/OS ZPARM. You might see similar labels in monitoring products, but verify their mapping before treating them as IBM parameter names.

MAX_NUM_CUR appears on the stored procedure installation panel because routines can expose cursor-heavy behavior, but its definition is per thread, not “per stored procedure.” If an application needs hundreds of simultaneous cursors, first check whether it closes cursors correctly. A larger limit consumes more resources and can postpone discovery of a leak.

WLMENV and WLM-managed address spaces

External stored procedures and user-defined functions do not normally execute in the caller's application address space. Db2 sends eligible routine work to a z/OS Workload Manager application environment. WLM starts and stops server address spaces using the JCL procedure named in that environment and manages capacity according to workload goals.

WLMENV is the default environment name used when an applicable CREATE FUNCTION, CREATE PROCEDURE, or advanced-trigger definition does not explicitly provide an environment. It is a name, not an address-space count and not the WLM service class. The environment definition points to a startup procedure; WLM may start multiple server address spaces for demand. Db2 dispatches routine requests to those spaces and enforces Db2 limits such as timeouts and failure thresholds.

The default is resolved when the object is created and stored in the catalog. Changing WLMENV later does not silently migrate existing routines. Production sites often define separate environments for Java, native code, security boundaries, resource profiles, or criticality. An explicit WLM ENVIRONMENT clause makes that intent visible and avoids accidental dependency on a subsystem default.

1
2
3
4
5
6
7
8
9
10
CREATE PROCEDURE APP.RECALCULATE_BALANCE
  (IN ACCOUNT_ID BIGINT)
  LANGUAGE COBOL
  EXTERNAL NAME 'ACCTLIB!RECALC'
  WLM ENVIRONMENT ACCTWLM
  PARAMETER STYLE GENERAL;
 
-- If WLM ENVIRONMENT were omitted where allowed,
-- Db2 could use the WLMENV subsystem default and
-- record the resolved environment in the catalog.

XML storage and DOCID parameters

XML parsing, validation, and manipulation can materialize values in memory. XMLVALA limits the XML value storage attributable to one user, while XMLVALS limits XML value storage for the subsystem. Think of them as two safety rails: the user limit prevents one thread from dominating XML storage, and the system limit protects the whole member. If XML work fails at a limit, determine whether the document, query, or concurrency level is unreasonable before increasing storage.

Db2 assigns internal document identifiers to XML values. With XML_RANDOMIZE_DOCID=NO, identifiers are generated sequentially. YES randomizes them and can improve performance when many threads insert XML documents concurrently by reducing concentration around the same key range. The setting applies when relevant XML columns or tables are established; it does not rewrite existing identifiers. A table that has begun randomized generation cannot be converted back to sequential generation merely by resetting the ZPARM.

Temporal defaults: distinguish ZPARMs from bind behavior

Temporal tables are a common source of parameter-name confusion. In current Db2 for z/OS terminology, SYSTIMESENSITIVE and BUSTIMESENSITIVE are bind options, not subsystem parameters. BUSINESS_POLICY and TEMPORAL_SENSITIVITY are not standard Db2 for z/OS ZPARM names. If a vendor tool displays those labels, find the underlying bind option, special register, or product-specific policy.

CURRENT TEMPORAL SYSTEM_TIME and CURRENT TEMPORAL BUSINESS_TIME are special registers. Their normal starting value is null. An application can SET a timestamp. If a package is bound SYSTIMESENSITIVE(YES), a non-null CURRENT TEMPORAL SYSTEM_TIME can make an eligible reference to a system-period temporal table behave as though it included FOR SYSTEM_TIME AS OF. BUSTIMESENSITIVE(YES) provides the corresponding interaction for application-period temporal tables and CURRENT TEMPORAL BUSINESS_TIME.

This design separates responsibilities. The table definition establishes SYSTEM_TIME or BUSINESS_TIME periods. The special register supplies a session's effective timestamp. The bind option decides whether SQL in a package is sensitive to that register. Db2 application defaults can seed bind behavior at a site, and an application defaults module can establish special-register defaults, but there is no universal “temporal sensitivity ZPARM” that replaces package binding and session state.

1
2
3
4
5
6
7
8
9
10
11
12
-- Session chooses an effective business timestamp.
SET CURRENT TEMPORAL BUSINESS_TIME =
  TIMESTAMP('2026-01-31-12.00.00');
 
-- With BUSTIMESENSITIVE(YES), this reference can receive
-- an implicit FOR BUSINESS_TIME AS OF specification.
SELECT POLICY_ID, PREMIUM
FROM INSURANCE_POLICY;
 
-- System-time sensitivity is independent.
SET CURRENT TEMPORAL SYSTEM_TIME =
  TIMESTAMP('2026-01-31-12.00.00');

Be especially careful with data changes. A non-null temporal register combined with a sensitive package can put an implicit period specification into effect, and Db2 can reject updates that are not valid under that temporal context. Reset the register to null or bind the package insensitive when that behavior is not desired.

Accelerator subsystem parameters

ACCEL: startup policy

ACCEL controls accelerator startup policy. NO keeps accelerator use unavailable, AUTO allows Db2 to start accelerator connectivity automatically, and COMMAND requires an operator action such as START ACCEL. COMMAND is useful when operations must verify accelerator health or synchronization before admitting work. AUTO reduces manual steps. NO is appropriate when acceleration is not installed or must be deliberately disabled.

ACCELMODEL and query modeling

ACCELMODEL controls modeling or eligibility analysis used to estimate which queries might benefit from acceleration, even when normal execution stays on Db2. Modes and spelling can vary with release and maintenance, including concepts such as NONE, ENABLE, ENABLE_WITH_FAILBACK, and ELIGIBLE. Modeling information can appear in EXPLAIN-related tables and help teams identify candidates before moving production execution. Because this area evolves, verify the exact accepted values at your active function level.

QUERY_ACCELERATION: the execution rule

QUERY_ACCELERATION is both a familiar bind/default concept and the source for acceleration behavior exposed to applications. Its modes answer two questions: should Db2 consider the accelerator, and what happens if eligible work cannot run there? NONE avoids acceleration. ENABLE permits acceleration when possible. ENABLE WITH FAILBACK can return eligible work to native Db2 after an accelerator failure. ELIGIBLE can require accelerator execution for eligible queries, while ALL is stricter about sending work. Exact availability and semantics depend on level, so bind and special-register documentation for the installed release wins.

Dynamic applications can use CURRENT QUERY ACCELERATION. Static SQL receives package bind behavior. A subsystem or application default is only the starting point; package options and allowed special-register changes determine the effective value for a statement.

QUERY_ACCEL_OPTIONS: capability bits

QUERY_ACCEL_OPTIONS is not a simple yes/no switch. It is a numeric option mask whose bits enable specific capabilities or compatibility behavior. Examples across supported levels include INSERT FROM SELECT support and choices used during accelerator-version coexistence. IBM updates these meanings through APARs and function levels. Record the decoded options in change tickets; a bare number is not useful to a future DBA.

GET_ACCEL_ARCHIVE: archived rows are a separate choice

Accelerator-only archive tables can hold rows no longer present in the active Db2 table. GET_ACCEL_ARCHIVE controls whether qualifying queries may retrieve those archived rows. It appears through bind/default behavior and the CURRENT GET_ACCEL_ARCHIVE special register for dynamic SQL. YES widens the logical data visible to a query; NO restricts it to the active data.

Archive retrieval and query acceleration are related but independent. Setting CURRENT GET_ACCEL_ARCHIVE to YES does not make an unsupported query eligible, does not start an accelerator, and does not override QUERY_ACCELERATION. Operations also need a plan for what should happen if archived data is requested while the accelerator is unavailable.

1
2
3
4
5
6
7
8
9
10
11
-- Dynamic SQL controls, subject to site policy and privileges
SET CURRENT QUERY ACCELERATION = ENABLE WITH FAILBACK;
SET CURRENT GET_ACCEL_ARCHIVE = YES;
 
SELECT CUSTOMER_ID, ORDER_TOTAL
FROM ARCHIVE_ENABLED_ORDERS
WHERE ORDER_DATE < DATE('2024-01-01');
 
-- Effective behavior still depends on ACCEL startup,
-- query eligibility, accelerator state, package/default settings,
-- and QUERY_ACCEL_OPTIONS capabilities.

A safe review workflow

1. Identify whether the observed behavior belongs to a subsystem parameter, object attribute, bind option, special register, or external z/OS definition.
2. Capture the active value from Db2, the member name, function level, and relevant maintenance. Do not rely on a copied sample job.
3. For data sharing, decide whether the setting is member-specific or should be consistent across the group.
4. Reproduce the issue with measurements: waits and retained locks, WLM queue time, XML storage, temporal register values, or accelerator EXPLAIN reason codes.
5. Read the update rules and fallback plan before changing a value. Some changes require a recycle or affect only newly created objects.
6. Test with representative concurrency. A parameter that works for one CALL or one XML insert can fail under a production surge.

Explain It Like I'm Five

Imagine Db2 is a large school. Data sharing settings tell several school buildings how to use the same library and how long to wait when one building has locked a book. Coupling facilities are the shared checkout desk; GBPCACHE is a rule written on a particular book collection, not a rule for the whole school. Stored procedure settings stop a broken science experiment from exploding repeatedly and stop children waiting forever outside the lab. WLMENV tells the school which lab should run an experiment. XML limits decide how much giant paper a student and the entire school may spread out. Temporal controls let a student say, “show me the records as they looked last Tuesday.” Accelerator settings decide whether a very fast helper building may answer a large question, whether a teacher must invite it first, and whether it may look in the archive basement.

Exercises

1. Draw the three data sharing layers: member ZPARMs, object-level GBPCACHE, and CF structure definitions. Place DSHARE, RETLWAIT, and GROUP_NAME awareness correctly.
2. A routine abends once and later CALLs are rejected. Explain how STORMXAB can cause this and list four logs or definitions you would inspect before changing it.
3. Compare STORTIME with WLM capacity. How could a healthy routine time out before its code begins executing?
4. Correct the fictional name MAX_NUM_CUR_OPT, state the scope of MAX_NUM_CUR, and explain why raising it can hide an application defect.
5. Write two SELECT scenarios against a bitemporal table: one package sensitive to CURRENT TEMPORAL BUSINESS_TIME and another package insensitive to it.
6. Explain why ACCEL=AUTO, CURRENT QUERY ACCELERATION, QUERY_ACCEL_OPTIONS, and CURRENT GET_ACCEL_ARCHIVE answer four different questions.