ACCESS commands in DB2 for z/OS

ACCESS versus START DATABASE ACCESS

Two different commands share the English word “access”:

• -START DATABASE ... ACCESS(RW | RO | UT | FORCE) — make the object available, set read/write/utility intent, or FORCE-reset certain restricted states (LPL, GRECP, CHKP, COPY, RECOVER pending—not RESTP)
• -ACCESS DATABASE ... MODE(OPEN | NGBPDEP | STATS) — open, un-GBP, or externalize statistics. It does not reset RECP

You can issue -ACCESS DATABASE from a z/OS console, SDSF, DSN, DB2I COMMANDS, IMS/CICS, or IFI. Data sharing scope is group or member depending on MODE.

Authorization

The privilege set needs one of:

• STARTDB privilege on the database
• DBMAINT, DBCTRL, or DBADM
• SYSCTRL, SYSADM, or System DBADM

If you list several databases and lack STARTDB on one of them, that database fails and the others that you are authorized for are still processed. When data definition control is active, installation SYSOPR or installation SYSADM is required for a database, table space, or index space that holds a registration table or index. Console commands use primary and secondary authorization IDs.

Syntax

You name databases and spaces the same way as START/STOP/DISPLAY DATABASE: lists, ranges, and asterisk patterns.

1
2
3
4
5
-ACCESS DATABASE(dbname) SPACENAM(spacename) PART(n) MODE(OPEN|NGBPDEP|STATS)
 
-ACCESS DATABASE(DSN9001) SPACENAM(DSN9002) PART(1,3) MODE(OPEN)
-ACCESS DATABASE(DSN9001) SPACENAM(DSN9003) MODE(NGBPDEP)
-ACCESS DB(*) SP(*) MODE(STATS)

• DATABASE / DB — one name, a list, a range (name1:name2), prefix* (1–7 characters), *suffix, *string*, or *s1*s2*
• SPACENAM / SPACE / SP — table space or index space names with the same pattern rules
• PART — partition numbers, ranges, or mixed lists. Valid for partitioned table spaces, partitioned indexes, and nonpartitioned type 2 indexes on partitioned table spaces. Invalid (error, object skipped) for non-partitioned table spaces, type 1 indexes, type 2 indexes not on a partitioned table space, and PBG table spaces in a work file database
• MODE — required action: OPEN, NGBPDEP, or STATS

When you access a logical partition of a nonpartitioning index, the index is not closed. To close a nonpartitioning index you access it without PART.

MODE(OPEN)

OPEN forces a physical open of the page set or partition on the local member only. The overhead moves from the first SQL thread onto the command thread, which can raise the first-transaction rate after IPL, after STOP DATABASE, or when objects were never opened on this member.

Db2 does not process objects whose data sets were never created (DEFINE NO without a later physical define). Opening on several members of a data sharing group at the same instant can return DSNI045I with reason 00C90090 (resource contention). Reissue the command; do not fire OPEN on every member in one automation blast.

MODE(NGBPDEP)

In data sharing, an object that is of interest to more than one member is group buffer pool dependent. Coupling-facility page writes and cross-invalidation cost CPU. A large batch job that will be the only updater on one member benefits if you first drain the object and drop GBP dependency.

• Issue NGBPDEP only on the member that will run the batch
• The page set or partition is drained
• Remote claimers (threads on other members, including RELEASE(DEALLOCATE) DBATs) can fail the command with DSNI048I (remote claimers exist)
• Successful completion messages look like DSNTDDIS 'ACCESS DATABASE' NORMAL COMPLETION

After the batch, normal SQL from other members makes the object GBP-dependent again. NGBPDEP is a window, not a permanent attribute of the table space.

MODE(STATS)

STATS externalizes in-memory real-time statistics and optimizer statistics recommendations to the catalog tables that hold them. In data sharing, in-memory statistics from all members are written. The mode does not physically open page sets and does not change RW/RO/UT.

IBM recommends only certain database-name and space-name combinations with STATS. The documented catch-all for “everything currently in memory” is:

1
-ACCESS DB(*) SP(*) MODE(STATS)

Use STATS before a report that reads RTS catalog tables, or before you want recommendations visible without waiting for the next automatic externalization. It is not a substitute for RUNSTATS after a bulk load.

Practical sequences

• After restart, before a critical CICS region comes up: ACCESS DATABASE MODE(OPEN) for the busiest table spaces on that member
• Before a nightly batch that should not pay GBP tax: stop or drain competing claimers, ACCESS MODE(NGBPDEP) on the batch member, run the job, let other members reopen as needed
• Before a statistics dashboard: ACCESS MODE(STATS), then SELECT from RTS tables

Explain It Like I'm Five

MODE(OPEN) is unlocking the classroom and turning the lights on before the first student arrives, so the first student does not stand in the dark fumbling with the key. MODE(NGBPDEP) is telling the other classrooms to stop sharing the one chalkboard (the coupling facility) so your class can write on it alone for a long exam. MODE(STATS) is emptying the teacher’s pocket notebook into the official grade book so everyone can read the scores, without opening extra classrooms.

Exercises

1. Write ACCESS DATABASE to physically open partitions 1 and 3 of table space PAYTS in database PAYDB.
2. A DBA issues START DATABASE ACCESS(FORCE) when they meant ACCESS DATABASE MODE(OPEN). What extra damage might FORCE do?
3. Why would MODE(NGBPDEP) on member DB2A fail while a CICS region on DB2B still holds claims, and which message should you look for?
4. Write the STATS command that flushes all in-memory RTS. Does it open page sets?
5. You get DSNI045I during MODE(OPEN) on three members at once. What does IBM tell you to do?