Other DB2 commands: END, RUN, SPUFI, ALTER, ARCHIVE, RESET

DSN leftovers: END, ABEND, DCLGEN, RUN, SPUFI

END

END has no options and needs no authorization. In foreground you type END at the DSN prompt and get TSO READY. In batch, if END is missing, /* or // ends the session. Pressing ATTENTION twice also ends DSN. Scope is member.

ABEND

ABEND is not “panic leave.” It forces the DSN session to terminate with completion code X'04E' and reason X'00C50101' so IBM Support can capture a dump while diagnosing DSN or Db2. Do not put ABEND in production CLISTs. Use END.

DCLGEN

DCLGEN (declarations generator) reads a table or view from the catalog and writes an SQL DECLARE TABLE statement plus a COBOL, PL/I, or C structure into a library member. You need ownership, SELECT, DBADM on the database, SYSADM, or SYSCTRL (catalog tables only). Scope is group. Typical options: TABLE, OWNER, AT(location) for a remote catalog, LIBRARY, ACTION(ADD|REPLACE), LANGUAGE(IBMCOB|PLI|C|CPP), APOST or QUOTE, LABEL, NAMES(prefix), STRUCTURE. Underscores in column names become hyphens in COBOL. DCLGEN can optionally build an indicator array; it does not invent business-level copybooks for you — regenerate after ALTER TABLE.

1
2
3
DCLGEN TABLE(HR.EMPLOYEE) -
  LIBRARY('SYS1.SRCLIB.COBOL(EMPDCL)') -
  LANGUAGE(IBMCOB) ACTION(REPLACE) APOST

RUN

RUN executes an application program that can contain SQL. You name PROGRAM and usually PLAN. From a requester you RUN a plan; you cannot RUN a package by itself. The program must already be bound. Background and foreground both work. Pass PARMS if the program expects them. Return codes from the program surface in register 15 at DSN END.

1
RUN PROGRAM(PAY001) PLAN(PAYPLAN) PARMS('/2026-08-13')

SPUFI

SPUFI means SQL Processor Using File Input: you edit a file of SQL, execute it, and browse the output file. It runs only in the ISPF foreground (almost always from DB2I). It is not a batch reporting engine; use DSNTEP2, DSNTIAUL, or a program for SYSTSIN SQL in batch. Isolation, autocommit, and max lines are DB2I panel settings, not DSN keywords you type next to SPUFI.

ALTER operator commands

SQL ALTER TABLE is not this family. These are hyphen commands that change runtime resources.

-ALTER BUFFERPOOL

Abbreviation -ALT BPOOL. Member scope. SYSOPR, SYSCTRL, or SYSADM. You name BP0–BP49 (4 KB), BP8K0–BP8K9, BP16K0–BP16K9, or BP32K–BP32K9.

1
2
3
-ALTER BUFFERPOOL(BP2) VPSIZE(50000) VPSEQT(80)
-ALTER BUFFERPOOL(BP8K0) PGFIX(YES)
-ALTER BUFFERPOOL(BP1) VPSIZE(0)

VPSIZE(0) is destructive: Db2 quiesces users of that pool and deletes it; later opens fail until you give the pool a real size again. Simulated buffer pools (SPSIZE) are a capacity-planning feature; they still count toward the 16 TB combined limit.

-ALTER GROUPBUFFERPOOL

Data sharing only. Changes coupling-facility GBP attributes (size, ratios, AUTOSIZE) that DISPLAY GROUPBUFFERPOOL reports. Issue it when the group must agree on a new GBP configuration; a local ALTER BUFFERPOOL does not resize the GBP. Coordinate with the coupling facility policy and avoid shrinking a GBP while members are in GRECP recovery.

-ALTER UTILITY

Abbreviation -ALT UTIL. Member scope. It does not rewrite LOAD SYSIN. It changes DEADLINE, MAXRO, LONGLOG, and DELAY on an in-flight REORG SHRLEVEL REFERENCE or CHANGE, or REBUILD SHRLEVEL CHANGE, identified by utility-id. LISTDEF: only the items currently in process are affected. LONGLOG values CONTINUE, TERM, and DRAIN tell Db2 what to do if log iterations are not shrinking. MAXRO(DEFER) postpones the last read-only log pass. You must be the submitter or hold a listed authority (SYSOPR, DBADM, and similar).

1
-ALTER UTILITY (REORGEMP) REORG MAXRO(240) LONGLOG(DRAIN)

ARCHIVE and -ARCHIVE LOG

ARCHIVE in the command book is the operator command -ARCHIVE LOG (abbreviation -ARC LOG), not SQL ARCHIVE tables. Privilege: ARCHIVE, installation SYSOPR, SYSCTRL, or SYSADM. Scope is group or member.

1
2
3
-ARCHIVE LOG
-ARCHIVE LOG MODE(QUIESCE) WAIT(YES) TIME(420)
-ARCHIVE LOG SCOPE(MEMBER)

Use MODE(QUIESCE) when you need a captured consistency point in the log before offload (for example before a coordinated copy). If the quiesce cannot finish in TIME, the command fails and updates resume. SCOPE cannot be combined with MODE(QUIESCE).

RESET commands

-RESET GENERICLU

Purges VTAM generic LU partner information Db2 stored for DRDA or related SNA partner names. Use it after a partner network change left stale generic LU data, not as a weekly chore. DISPLAY LOCATION is the usual companion.

-RESET INDOUBT

Purges information about indoubt units of recovery after you no longer need Db2 to remember them. Resolving the UR (commit or abort) is -RECOVER INDOUBT. RESET is the cleanup of leftover interest once the coordinator story is finished or abandoned per your procedures. DISPLAY THREAD TYPE(INDOUBT) first. Wrong RESET can make a later heuristic decision harder to audit — treat it as a DBA/sysprog command.

RUN libraries, plans, and return codes

RUN needs the load module on STEPLIB or JOBLIB (or LNKLST) and a plan that was bound for that program’s DBRM or package list. PROGRAM names the member; PLAN names the application plan. You cannot RUN a package name. If the program uses a different plan than the one you named, SQLCODE -805 or -818 style problems appear at first SQL. PARMS are passed the way TSO CALL would pass them; quote strings that contain blanks. Language Environment options belong in the usual CEEOPTS DD, not in the RUN subcommand.

DCLGEN LIBRARY must be a partitioned data set your ID can update. ACTION(ADD) fails if the member exists; ACTION(REPLACE) overwrites. LANGUAGE(IBMCOB) is Enterprise COBOL; PLI, C, and CPP cover the other host languages DCLGEN supports. APOST versus QUOTE must match the compiler option in the program that INCLUDEs the member. AT(location) points DCLGEN at a remote catalog through three-part names when you are generating structures for a table that lives on another Db2. Labels from LABEL ON COLUMN can be copied into the COBOL comments when you specify LABEL. After ALTER TABLE ADD COLUMN, regenerate or the host structure and the table drift apart — BIND will not save you from truncated fields.

SPUFI isolation, max lines, and autocommit live on the DB2I SPUFI defaults panel. A COMMIT in the input file still commits; a failure without autocommit leaves a unit of work you must COMMIT or ROLLBACK from the same session. Do not treat SPUFI as a change control tool: production DDL belongs in reviewed jobs, not a foreground edit session.

Buffer pool and GBP practice

Altering VPSIZE grows or shrinks the local pool on this member only. Virtual storage for the pool comes from the Db2 database services address space (DBM1). Oversizing BP2 on a memory-constrained LPAR can cause paging that is worse than a smaller pool with more DASD I/O. DISPLAY BUFFERPOOL DETAIL before and after an ALTER so you can see VPSIZE, current buffers, page-in rates, and sequential steal. PGFIX(YES) is attractive for pools that should stay in real storage, often with large frames, but you typically need to deallocate and reallocate the pool (stop objects that use it, or recycle Db2) before the fix takes effect. VPSIZE(0) is an emergency drain: every object assigned to that pool will fail to open until you restore a positive size.

AUTOSIZE lets WLM help grow and shrink the pool between VPSIZEMIN and VPSIZEMAX. It is not a substitute for assigning the right objects to the right pools. Random index leaf pages and sequential table scans want different VPSEQT values; dumping everything into BP0 is how shops paint themselves into a corner. Simulated pools (SPSIZE) exist for capacity studies; they still count toward the combined buffer pool limit IBM documents (on the order of 16 TB of virtual buffers across pools).

-ALTER GROUPBUFFERPOOL is the data-sharing twin. GBP size lives in the coupling facility policy. Changing it while a member is in GRECP or LPL recovery can make a bad day worse. DISPLAY GROUPBUFFERPOOL first, coordinate with the sysprog who owns the CFRM policy, then alter. A local -ALTER BUFFERPOOL never resizes the GBP; the two layers are easy to confuse when hit ratios look wrong.

Utility control

Online utilities are not started with a hyphen command. You EXEC PGM=DSNUTILB and pass control statements in SYSIN (LOAD, COPY, REORG, LISTDEF, TEMPLATE, OPTIONS). While they run, operator commands steer them:

• -DISPLAY UTILITY — phase, UID, percent
• -TERM UTILITY — stop and do not restart that execution
• -ALTER UTILITY — retune REORG/REBUILD SHRLEVEL as above

Utility control statements themselves (OPTIONS PREVIEW, LISTDEF, TEMPLATE) travel in SYSIN with the utility, not on the console. The next section of this tutorial is the utilities overview that starts with DSNUTILB.

ARCHIVE LOG without QUIESCE is the everyday “truncate and offload now” button after you filled an active log or before a maintenance window. MODE(QUIESCE) is heavier: Db2 stops new update units of work across the scope, waits until existing updaters commit or roll back, then cuts the log. In data sharing that consistency point is group-wide when you use QUIESCE, which is why SCOPE is mutually exclusive with MODE(QUIESCE). If a long-running batch job will not commit within TIME(nnn), the quiesce fails and updates resume — plan the window or CANCEL THREAD the blocker. CANCEL OFFLOAD is for a stuck archive task, not a substitute for fixing SMS or tape drive problems that caused the stall.

RESET GENERICLU is rare SNA/DRDA cleanup after partner names change. RESET INDOUBT is more dangerous: DISPLAY THREAD TYPE(INDOUBT) and talk to the coordinator (CICS, IMS, XA) before you purge Db2’s memory of the UR. -RECOVER INDOUBT ACTION(COMMIT|ABORT) is how you finish a UR when the coordinator is gone. RESET after that (or when procedures say the interest row is leftover junk) keeps DISPLAY THREAD from crying wolf. Mixing the two commands is a common late-night mistake.

Explain It Like I'm Five

END is hanging up the hallway phone (DSN). RUN is asking a student (your program) to read their homework aloud using a hall pass (the plan). SPUFI is a whiteboard you scribble SQL on in class, not a dump truck. ALTER BUFFERPOOL is changing how many cubbies the classroom has. ARCHIVE LOG is closing today’s notebook and putting it on the archive shelf. RESET is erasing a sticky note about a fight that was already settled. Utility control is the teacher walking over to a dump truck (DSNUTILB) to ask how far it has gotten.

Exercises

1. Generate a COBOL DCLGEN for a sandbox table with ACTION(REPLACE) and APOST. Include the member in a program and BIND.
2. DISPLAY BUFFERPOOL(BP2), then explain what VPSIZE and VPSEQT would change before you ALTER anything.
3. Compare a plain -ARCHIVE LOG with MODE(QUIESCE) WAIT(YES). When would QUIESCE fail in data sharing?
4. Write the difference between -RECOVER INDOUBT and -RESET INDOUBT in one paragraph.
5. From DISPLAY UTILITY, decide whether you need TERM, ALTER UTILITY MAXRO, or neither.