Run DB2 SQL through SPUFI on z/OS

What SPUFI is for

SPUFI means SQL Processor Using File Input. You place one or more SQL statements in an input dataset. SPUFI submits them dynamically to Db2 and writes formatted output—result rows, messages, and SQLCODEs—to an output dataset. It is ideal for learning SQL, validating sample data, checking catalog rows after a bind, and performing controlled DBA checks.

SPUFI is not a replacement for application packages, batch schedules, or production change management. Use it as an interactive proof tool, then move durable SQL into the controlled path your shop requires.

Prerequisites

A simple personal input PDS such as userid.SPUFI.SQL with members for each exercise keeps work organized. Prefer a dedicated output dataset name you are willing to overwrite on every run.

SPUFI panel options

For a first successful query, a common choice is EDIT INPUT YES, EXECUTE YES, AUTOCOMMIT YES, and BROWSE OUTPUT YES. Switch AUTOCOMMIT to NO when you want an explicit decision before making data changes permanent.

Steps: run SQL through SPUFI

1. Allocate the input dataset

Create a sequential dataset or PDS member before opening SPUFI. The member can be empty; you will edit it in the next step.

2. Open SPUFI from DB2I

From the DB2I Primary Option Menu, select SPUFI. On the SPUFI panel, fill in the input dataset name and an output dataset name such as RESULT or userid.SPUFI.OUT.

1
2
3
4
5
6
7
8
SPUFI panel (conceptual)
  1  INPUT DATA SET  ===> 'STEVE.SPUFI.SQL(EMPSEL)'
  4  OUTPUT DATA SET ===> 'STEVE.SPUFI.OUT'
  5  CHANGE DEFAULTS ===> N
  6  EDIT INPUT      ===> Y
  7  EXECUTE         ===> Y
  8  AUTOCOMMIT      ===> Y
  9  BROWSE OUTPUT   ===> Y

3. Edit the SQL

When EDIT INPUT is YES, ISPF edit opens. Enter one or more SQL statements. End statements according to your SPUFI defaults (often a semicolon). Keep teaching examples small and intentional.

1
2
3
4
SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
  FROM TRAINING.EMPLOYEE
 WHERE DEPT_CODE = 'A10'
 ORDER BY EMP_ID;

4. Execute and browse

Press Enter to process. With EXECUTE YES, SPUFI sends the input to Db2. With BROWSE OUTPUT YES, you land in an ISPF browse of the output dataset. Read every SQLCODE, not only the result table. The summary at the end shows whether the run committed or rolled back.

5. Re-run without retyping

Leave the SQL in the input member. Next time, set EDIT INPUT to NO if no changes are needed, keep EXECUTE YES, and press Enter. This makes SPUFI a fast regression tool for the same verification query after an application run or bind.

Verify results

• Confirm each statement's SQLCODE in the output dataset.
• For SELECT, confirm row counts and key column values against the business expectation.
• For INSERT/UPDATE/DELETE, confirm the reported row count and re-query the affected keys.
• Check the final commit/rollback indication when AUTOCOMMIT is NO or when a later statement failed.
• If CONNECT LOCATION is used, confirm you executed against the intended remote or local location.

1
2
3
4
-- Follow-up verification after a sample insert through SPUFI
SELECT EMP_ID, EMP_NAME, DEPT_CODE
  FROM TRAINING.EMPLOYEE
 WHERE EMP_NAME = 'LINDA CHEN';

Common errors

Another frequent beginner mistake is running change SQL against a shared training table without a cleanup plan. Prefer personal schemas or clearly tagged sample keys, and leave AUTOCOMMIT NO until you have inspected the preview queries.

Explain It Like I'm Five

SPUFI is like a homework notebook for Db2. You write your questions on a page (the input dataset), hand the notebook to Db2, and Db2 writes the answers on another page (the output dataset). AUTOCOMMIT is whether you permanently ink the changes or keep an eraser handy. BROWSE OUTPUT is simply opening the answer page to read it.

Exercises

1. Allocate a personal SQL PDS member and run a SELECT against a table you are allowed to read.
2. Run the same member twice: once with EDIT INPUT YES and once with EDIT INPUT NO.
3. Execute an INSERT with AUTOCOMMIT NO, browse the output, then choose rollback and prove the row is gone.
4. Intentionally misspell a table name and record the SQLCODE and message from the output.
5. Adjust one SPUFI default formatting option and describe how the output presentation changed.