After you choose BIND PACKAGE or BIND PLAN, a second group of options decides how the SQL actually runs: how soon locks drop, whether cursors must see the latest committed row, whether queries may run in parallel, and whether Db2 re-prepares dynamic SQL after COMMIT. This page covers ISOLATION, CURRENTDATA, RELEASE, DEGREE, KEEPDYNAMIC, and REOPT for DB2 for z/OS.
You specify them on DSN BIND PACKAGE, BIND PLAN, BIND SERVICE, and the matching REBIND subcommands. Catalog columns on SYSIBM.SYSPACKAGE and SYSIBM.SYSPLAN record the chosen values. Package options usually win at run time for the SQL in that package. For a local BIND PACKAGE, some options default to the plan value if you omit them.
These options are not valid for REBIND of native SQL procedure packages or advanced triggers (ISOLATION, CURRENTDATA, DEGREE, KEEPDYNAMIC, REOPT, and RELEASE all carry that restriction in the Command Reference). Change those packages with ALTER PROCEDURE / ALTER TRIGGER or a new CREATE, not with a casual REBIND PACKAGE of the generated package name.
123456DSN SYSTEM(DB2A) BIND PACKAGE(PAYROLL) MEMBER(PAYUPD) - ISOLATION(CS) CURRENTDATA(NO) - RELEASE(COMMIT) DEGREE(1) - KEEPDYNAMIC(NO) REOPT(NONE) END
ISOLATION is the degree to which this application is isolated from other concurrent work. It mainly controls how soon Db2 can release S and U locks on rows or pages. Claims on objects can still block utilities even when your SQL isolation looks “light.”
| Value | Name | What it guarantees |
|---|---|---|
| CS | Cursor stability | Do not read uncommitted changes; locks on unchanged rows can drop as you move on |
| RS | Read stability | Rows already returned cannot change until commit; new rows may still appear |
| RR | Repeatable read | The qualifying set cannot change until commit; inserts that would qualify are blocked |
| UR | Uncommitted read | Read-only SQL may skip S locks (except LOB rules) and can see uncommitted data |
| NC | No commit | For some non-z/OS servers only; Db2 for z/OS does not support NC and maps it to UR |
Cursor stability is the default for BIND PLAN and BIND SERVICE, and the usual choice for BIND PACKAGE. Like repeatable read, CS will not return a row another process has changed but not yet committed. Unlike RR, CS does not freeze rows you already read: after you FETCH off a page or row, another transaction may update that row before you COMMIT.
IBM’s performance guidance is: bind most applications with ISOLATION(CS) and CURRENTDATA(NO). CS lets Db2 release acquired locks as soon as possible. CURRENTDATA(NO) lets Db2 skip many locks through lock avoidance.
Read stability keeps every row that satisfied your search condition from being changed by others until you commit or roll back. Other applications may insert new rows that would also have qualified (phantoms). Use RS when the rows you already returned must stay put, but you do not need a frozen answer set. If a remote server does not support RS, it uses RR.
Repeatable read is the strictest common level. Rows you evaluated must not change, and other processes cannot insert rows that would join your answer set, until you commit. That extra locking reduces concurrency. Reserve RR for the few programs that really need a stable set (inventory allocation that must not “see” a new row appear mid-transaction is a classic case).
Uncommitted read does not give CS/RR guarantees. Except for LOB data, UR avoids taking S locks and can return data another process has not committed—and might later roll back. You may specify UR only for read-only operations: SELECT, SELECT INTO, or FETCH on a read-only cursor. If you bind UR on an updating statement, Db2 uses CS for that statement.
UR is useful for dirty reporting against huge tables where a slightly stale or uncommitted picture is acceptable. Do not use it for financial postings or anything that must match a later COMMIT of another job.
NC (no commit) exists for some servers that are not Db2 for z/OS. Db2 for z/OS does not support NC. If a server does not support NC, it uses UR.
Defaults: BIND PLAN and BIND SERVICE use CS. BIND PACKAGE on a local server inherits the plan value; on a remote server the default is CS. REBIND keeps the existing value. You cannot REBIND PACKAGE a specified isolation back to “inherit the plan”; use BIND PACKAGE ACTION(REPLACE) for that.
CURRENTDATA applies when ISOLATION(CS) is in effect. It answers: for read-only and ambiguous cursors, must the data stay current until the next FETCH?
Do not use CURRENTDATA(NO) if the program will issue DELETE WHERE CURRENT OF against an ambiguous cursor after OPEN. You can get a negative SQLCODE if the cursor is using block fetch, query parallelism, or is positioned on a row another process already changed. CURRENTDATA(YES) is not valid for REBIND of native REST service packages. You cannot change CURRENTDATA with a remote REBIND; use BIND REPLACE, FREE then BIND ADD, or a local rebind at the package’s location.
When you use CS and CURRENTDATA(NO), shops often set the SKIPUNCI subsystem parameter to YES so readers do not wait on uncommitted inserts.
RELEASE decides when Db2 frees table, partition, and table space locks (and some other cached resources)—not page, row, LOB, or XML locks. Those short locks still follow isolation and commit rules.
Resources drop at each commit, unless a cursor defined WITH HOLD must keep position. If the program touches the object again, it reacquires the lock. What “commit” means depends on the attachment:
COMMIT is the default for BIND PLAN and BIND SERVICE. A local BIND PACKAGE inherits the plan value; a remote BIND PACKAGE defaults to COMMIT.
Locks and many parent resources stay until the thread ends. That avoids repeated lock and EDM work across many commits in one batch job or a CICS protected thread. The package or plan can grow because more items stay resident. High-performance DBATs require RELEASE(DEALLOCATE).
Partition locks for one table space always share one duration. If any package on the thread uses DEALLOCATE for that space, all partition locks promote to DEALLOCATE. If an application mixes packages, COMMIT-duration locks can be promoted when a later package with DEALLOCATE touches the same space.
A local package inherits the plan’s RELEASE value, even if the package was bound remotely. There is no plan for RRSAF-only packages, copied packages with no local plan, or utility packages—those fall back to COMMIT. INHERITFROMPLAN is not valid on REBIND of native REST services.
Most dynamic SQL behaves as RELEASE(COMMIT) even if the package says DEALLOCATE. Two important exceptions: (1) RELEASE(DEALLOCATE) plus KEEPDYNAMIC(YES) with CACHEDYN=YES honors DEALLOCATE for dynamic SELECT, INSERT, UPDATE, and DELETE; (2) prepared INSERT, UPDATE, DELETE, and MERGE that reference declared temporary tables can be kept past commit under DEALLOCATE unless the table was defined ON COMMIT DROP TABLE.
DEALLOCATE can block BIND REPLACE, REBIND, and online REORG materialization. With PKGREL_COMMIT=YES (the default), Db2 can still release an active DEALLOCATE package at COMMIT so those operations can break in. MODIFY DDF PKGREL(COMMIT) disables DEALLOCATE behaviour for DBATs at a server.
DEGREE controls query parallelism for static SQL in the package.
For plans, DEGREE has no effect on its own; the packages carry the setting. DEGREE(ANY) can grow EDM pool use by roughly 50% to 70%. Start with DEGREE(1) unless you have measured a query that benefits from CPU or I/O parallelism and the subsystem parallelism limits are set.
KEEPDYNAMIC decides whether prepared dynamic SQL survives COMMIT and ROLLBACK. Default is NO for BIND PACKAGE and BIND PLAN.
KEEPDYNAMIC(YES) DDF server threads stay active and are subject to idle thread timeout. You must not combine KEEPDYNAMIC(YES) with REOPT(ALWAYS); REOPT(ONCE) is allowed. DRDA clients that use WITH HOLD cursors often see fewer network messages because Db2 can close a held cursor when no rows remain. MAXKEEPD limits how many kept dynamic statements a thread may retain.
At bind time, host variables, parameter markers, and special registers are unknown, so the optimizer uses default filter factors. REOPT can rebuild the access path at run time when the real values are known. Specifying ALWAYS, AUTO, or ONCE also sets DEFER(PREPARE).
| Value | When Db2 reoptimizes | Notes |
|---|---|---|
| NONE | Bind-time path only | Synonym NOREOPT(VARS); default for new binds |
| ALWAYS | Reoptimize every execution | Synonym REOPT(VARS); cannot combine with KEEPDYNAMIC(YES) |
| ONCE | First open or first run of a dynamic statement | Reuse that path while the statement stays in the cache |
| AUTO | First run, then again if values look different | Useful when parameter marker filter factors swing widely |
For dynamic SQL, REOPT also controls whether literal values count when you PREPARE with CONCENTRATE STATEMENTS WITH LITERALS. Literals are considered only with REOPT(ONCE) or REOPT(AUTO).
ALWAYS is expensive if the statement runs thousands of times per thread—each execution pays optimization CPU. ONCE is the usual compromise for cached dynamic SQL with skewed parameter values: optimize once with the first set of values. AUTO watches later executions and builds a new path if estimated filter factors change enough.
A typical OLTP COBOL package looks like this:
123456BIND PACKAGE(CICSPROD) MEMBER(ORDRUPD) - ACTION(REPLACE) - ISOLATION(CS) CURRENTDATA(NO) - RELEASE(DEALLOCATE) - DEGREE(1) - KEEPDYNAMIC(NO) REOPT(NONE)
A nightly reporting package that must not block updaters, and that runs one heavy SELECT with host variables, might use ISOLATION(UR) on the read-only program, DEGREE(ANY) if parallelism helps, and REOPT(ALWAYS) if the host variable ranges are wild. A JDBC package that PREPAREs once per connection often uses KEEPDYNAMIC(YES) and REOPT(ONCE) with the global dynamic statement cache (CACHEDYN=YES).
Catalog check after bind: SYSIBM.SYSPACKAGE columns ISOLATION, CURRENTDATA (DEFERPREP / related flags), RELEASE, DEGREE, KEEPDYNAMIC, and REOPT (or the older encoding of reoptimization) show what you actually bound—not what you meant to type in last week’s JCL.
Isolation is the “please do not grab my crayons” rule. CS means nobody changes the crayon you are holding, but they may change the one you already put down. RR means the whole coloring page stays frozen until you finish. UR means you might color using a crayon someone else is still thinking about taking back. CURRENTDATA is whether you insist the picture on the page cannot change between glances. RELEASE is when you put the crayon box away—after each little pause (COMMIT) or only when you leave the table (DEALLOCATE). DEGREE is whether friends may color different parts of the page at the same time. KEEPDYNAMIC is keeping your prepared recipe on the counter after dinner instead of throwing it away. REOPT is tasting the soup again because the ingredients this time are different from the recipe you wrote this morning.
1. Which isolation level do most Db2 for z/OS applications use with CURRENTDATA(NO)?
2. What does RELEASE(DEALLOCATE) do?
3. Which REOPT value cannot be combined with KEEPDYNAMIC(YES)?
4. What does DEGREE(ANY) request?
5. When is CURRENTDATA(YES) useful?