DB2 LOCKSIZE, LOCKMAX, BUFFERPOOL and CLOSE

LOCKSIZE

The LOCKSIZE clause of CREATE TABLESPACE and ALTER TABLESPACE sets the size of locks that application processes take on a table or table space. Finer locks let more programs run at once. Coarser locks mean fewer lock requests and less IRLM work, but one updater can block many readers.

Default is LOCKSIZE ANY: Db2 may use any size. In most cases that is page locking with LOCKMAX SYSTEM. Function level 507 documents that when the number of locks exceeds SYSIBMADM.MAX_LOCKS_PER_TABLESPACE, page locks can be released and partition-level locks used.

For partitioned table spaces, locks are taken on individual partitions rather than one lock on the entire space when the lock size is a parent lock. That is why a PBR design can isolate hot ranges.

Do not specify LOCKSIZE for a table space in a work file database; Db2 ignores it because those locks are not used that way.

What each value is for

LOCKSIZE TABLESPACE — the process does not take table, page, row, LOB, or XML locks inside the space. Throughput of lock management is best; concurrency is worst. Use it for a space that is almost single-threaded (batch-only load tables, some staging spaces). LOCKPART YES and LOCKSIZE TABLESPACE are mutually exclusive on older partitioned spaces.

LOCKSIZE TABLE — on a segmented space that is not partitioned, lock the table. If the space holds more than one table (legacy segmented), other tables can still run. Function level 504: if the space is not PBG/PBR (no MAXPARTITIONS or NUMPARTS), TABLE is a synonym for TABLESPACE. New UTS designs have one table per space, so TABLE and TABLESPACE are the same idea.

LOCKSIZE PAGE — page locks plus a parent intent lock (IS, IX, or SIX) so IRLM knows the space is in use. Bind can still promote to S or X on the parent without page locks if the plan needs it; you get a warning that lock size was promoted.

LOCKSIZE ROW — the same parent intent lock, but the child lock is a row. Two programs can update different rows on the same page. That is the usual choice for OLTP with hot pages. Cost: more lock and unlock calls. Currently committed readers on LOCKSIZE PAGE spaces may need MAXROWS 8 or less so they do not wait on insert/delete; row locking is the other way to keep currently committed access well-behaved.

LOCKSIZE LOB and LOCKSIZE XML apply only to those auxiliary page sets. A process that needs a LOB value takes a LOB lock and IS/IX on the LOB table space.

Changing ANY to ROW with ALTER can require the table space to be stopped for the ALTER to complete. Plan an outage window; do not assume online ALTER for every lock-size change.

1
2
3
4
5
6
7
8
9
10
CREATE TABLESPACE ORDTS
     IN SALESDB
     USING STOGROUP SALESSTG
     PRIQTY 1440 SECQTY 720
     SEGSIZE 32
     MAXPARTITIONS 32
     LOCKSIZE ROW
     LOCKMAX SYSTEM
     BUFFERPOOL BP2
     CLOSE NO;

LOCKMAX

LOCKMAX is the maximum number of page or row (or LOB) locks one application process may hold at once in that table space. If the program asks for more, Db2 escalates: it releases the child locks and promotes the intent lock on the table space or segmented table to S or X.

• integer — 0 to 2 147 483 647. That many child locks are allowed before escalation.
• 0 — do not count locks; escalation does not occur.
• SYSTEM — use the built-in global variable SYSIBMADM.MAX_LOCKS_PER_TABLESPACE (the modern form of the old NUMLKTS subsystem parameter).

If LOCKSIZE is TABLESPACE, LOCKMAX must be omitted or 0 — there are no child locks to count. Work file table spaces ignore LOCKMAX.

Escalation is a safety valve, not a tuning goal. When it fires, one thread suddenly holds the whole partition or space and everyone else waits. Prefer a LOCKSIZE and application commit frequency that stay under the limit. If a nightly job touches every row, either commit more often, use LOCKSIZE PAGE/TABLESPACE on purpose, or accept escalation on that job only.

Unlike hitting some other lock limits, exceeding LOCKMAX does not fail the SQL with a resource-unavailable code for that reason; Db2 escalates instead, and historically it may do so without a loud console message. Watch IFCID lock-escalation statistics and accounting traces.

1
2
3
ALTER TABLESPACE SALESDB.ORDTS
     LOCKSIZE PAGE
     LOCKMAX 10000;

BUFFERPOOL

BUFFERPOOL bpname names the virtual pool that caches this table space and therefore sets the page size. The pool must be activated. The privilege set needs SYSADM, SYSCTRL, or USE on that buffer pool.

If you omit BUFFERPOOL, Db2 uses the default buffer pool of the database (set on CREATE/ALTER DATABASE). Work file table spaces cannot use 8 KB or 16 KB pools.

Page size vs row length: a 2000-byte row in a 4 KB page wastes less than the same row in a 32 KB page if you only store one or two rows per page. Long rows, large inline LOBs, and MAXROWS designs sometimes need 8K, 16K, or 32K. Compression also interacts with page size — compressed rows must still fit.

Isolation: putting a hot table space in its own pool (for example BP2) lets you hit ratios and -DISPLAY BUFFERPOOL stats without mixing them with catalog (often BP0) or random indexes. Indexes have their own BUFFERPOOL clause on CREATE INDEX; they do not inherit the table space pool.

ALTER BUFFERPOOL on a universal table space is often a pending definition change: the catalog remembers the new pool, the object goes advisory REORG-pending (AREOR), and an online REORG materializes it. That is covered on the table-space design page.

1
2
3
4
5
6
7
CREATE TABLESPACE LARGETS
     IN APPDB
     BUFFERPOOL BP16K0
     SEGSIZE 64
     MAXPARTITIONS 8
     LOCKSIZE PAGE
     CLOSE NO;

CLOSE

z/OS and Db2 can have only so many data sets open at once (DSMAX and related limits). When that limit is reached, Db2 closes page sets that are not in use. CLOSE is the priority flag:

• CLOSE YES — default. Eligible to close before CLOSE NO data sets. Good for rarely used table spaces.
• CLOSE NO — eligible to close only after all eligible CLOSE YES data sets are already closed. Use for objects you do not want bouncing open and closed under load.

CLOSE YES does not mean “close as soon as nobody is using it.” It means “when we are forced to close something, pick YES first.” If the limit is hit and nothing with CLOSE YES remains, Db2 will close CLOSE NO page sets too.

For a table space in a work file database, Db2 uses CLOSE NO no matter what you write. Indexes have the same CLOSE YES/NO clause; declared global temporary table indexes always behave as CLOSE NO.

IBM’s own sample CREATE TABLESPACE often shows CLOSE YES with LOCKSIZE PAGE and a named BUFFERPOOL. Production OLTP spaces are frequently CLOSE NO so the first SQL after a quiet period does not pay OPEN overhead.

1
2
3
4
5
6
7
8
-- IBM-style sample: page locks, BP1, close-eligible
CREATE TABLESPACE DSN8S12D
     IN DSN8D12A
     USING STOGROUP DSN8G120
     PRIQTY 20 SECQTY 20
     LOCKSIZE PAGE
     BUFFERPOOL BP1
     CLOSE YES;

If there are no CLOSE YES data sets left to close, Db2 might still close CLOSE NO spaces when DSMAX is reached — so CLOSE NO is a preference, not a guarantee the file stays open forever. Raise DSMAX and keep unused objects CLOSE YES if you are hitting the limit.

Putting the four clauses together

A typical OLTP universal table space:

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLESPACE PAYTS
     IN PAYROLL
     USING STOGROUP PAYSTO
     PRIQTY 720 SECQTY 360
     SEGSIZE 32
     DSSIZE 4 G
     MAXPARTITIONS 16
     LOCKSIZE ROW
     LOCKMAX SYSTEM
     BUFFERPOOL BP3
     CLOSE NO
     PCTFREE 10
     FREEPAGE 0;

A scan-heavy, batch-updated code table might instead use LOCKSIZE PAGE, LOCKMAX 0 (never escalate — the job already intends to touch many pages), BUFFERPOOL BP1, and CLOSE YES. Neither pattern is a law; both are explicit so the next DBA can see the intent.

Catalog: SYSIBM.SYSTABLESPACE holds LOCKRULE (lock size), LOCKMAX, BPOOL, and CLOSERULE. DISPLAY DATABASE shows the current state, not the DDL default.

Explain It Like I'm Five

LOCKSIZE is how big a “do not touch” sticker Db2 puts on the data: the whole warehouse (table space), one shelf (page), or one box (row). LOCKMAX is “if you grab too many boxes, we will just lock the whole warehouse so we can stop counting stickers.” BUFFERPOOL is the table by the door where copies of pages sit so you do not walk to the warehouse every time. CLOSE is whether we are allowed to lock the warehouse door when too many buildings are open at once: YES means this building is first in line to be locked; NO means please leave it open if you can.

Exercises

1. Write CREATE TABLESPACE for an OLTP table: LOCKSIZE ROW, LOCKMAX SYSTEM, BUFFERPOOL BP2, CLOSE NO. Explain each choice in one sentence.
2. Why must LOCKMAX be 0 (or omitted) when LOCKSIZE is TABLESPACE?
3. A 2500-byte row is going into a 4 KB page. What happens to wasted space, and when would you pick BP8K0 instead?
4. ALTER LOCKSIZE from ANY to ROW. What operational step might be required, and why is that different from ALTER PCTFREE?
5. Your subsystem hits DSMAX during online day. Which objects should be CLOSE YES vs CLOSE NO, and why is CLOSE NO still not an infinite open guarantee?