DB2 command overview: DSN subcommands and operator commands

Two command processors, two jobs

IBM’s Command Reference splits the book the same way:

• The DSN command and its subcommands — DSN executes as a TSO command processor. Subcommands prepare and run applications.
• Db2 commands (operator / system commands) — control the operational environment of a started subsystem: databases, threads, logs, DDF, traces, utilities, accelerators, profiles.

A third group appears in the same manual but is not “Db2 commands” in this sense: IMS /SSR commands, CICS attachment commands, and z/OS IRLM procedure commands. Application programmers live in DSN and DB2I. Operators live on the console and SDSF. DBAs use both every day.

Command prefix and recognition character

Operator-command examples are written as -DISPLAY DATABASE. The hyphen is not SQL syntax. It is the default command prefix (subsystem recognition character) assigned when the subsystem is defined to z/OS. The prefix tells MVS “this text is for Db2 ssid, not for JES or SDSF.” Your site might use a different prefix—period, dollar sign, or a two-character prefix in a data sharing group. Always copy the prefix from a working SDSF command or from DSNZPARM / IEFSSNxx documentation, not from a textbook hyphen.

Inside a DSN session you can also issue operator commands by typing the prefix and the command. DSN then forwards them to Db2. Subcommands such as BIND never take that prefix.

DSN subcommands

Start a session from TSO READY or a batch TMP:

1
2
3
4
5
DSN SYSTEM(DB2P)
BIND PACKAGE(PAYROLL.PAYROLL1) MEMBER(PAYRPT) -
     LIBRARY('PAYROLL.DBRMLIB') ACTION(REPLACE)
RUN PROGRAM(PAYRPT) PLAN(PAYRPT) LIB('PAYROLL.LOAD')
END

SYSTEM names the Db2 subsystem (or group attachment name). In JCL, the same text sits in SYSTSIN for PGM=IKJEFT01. Continuation uses a hyphen at the end of a DSN line (that hyphen is a TSO continuation character, which confuses beginners because operator commands also start with a hyphen).

What each common subcommand actually does

• BIND PACKAGE — reads a DBRM (or copies from another package), produces a package in the directory (SPT01), and records it in SYSPACKAGE. ACTION(ADD) versus REPLACE, isolation, release, VALIDATE, EXPLAIN, and OWNER all live here—not on -START.
• BIND PLAN — builds the application plan and its package list. Runtime SQL still needs a plan name even when all SQL is in packages.
• BIND QUERY — reads DSN_USERQUERY_TABLE and related EXPLAIN tables and inserts stabilization rows into SYSQUERY, SYSQUERYPLAN, and SYSQUERYOPTS.
• BIND SERVICE — builds the package that represents a Db2 REST service.
• REBIND — rebuilds access paths when statistics or schema changed but SQL did not. REBIND TRIGGER PACKAGE is for basic triggers (SYSTRIGGERS SQLPL blank); advanced triggers use REBIND PACKAGE.
• FREE — deletes catalog/directory entries. FREE STABILIZED DYNAMIC QUERY also purges matching dynamic-cache statements.
• RUN — loads the program and uses the named PLAN. SQL errors appear in SYSTSPRT, not in SDSF DA.
• DCLGEN — reads the catalog and writes a DECLARE TABLE plus COBOL, PL/I, or C host structures so copybooks match SYSCOLUMNS.
• SPUFI — ISPF foreground only; not for unattended batch. Other subcommands run foreground or background except as IBM notes for BIND QUERY and END.
• ABEND — diagnostic only (X'04E' / X'00C50101'). Use only under IBM Support.

All DSN subcommands except SPUFI run under DSN in foreground or background. All except BIND QUERY and END also run under Db2 Interactive (DB2I). That is why shops teach beginners BIND on the DB2I panels first, then show the equivalent SYSTSIN for production change-man jobs.

Db2 operator commands

Operator commands control a running subsystem. IBM’s environment rule:

• -START DB2 — only from a z/OS console or TSO SDSF (the subsystem is not up, so DSN/DB2I/CICS paths are not available yet).
• All other Db2 commands — z/OS consoles; TSO via DSN or the DB2I Commands panel; IMS terminals; authorized CICS terminals; batch programs including APF-authorized TMPs; IFI applications.

Extended MCS consoles are supported, so messages return to the console that issued the command even when the system has more than 99 consoles.

Authorization in one minute

DISPLAY-type commands commonly require DISPLAY privilege or SYSOPR, SYSCTRL, SYSADM, or system DBADM. START DATABASE needs the privileges that apply to that database (DBADM / STARTDB / SYSCTRL / SYSADM depending on the object). Commands issued from a logged-on z/OS console or SDSF can be checked by Db2 using primary and secondary authorization IDs—the console user must exist in RACF. A “fired from the master console with no user” model may bypass Db2 auth depending on install options; modern shops do not rely on that.

Data sharing scope

Many commands accept SCOPE(LOCAL) versus SCOPE(GROUP), or they default to member versus group. DISPLAY GROUP is inherently group-aware. DISPLAY ARCHIVE shows only the member you issued it on, even if ARCHIVE LOG SCOPE(GROUP) archived everyone. Always read the “Data sharing scope” line in the Command Reference before you assume one member’s output is the whole group. Group-scope commands can produce IFCID 090 trace records on other members showing SYSOPR as the issuer.

1
2
3
4
-DISPLAY THREAD(*)
-DISPLAY DATABASE(DSNDB06) SPACENAM(*) LIMIT(*)
-DISPLAY UTILITY(*)
-START DATABASE(PAYROLL) SPACENAM(TSACCT) ACCESS(RW)

DB2I, SDSF, IFI, and batch

DB2I (option COMMANDS) is the safest classroom path: it supplies the prefix and authorization context of your TSO ID. SDSF / system command line is what operators use for -START DB2 and -DISPLAY during incidents. IFI (Instrumentation Facility Interface) lets a program issue commands and read the result messages—monitors and automation use it. Batch TMP jobs can issue operator commands in SYSTSIN after DSN SYSTEM(ssid), which is useful for controlled automation but dangerous if the JCL is over-privileged.

Message DSN9022I with NORMAL COMPLETION means the command processor finished; it does not always mean the object is healthy. Read the preceding DSN* display messages. A command can complete normally while DISPLAY still shows RECP or STOP.

Common mistakes when mixing the two families

Beginners lose hours on errors that are really category mistakes. Putting BIND PACKAGE on the SDSF command line fails because BIND is not an operator command. Putting -DISPLAY DATABASE into a BIND job’s SYSTSIN without a DSN SYSTEM(ssid) session fails because the TMP does not know the prefix belongs to Db2. Using the textbook hyphen when your group uses a two-character prefix sends the text to the wrong subsystem—or to MVS, which replies INVALID COMMAND. Abbreviating operator keywords too aggressively can also parse as a different command; IBM allows abbreviations (DIS, STA, STO) but tells you to avoid inventing your own.

Another frequent mix-up: SPUFI and DSN RUN both “run SQL,” but SPUFI is an interactive ISPF tool that prepares dynamic SQL, while RUN executes a bound program with a plan. Neither starts DDF. Neither opens a STOPPED table space. If SQLCODE -904 says the space is stopped, you need -DISPLAY DATABASE and -START DATABASE, not another BIND.

Batch IKJEFT01 jobs that concatenate BIND and a hyphen command in one SYSTSIN work only after DSN SYSTEM(ssid) has started the session. END closes that session; commands after END are TSO again. A stray END in the middle of a bind job is a classic “why did the rest of my SYSTSIN run as TSO CLISTs?” incident.

How this tutorial series is organized

The next pages walk the operator-command families in the order shops actually use them: DISPLAY (what is going on), START (make it available), then STOP, SET, CANCEL, RECOVER, and TERM. DSN subcommands (BIND, REBIND, FREE, RUN) have their own later topics. Keep the split in your notes: if the task is “prepare a COBOL program,” you want DSN. If the task is “why is PAYROLL stopped,” you want -DISPLAY / -START.

Explain It Like I'm Five

Imagine Db2 is a school. DSN subcommands are the office printer: BIND makes a nametag (package) for a student program, RUN sends the student to class. Operator commands are the building lights and door keys: START opens the school, DISPLAY looks down the hallway to see who is there, STOP locks a classroom. You do not print nametags with the light switch, and you do not unlock the gym by compiling COBOL.

Exercises

1. Find your subsystem command prefix from a successful SDSF Db2 command or from operations documentation. Write it down; do not assume hyphen.
2. Classify each of these as DSN subcommand or operator command: BIND PLAN, -DISPLAY LOG, DCLGEN, -TERM UTILITY, RUN, -START DDF.
3. Write a minimal SYSTSIN that starts DSN, issues BIND PACKAGE (dummy names), and END.
4. Explain why -START DB2 cannot be issued from the DB2I Commands panel before Db2 is up.
5. Ask whether your data sharing group uses SCOPE(GROUP) on DISPLAY THREAD and what extra output that produces.