DB2I is the Db2 Interactive ISPF interface, and SPUFI—SQL Processor Using File Input—is its classic way to edit, run, and review dynamic SQL in a TSO session. These tools remain useful for diagnosis and learning, but production work still needs correct subsystem selection, allocation, authority, review, and output retention.
DB2I is the panel-driven Db2 interface inside TSO/ISPF. The DB2I Primary Option Menu provides paths to SPUFI, Db2 commands, utilities, precompile, bind, run, and catalog-oriented panels. It reduces the amount of JCL and DSN syntax you must remember, while still driving Db2 functions that require real authorization and operational care.
Before using any panel, confirm the subsystem identifier (SSID), group attachment, and environment. A familiar panel can point at a different test, QA, or production member depending on the invocation procedure. Treat the displayed SSID as a pre-flight check, especially before utilities or DDL. Keep separate ISPF profiles and clear naming conventions for each environment.
SPUFI executes one or more SQL statements dynamically from an input data set. You select SPUFI from DB2I, identify a sequential or partitioned input data set and a sequential output data set, choose processing options, and submit the work. Db2 executes the input and writes formatted results, messages, and SQL diagnostics to the output data set.
SPUFI is excellent for controlled investigation, catalog queries, small DDL experiments in non-production, and reproducing an SQLCODE. It is not a source-control system or a deployment engine. Save reusable SQL in versioned members, review it, and use batch tooling or approved change automation for production migration.
The input data set contains SQL statements and can be sequential or partitioned. Allocate it before use, or select an existing member. Use the ISPF editor when EDIT INPUT is Y. The output data set must be sequential; SPUFI writes the report there, and BROWSE OUTPUT lets you inspect it afterward. Protect both data sets appropriately because SQL text and results can contain sensitive object names or data.
Input character encoding matters. Traditional SPUFI processing expects z/OS/EBCDIC-friendly content. When SQL originated on a workstation, verify conversion and line length before executing. Fixed-width data sets can truncate overlong statements; do not discover this from a confusing SQL syntax error after a production window begins.
1234567-- Member: USERID.SQL(DB2I01) SELECT DBNAME, NAME, STATUS FROM SYSIBM.SYSTABLESPACE WHERE DBNAME = 'PAYROLL' ORDER BY NAME; -- Keep each reusable script headed with owner, purpose, target, and change reference.
On the SPUFI panel, the important choices are input data set, output data set, CHANGE DEFAULTS, EDIT INPUT, EXECUTE, AUTOCOMMIT, BROWSE OUTPUT, and optionally CONNECT LOCATION. Set EDIT INPUT to N when running an already reviewed member without opening it. Set EXECUTE to Y to submit it. BROWSE OUTPUT opens the result report after processing.
AUTOCOMMIT is a safety decision, not a convenience checkbox. For SELECT it has little consequence; for INSERT, UPDATE, DELETE, and DDL it can make a change durable immediately. Know your shop rules. Explicit COMMIT and ROLLBACK statements are usually clearer in a controlled script. The CONNECT LOCATION field issues a type 2 CONNECT to a remote server when applicable; confirm remote authority and where output is generated.
The DB2I Db2 Command panel submits Db2 commands without making you construct a console or batch path manually. Use it for authorized operational commands, and capture output in the ticket or runbook. The Utilities panels help generate and submit utility control statements; review generated JCL for object scope, templates, copy options, and restart behavior before execution.
Precompile, Bind, and Run panels support application-development flow. Precompile processes embedded SQL, bind creates packages/plans with chosen options, and run executes an application or test path. These panels make the steps approachable but do not remove dependencies on DBRMs, collections, authorization, APPLCOMPAT, or package ownership. Treat panel settings as build configuration and record them with source.
SPUFI formatting controls determine headings, spacing, line width, and result presentation. A well-formatted report makes diagnosis faster; an overly narrow report can wrap or hide the column relationship you need. Use named columns, ORDER BY for deterministic evidence, and predicates that avoid dumping millions of rows into an output data set.
When SPUFI reports an SQL error, read the SQLCODE, SQLSTATE if shown, message text, and statement position. Check whether the input was truncated, whether the correct subsystem and SQLID were selected, and whether a prior statement committed or rolled back. Do not repeatedly rerun a failing DML script without understanding its transaction state. Save the output member as incident evidence before overwriting it.
DSNTEP2 is an IBM-supplied sample dynamic SQL program commonly used to execute SQL in batch JCL or TSO contexts. SPUFI is interactive and panel-driven; DSNTEP2 is program-driven and better suited to repeatable batch jobs. Both execute dynamic SQL, but their input/output conventions, formatting, and operational controls differ.
Use SPUFI to explore and prove a statement. Move repeatable deployment, reporting, or data-fix work into reviewed batch JCL (often DSNTEP2 or an approved framework) so the exact input, job log, return codes, and target subsystem are retained. This progression—from interactive experiment to controlled automation—is a core mainframe discipline.
DB2I is a hallway of labeled doors in the Db2 building. SPUFI is a desk where you write a question on paper, hand it to Db2, and get a printed answer. The input paper is your SQL member; the output paper is the report. If you write “change everything” and turn on autocommit, the change becomes real, so ask a grown-up and read the paper first.
1. What does SPUFI stand for?
2. What kind of data set must SPUFI use for output?
3. When is DSNTEP2 commonly preferred?