cheat sheet
Db2 SPUFI
Run SQL through SPUFI, drive Db2 with DSN subsystem commands, BIND packages and plans, schedule DSNTEP2 in JCL, query the SYSIBM catalog, and generate DCLGEN.
Db2 SPUFI — Interactive SQL, DSN commands, BIND, and catalog queries on z/OS
What it is
SPUFI (SQL Processor Using File Input) is the IBM-shipped ISPF panel application that lets a TSO user run ad-hoc SQL against Db2 for z/OS without writing a host-language program. It is part of every Db2 for z/OS install, lives under =DB2.1 in the standard Db2 primary menu, and is the on-platform equivalent of mysql or psql clients. Reach for SPUFI when you need to run a one-off SELECT, prototype a DDL change, EXPLAIN a query plan, or test SQL that will later be embedded in COBOL/PL/I. The alternatives are DSNTEP2 (the batch SQL utility used in JCL) for scheduled work and IBM Data Studio / VS Code extensions for off-platform development; SPUFI remains the fastest path for on-mainframe iteration.
This article covers SPUFI itself plus the surrounding DBA-flavoured layer: the DSN subsystem command processor, BIND PACKAGE / BIND PLAN, running SQL from JCL, useful queries against the SYSIBM catalog, and DCLGEN.
Db2 13 for z/OS function-level roadmap. Db2 13 follows the continuous-delivery function-level (FL) model — features ship in PTFs and activate when the catalog's FL is raised. Recent levels include FL 504 (Oct 2023; added the AI_COMMONALITY SQL Data Insights built-in plus LISTAGG-on-fullselect ORDER BY), FL 505 (Apr 2024; token-based auth, temporal support for security catalog tables, a Db2ZAI trace destination, and the INTERPRET SQL function), FL 506 (Oct 2024; column-mask restriction removal and a multi-row INSERT enhancement), and FL 507 (Apr 2025). All examples below assume at least FL 500 — raise your subsystem to the latest applicable FL before depending on AI_*, INTERPRET, or token-based auth.
Tooling shift. IBM Data Studio and Data Server Manager (DSM) have been retired in favour of the Db2 Developer for z/OS VS Code extension and Zowe Explorer for desktop/IDE workflows; SPUFI itself remains the on-mainframe answer for ad-hoc SQL.
Entering SPUFI
SPUFI is reached through the Db2 primary panel (Option =DB2), then sub-option 1. Most shops map =D or =DB2 to it from the ISPF main menu via a site-specific dialog.
=DB2.1 (* Db2 → SPUFI *)
=D2.1 (* common site shortcut *)
DB2I (option 1 — SPUFI) (* via DB2I Defaults panel *)
EX 'SYS1.DSNHLOAD.CLIST(DSNH)' (* manual TSO CLIST entry on older systems *)
Output: (none — opens the SPUFI panel)
SPUFI panel — the 8 fields
The SPUFI primary panel takes 8 inputs. Get these right once and SPUFI is a 30-second editor-to-results loop forever after.
===> SPUFI
1 DATA SET NAME ...... ===> 'ALICE.SQL(QUERY1)'
2 VOLUME SERIAL ...... ===>
3 DATA SET PASSWORD .. ===>
4 CHANGE DEFAULTS .... ===> NO (Y for first run, then keep NO)
5 EDIT INPUT ......... ===> YES (open the SQL in Edit before run)
6 EXECUTE ............ ===> YES (actually run after editing)
7 AUTOCOMMIT ......... ===> YES (commit on RC=0, rollback otherwise)
8 BROWSE OUTPUT ...... ===> YES (open the result dataset in Browse)
FOR REMOTE SQL PROCESSING:
CONNECT LOCATION ... ===> (Db2 location for 3-part names)
Output: (none — exits 0 on success)
Field-by-field meaning:
| Field | Notes |
|---|---|
| 1 INPUT DS | PDS member or sequential dataset holding the SQL text. SPUFI opens it for Edit. |
| 2 VOLUME | Only for uncatalogued datasets (rare). |
| 3 PASSWORD | Pre-RACF password protection. Empty on every modern system. |
| 4 CHANGE DEFAULTS | Opens the SPUFI Defaults panel (commit, isolation, max rows, codepage). |
| 5 EDIT INPUT | If NO, skip directly to execute. Useful when re-running unchanged SQL. |
| 6 EXECUTE | If NO, just edit and return. |
| 7 AUTOCOMMIT | YES commits on RC=0. NO leaves the unit-of-work open — required for testing rollback. |
| 8 BROWSE OUTPUT | Opens the output dataset in ISPF Browse after execution. |
| CONNECT LOCATION | Routes the SQL to a different Db2 system via DDF (3-part names). |
SPUFI Defaults (option 4 = YES)
The Defaults panel governs commit behavior, isolation level, ASCII/EBCDIC encoding, and dataset DCB attributes for the output dataset. Set them once per environment.
===> SPUFI Defaults
ISOLATION LEVEL .... ===> CS (RR / RS / CS / UR — Cursor Stability is typical)
MAX SELECT LINES ... ===> 250 (max rows fetched per SELECT)
OUTPUT DATA SET ATTRIBUTES:
RECORD LENGTH .... ===> 4092 (LRECL of result DS)
BLOCK SIZE ....... ===> 4096
RECORD FORMAT .... ===> VB
DEVICE TYPE ...... ===> SYSDA
PRIMARY SPACE .... ===> 100 (track allocation)
SECONDARY SPACE .. ===> 50
NUMBER OF ROWS ..... ===> 100
ALLOW IDENTIFIER LOWERCASE ===> NO (NO = fold to uppercase per SQL standard)
SHOW BINARY VALUES ... ===> HEX
GRAPHIC ............. ===> NO
SQL TERMINATOR ...... ===> ;
Output: (none — exits 0 on success)
The most consequential field is SQL TERMINATOR. The default ; is correct for almost every shop; only change it when your SQL contains compound statements with embedded semicolons (typical for triggers and procedures) — pick a character that does not appear in the body (often @ or #).
Writing the SQL input file
The input dataset is just plain SQL text, one statement per ;. Comments use -- (line) or /* ... */ (block). SPUFI strips blank lines and runs each statement separately, capturing the resulting table or RC into the output dataset.
-- ALICE.SQL(QUERY1)
-- Count rows in the orders table
SELECT COUNT(*) AS ORDER_COUNT
FROM ALICE.ORDERS
;
-- Group by month, last 12 months
SELECT MONTH(ORDER_DATE) AS MONTH,
SUM(NET_AMOUNT) AS REVENUE
FROM ALICE.ORDERS
WHERE ORDER_DATE >= CURRENT_DATE - 12 MONTHS
GROUP BY MONTH(ORDER_DATE)
ORDER BY MONTH
;
-- Catalog peek: which tables live in my schema?
SELECT NAME, CREATOR, COLCOUNT
FROM SYSIBM.SYSTABLES
WHERE CREATOR = 'ALICE'
AND TYPE = 'T'
ORDER BY NAME
;
Output: (none — exits 0 on success — but SPUFI writes the results in the output dataset)
Output dataset layout
After executing, SPUFI opens the output dataset (input-DS-name + .SPUFI.OUT by default) in Browse. Each statement contributes a block: the SQL text, the result set or message, then the timing and SQLCODE.
---------+---------+---------+---------+---------+---------+---------+---------+
SELECT COUNT(*) AS ORDER_COUNT FROM ALICE.ORDERS;
---------+---------+---------+---------+---------+---------+---------+---------+
ORDER_COUNT
-----------
1247385
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+---------+---------+
SELECT MONTH(ORDER_DATE) AS MONTH, SUM(NET_AMOUNT) AS REVENUE
FROM ALICE.ORDERS
WHERE ORDER_DATE >= CURRENT_DATE - 12 MONTHS
GROUP BY MONTH(ORDER_DATE)
ORDER BY MONTH;
---------+---------+---------+---------+---------+---------+---------+---------+
MONTH REVENUE
----- --------------
1 428193.50
2 391824.07
...
DSNE610I NUMBER OF ROWS DISPLAYED IS 12
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
DSNE617I COMMIT PERFORMED, SQLCODE IS 0
DSNE618I ROLLBACK NOT PERFORMED, SQLCODE IS 0
DSNT400I SQLCODE = 100, NOT FOUND: ROW NOT FOUND...
DSNT415I SQLERRP = DSNXRDA, SQLERRD = ...
DSNE601I SQL STATEMENTS ASSUMED TO BE BETWEEN COLUMNS 1 AND 72
DSNE620I NUMBER OF SQL STATEMENTS PROCESSED IS 3
DSNE621I NUMBER OF INPUT RECORDS READ IS 28
DSNE622I NUMBER OF OUTPUT RECORDS WRITTEN IS 47
The two messages to scan for are DSNE616I (a single statement worked) and DSNE620I (total processed). SQLCODE = 100 here is fine — it means "no more rows" at end-of-cursor, not an error.
EXPLAIN — how Db2 will run a query
EXPLAIN PLAN writes the access path into PLAN_TABLE rows that you can then query like any other Db2 table. SPUFI is the canonical place to do this iteratively.
-- Make sure PLAN_TABLE exists in your schema first; standard DDL is
-- in SDSNSAMP(DSNTESC) at most shops.
EXPLAIN PLAN
SET QUERYNO = 1001
FOR
SELECT O.ORDER_ID, O.NET_AMOUNT, C.CUSTOMER_NAME
FROM ALICE.ORDERS O
JOIN ALICE.CUSTOMERS C ON C.ID = O.CUSTOMER_ID
WHERE O.ORDER_DATE >= CURRENT_DATE - 30 DAYS
;
-- Then read the access path back from PLAN_TABLE
SELECT QBLOCKNO, PLANNO, METHOD, ACCESSTYPE,
TNAME, ACCESSNAME, MATCHCOLS, INDEXONLY, SORTC_UNIQ, SORTN_UNIQ
FROM ALICE.PLAN_TABLE
WHERE QUERYNO = 1001
ORDER BY QBLOCKNO, PLANNO
;
Output:
QBLOCKNO PLANNO METHOD ACCESSTYPE TNAME ACCESSNAME MATCHCOLS INDEXONLY
1 1 0 I ORDERS ORDERS_X1 1 N
1 2 4 I CUSTOMERS CUSTOMERS_PK 1 Y
ACCESSTYPE of I is an indexed access; R would be a table-space scan (often a sign that you need an index). MATCHCOLS=0 plus ACCESSTYPE=I is an index scan (still better than R but worse than match-cols-on-leading-key).
DSN subsystem commands
The DSN command processor is a TSO interface to the Db2 address space. It is how you start/stop the subsystem, manage utilities, BIND, and run RUN/EXEC against application plans.
Entering DSN from TSO
DSN SYSTEM(DSN1) (* enter DSN with subsystem ID DSN1 *)
DSN
SUBSYSTEM(DSN1)
> END (* leave DSN, back to READY *)
Output:
DSN
SUBSYSTEM(DSN1)
END
DSN ENDED
Subsystem display
These commands report on Db2's running state — what threads are active, which databases are restricted, who is logged on. Type the leading hyphen.
-DISPLAY THREAD(*) (* all active threads *)
-DISPLAY THREAD(*) TYPE(SYSTEM) (* system threads only *)
-DISPLAY THREAD(*) TYPE(INACTIVE) (* inactive (parked) threads *)
-DISPLAY THREAD(ALICE) (* threads owned by user ALICE *)
-DISPLAY DATABASE(*) USE (* database use *)
-DISPLAY DATABASE(*) RESTRICT (* objects in restrict status (RO/STOP) *)
-DISPLAY DATABASE(DSNDB06) SPACENAM(*) (* DDL/catalog space *)
-DISPLAY UTILITY(*) (* running utilities *)
-DISPLAY GROUP (* DDF / data-sharing group *)
-DISPLAY LOCATION (* DDF remote locations *)
-DISPLAY DDF (* distributed data facility status *)
-DISPLAY LOG (* active log status *)
-DISPLAY BUFFERPOOL(BP0) (* bufferpool stats *)
-DISPLAY ARCHIVE (* archive log status *)
-DISPLAY FUNCTION SPECIFIC(*) (* registered UDFs *)
-DISPLAY PROCEDURE(*) (* registered stored procedures *)
-DISPLAY PROFILE (* SYSIBM.DSN_PROFILE_TABLE entries *)
Output (-DISPLAY THREAD(*)):
DSNV401I DISPLAY THREAD REPORT FOLLOWS -
DSNV402I ACTIVE THREADS -
NAME ST A REQ ID AUTHID PLAN ASID TOKEN
TSO T * 47 ALICE ALICE DSNESPCS 0044 19
TSO T * 23 BOB BOB DSNESPCS 0045 20
BATCH T * 1024 ALICEJ01 ALICE DSNTEP2 0046 21
DSNV403I DISPLAY ACTIVE REPORT COMPLETE
Start, stop, alter
These commands are restricted to authorized users (SYSCTRL or SYSADM RACF authority). Use them when bringing Db2 up after maintenance, or quiescing a database for a structural change.
-START DB2 (* start the whole subsystem *)
-STOP DB2 MODE(QUIESCE) (* stop after current workload *)
-STOP DB2 MODE(FORCE) (* immediate stop — rolls back txns *)
-START DATABASE(ALICEDB) (* re-enable a database *)
-STOP DATABASE(ALICEDB) SPACENAM(*) (* deny all access to one DB *)
-START DATABASE(ALICEDB) ACCESS(RO) (* read-only mode *)
-START DATABASE(ALICEDB) ACCESS(RW) (* read-write *)
-ALTER BUFFERPOOL(BP0) VPSIZE(20000) (* resize a bufferpool dynamically *)
-ALTER UTILITY(REORG) PRIORITY(HIGH) (* re-prioritise a running utility *)
-CANCEL THREAD(0046) (* by ASID — kills a hung BATCH job *)
-CANCEL THREAD(0046) NOBACKOUT (* skip rollback — fastest cancel *)
-TERM UTILITY(REORGJOB) (* terminate a stuck REORG job *)
Output:
DSNY002I -DSN1 SUBSYSTEM STOPPING
DSN9022I -DSN1 DSNYASCP 'STOP DB2' NORMAL COMPLETION
Recovery and admin
-DISPLAY LOG (* active log status *)
-ARCHIVE LOG (* force an archive-log switch *)
-SET LOG SUSPEND (* freeze log writes for backup *)
-SET LOG RESUME (* unfreeze *)
-RECOVER INDOUBT (* show in-doubt distributed UOWs *)
-RECOVER INDOUBT(*) ACTION(COMMIT)
-RECOVER POSTPONED (* postponed-abort rollback *)
-SET SYSPARM RELOAD (* re-read DSNZPARMs without restart *)
Output: (none — exits 0 on success)
BIND PACKAGE / BIND PLAN
BIND turns an SQL-laden DBRM (Database Request Module — produced by the precompiler) into a runnable package, then assembles one or more packages into a plan that an application binds at run time. You almost always BIND from DSN inside SPUFI option 5 (BIND/REBIND/FREE) or from JCL.
BIND PACKAGE — single program
BIND PACKAGE(ALICECOL)
MEMBER(BILLCALC)
LIBRARY('ALICE.DBRM.LIB')
ACTION(REPLACE)
ISOLATION(CS)
EXPLAIN(YES)
QUALIFIER(ALICE)
OWNER(ALICE)
CURRENTDATA(NO)
RELEASE(COMMIT)
VALIDATE(BIND)
SQLERROR(NOPACKAGE)
;
| Option | Effect |
|---|---|
PACKAGE(coll.name) | Collection ID + package name. Use ALICECOL for "alice's collection". |
MEMBER(...) | DBRM library member produced by the Db2 precompiler. |
LIBRARY(...) | PDS or PDSE holding DBRMs. |
| `ACTION(ADD | REPLACE)` |
| `ISOLATION(RR | RS |
| `EXPLAIN(YES | NO)` |
QUALIFIER(...) | Default schema prepended to unqualified table names. |
OWNER(...) | RACF authid whose grants apply at execution. |
| `CURRENTDATA(YES | NO)` |
| `RELEASE(COMMIT | DEALLOCATE)` |
| `VALIDATE(BIND | RUN)` |
| `SQLERROR(NOPACKAGE | CONTINUE)` |
BIND PLAN — assemble packages into an executable plan
BIND PLAN(BILLPLAN)
PKLIST(ALICECOL.*)
ACTION(REPLACE)
ISOLATION(CS)
OWNER(ALICE)
QUALIFIER(ALICE)
ACQUIRE(USE)
RELEASE(COMMIT)
CACHESIZE(1024)
CURRENTSERVER(*)
;
PKLIST(ALICECOL.*) says "include every package in the ALICECOL collection". A plan can also pin specific versions with PKLIST(ALICECOL.BILLCALC, ALICECOL.LEDGER01).
REBIND and FREE
REBIND re-evaluates access paths without re-reading the DBRM — use it after a RUNSTATS to pick up new statistics. FREE drops the package/plan.
REBIND PACKAGE(ALICECOL.BILLCALC) EXPLAIN(YES);
REBIND PACKAGE(ALICECOL.*) EXPLAIN(YES) APREUSE(WARN);
REBIND PLAN(BILLPLAN) EXPLAIN(YES);
FREE PACKAGE(ALICECOL.BILLCALC);
FREE PLAN(BILLPLAN);
Output:
DSNT201I -DSN1 PACKAGE = ALICECOL.BILLCALC SUCCESSFULLY REBOUND
DSNT232I -DSN1 SUCCESSFUL EXPLAIN FOR REBIND PACKAGE = ALICECOL.BILLCALC
APREUSE(WARN) is the safe default — Db2 reuses the previous access path if possible, warns if not.
Running SQL from JCL — DSNTEP2
DSNTEP2 is the IBM-supplied PL/I program that runs SQL from a sequential dataset, scheduled inside JCL. It is the batch counterpart of SPUFI and the workhorse of nightly DBA scripts.
//ALICEAD1 JOB (ACCT),'ALICE DEV',CLASS=A,MSGCLASS=X,NOTIFY=&SYSUID
//*
//STEP1 EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB DD DISP=SHR,DSN=DSN.SDSNLOAD
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN1)
RUN PROGRAM(DSNTEP2) PLAN(DSNTEP10) -
LIB('DSN.RUNLIB.LOAD') -
PARMS('/ALIGN(LHS)')
END
/*
//SYSIN DD *
SELECT COUNT(*) FROM ALICE.ORDERS;
COMMIT;
SELECT NAME, COLCOUNT FROM SYSIBM.SYSTABLES
WHERE CREATOR = 'ALICE'
ORDER BY NAME;
COMMIT;
/*
Output (in SYSPRINT):
DSNT400I SQLCODE = 0, SUCCESSFUL EXECUTION
COUNT
-----
1247385
NAME COLCOUNT
---------- --------
CUSTOMERS 8
ORDERS 14
PAYMENTS 6
DSNTEP4 is the v12+ replacement that supports unlimited SQL length per statement (DSNTEP2 caps at 32 KB). Pick DSNTEP4 for new work; DSNTEP2 only when targeting older subsystems.
SYSIBM catalog — common admin queries
The Db2 catalog (SYSIBM.* and SYSIBM.SYSADM.* tables) is the source of truth for every object in the subsystem. Each query below is a real diagnostic you will reach for repeatedly.
Find every table in your schema
SELECT NAME, COLCOUNT, CARDF, NPAGESF, CREATEDTS
FROM SYSIBM.SYSTABLES
WHERE CREATOR = 'ALICE'
AND TYPE = 'T'
ORDER BY NAME
;
Output:
NAME COLCOUNT CARDF NPAGESF CREATEDTS
CUSTOMERS 8 142853 3210 2025-11-04 12:01:23.451209
ORDERS 14 1247385 28456 2025-11-04 12:01:24.118021
PAYMENTS 6 891204 19112 2025-11-04 12:01:24.871009
Columns of one table
SELECT NAME, COLNO, COLTYPE, LENGTH, SCALE, NULLS, DEFAULT, REMARKS
FROM SYSIBM.SYSCOLUMNS
WHERE TBCREATOR = 'ALICE' AND TBNAME = 'ORDERS'
ORDER BY COLNO
;
Output:
NAME COLNO COLTYPE LENGTH SCALE NULLS DEFAULT
ORDER_ID 1 INTEGER 4 0 N N
CUSTOMER_ID 2 INTEGER 4 0 N N
ORDER_DATE 3 DATE 4 0 N N
NET_AMOUNT 4 DECIMAL 9 2 N N
STATUS 5 CHAR 2 0 N Y
...
Indexes on a table
SELECT IX.NAME AS INDEX_NAME,
IX.UNIQUERULE,
IX.CLUSTERING,
IX.CARDF,
KEY.COLSEQ,
KEY.ORDERING,
KEY.COLNAME
FROM SYSIBM.SYSINDEXES IX
JOIN SYSIBM.SYSKEYS KEY
ON KEY.IXCREATOR = IX.CREATOR
AND KEY.IXNAME = IX.NAME
WHERE IX.TBCREATOR = 'ALICE'
AND IX.TBNAME = 'ORDERS'
ORDER BY IX.NAME, KEY.COLSEQ
;
Output:
INDEX_NAME UNIQUERULE CLUSTERING CARDF COLSEQ ORDERING COLNAME
ORDERS_PK P Y 1247385 1 A ORDER_ID
ORDERS_X1 D N 142853 1 A CUSTOMER_ID
2 A ORDER_DATE
Packages in a collection
SELECT NAME, VERSION, BINDTIME, VALID, OPERATIVE, ISOLATION, RELEASE
FROM SYSIBM.SYSPACKAGE
WHERE COLLID = 'ALICECOL'
ORDER BY NAME, BINDTIME DESC
;
Output:
NAME VERSION BINDTIME VALID OPERATIVE ISOLATION RELEASE
BILLCALC V2026.05.24 2026-05-24-09.13.42 Y Y CS C
BILLCALC V2026.05.10 2026-05-10-22.40.17 N Y CS C
LEDGER01 V2026.04.30 2026-04-30-08.15.04 Y Y CS C
Plans and their package lists
SELECT P.NAME, P.CREATOR, P.BINDDATE, P.VALID,
L.COLLID, L.NAME AS PACKAGE
FROM SYSIBM.SYSPLAN P
JOIN SYSIBM.SYSPACKLIST L ON L.PLANNAME = P.NAME
WHERE P.NAME = 'BILLPLAN'
ORDER BY L.SEQNO
;
Output:
NAME CREATOR BINDDATE VALID COLLID PACKAGE
BILLPLAN ALICE 2026-05-24 Y ALICECOL BILLCALC
BILLPLAN ALICE 2026-05-24 Y ALICECOL LEDGER01
Copies and recovery — SYSCOPY
SYSIBM.SYSCOPY tracks every full image-copy, incremental copy, LOAD, REORG, and recover. It is the input to RECOVER utilities and the first place to look when a recovery is needed.
SELECT DBNAME, TSNAME, ICTYPE, ICDATE, ICTIME, DSNAME
FROM SYSIBM.SYSCOPY
WHERE DBNAME = 'ALICEDB'
AND ICDATE >= '20260520'
ORDER BY ICDATE DESC, ICTIME DESC
FETCH FIRST 20 ROWS ONLY
;
Output:
DBNAME TSNAME ICTYPE ICDATE ICTIME DSNAME
ALICEDB ORDERTS F 20260524 040127 ALICE.IC.ORDERTS.D260524.T040127
ALICEDB CUSTTS F 20260524 040055 ALICE.IC.CUSTTS.D260524.T040055
ALICEDB ORDERTS R 20260523 220012 (REORG marker — no copy DSN)
ICTYPE codes: F=full copy, I=incremental, R=REORG, L=LOAD, A=ALTER, S=STOSPACE, M=MODIFY.
Active locks
SELECT LOCK_OWNER, LOCK_LEVEL, LOCK_HOLD_TYPE,
LOCK_DURATION, OBJ_SCHEMA, OBJ_NAME
FROM TABLE(SYSPROC.MON_GET_LOCKS(NULL,-1)) AS L
WHERE OBJ_SCHEMA = 'ALICE'
;
Output:
LOCK_OWNER LOCK_LEVEL LOCK_HOLD_TYPE OBJ_SCHEMA OBJ_NAME
0046 IX W ALICE ORDERS
0046 X W ALICE ORDERS_PK
DCLGEN — generate host-language declarations
DCLGEN writes the COBOL/PL/I/C declarations needed for a host program to talk to a Db2 table — record layout, indicator variables, column SQL DESCRIPTOR. Run from SPUFI option 2 (DCLGEN) or from DSN.
DCLGEN TABLE(ALICE.ORDERS)
LIBRARY('ALICE.SRC.LIB(ORDERS)')
LANGUAGE(COBOL)
APOST
QUOTE(NO)
STRUCTURE(ORDERS-REC)
NAMES(O-)
INDVAR(YES)
;
Output (ALICE.SRC.LIB(ORDERS)):
*****************************************************************
* DCLGEN TABLE(ALICE.ORDERS) *
* LIBRARY(ALICE.SRC.LIB(ORDERS)) *
* LANGUAGE(COBOL) *
* STRUCTURE(ORDERS-REC) *
* NAMES(O-) *
*****************************************************************
EXEC SQL DECLARE ALICE.ORDERS TABLE
( ORDER_ID INTEGER NOT NULL,
CUSTOMER_ID INTEGER NOT NULL,
ORDER_DATE DATE NOT NULL,
NET_AMOUNT DECIMAL(9, 2) NOT NULL,
STATUS CHAR(2)
) END-EXEC.
01 ORDERS-REC.
10 O-ORDER-ID PIC S9(9) USAGE COMP.
10 O-CUSTOMER-ID PIC S9(9) USAGE COMP.
10 O-ORDER-DATE PIC X(10).
10 O-NET-AMOUNT PIC S9(7)V99 USAGE COMP-3.
10 O-STATUS PIC X(2).
| Option | Meaning |
|---|---|
| `LANGUAGE(COBOL | PLI |
STRUCTURE(name) | Top-level structure name (01 …). |
NAMES(prefix) | Prepend prefix to every column name (avoids COBOL keyword clashes). |
INDVAR(YES) | Generate parallel indicator-variable array. |
APOST / QUOTE | Literal-delimiter convention for the COBOL compiler. |
LABEL(YES) | Include LABEL (table/column comments) as COBOL paragraphs. |
Common pitfalls
AUTOCOMMIT NOand walking away — leaves an uncommitted unit-of-work that holds locks and may block other users. AlwaysCOMMIT;(or set AUTOCOMMIT YES) before leaving SPUFI.- Forgetting the trailing
;— SPUFI runs the previous statement implicitly with no separator and printsDSNT408I … TERMINATOR NOT FOUND. Every statement needs the configured terminator. MAX SELECT LINEStoo small — silently truncates large result sets. Raise it for ad-hoc reporting (a few thousand is reasonable).SQLCODE = -805after a bind change — "package not found in plan PKLIST" — means a freshly bound package is not yet picked up by the plan. Rebind the plan or wait for the next allocate.- EXPLAIN against a non-existent
PLAN_TABLE—SQLCODE = -204. Run the IBM-supplied DDL (SDSNSAMP(DSNTESC)) once per schema. -STOP DB2 MODE(FORCE)during a long REORG — leaves the tablespace inSTOPorRESTPstatus. UseMODE(QUIESCE)and let utilities drain.- BIND
ACTION(REPLACE)withoutVERSION— overwrites the running package; existing plans relink atomically but any cached statement plans flush. For zero-downtime promotion useVERSION(AUTO)+DEPLOY. ISOLATION(RR)on a high-concurrency table — Repeatable Read holds locks until COMMIT; under load you get deadlocks. UseCSunless you genuinely need repeatable reads.CURRENTDATA(YES)legacy default — disables lock-avoidance. Modern shops bind everything withCURRENTDATA(NO).DCLGENoverwrites your hand-edited copybook — DCLGEN-generated members are regenerable artifacts. Keep host-program copybooks separate from DCLGEN output.SYSIBM.SYSCOPYrows held forever — without periodicMODIFY RECOVERY, the catalog row count grows without bound. ScheduleMODIFY RECOVERY DELETE AGE(*)nightly per tablespace.-CANCEL THREADon a Db2-internal thread — refuses withDSNV413I. Use-DISPLAY THREAD(*) TYPE(SYSTEM)first to identify owner.
Real-world recipes
Ad-hoc SQL → save formatted results
You need a CSV-friendly export of a query. SPUFI cannot write CSV directly, but its output dataset is fixed-column and easily reshaped with DFSORT or a one-line REXX.
-- ALICE.SQL(EXPORT)
SELECT CHAR(ORDER_ID) || ',' ||
CHAR(CUSTOMER_ID) || ',' ||
CHAR(ORDER_DATE, ISO) || ',' ||
CHAR(NET_AMOUNT) AS ROW
FROM ALICE.ORDERS
WHERE ORDER_DATE = CURRENT_DATE - 1 DAY
;
Output (sample):
1247380,884,2026-05-24,128.40
1247381,915,2026-05-24,89.99
...
Then in TSO, copy the SPUFI output dataset to a CSV PDS member and FTP it down.
Promote BIND to production without an outage
Two collections (ALICETST, ALICEPRD), each pointing at the same DBRMs. The plan list switches collections to roll forward.
BIND PACKAGE(ALICEPRD) -
COPY(ALICETST.BILLCALC) -
COPYVER(V2026.05.24) -
ACTION(REPLACE) DEPLOY -
EXPLAIN(YES);
BIND PLAN(BILLPLAN) -
PKLIST(ALICEPRD.*) -
ACTION(REPLACE);
Output:
DSNT201I -DSN1 PACKAGE = ALICEPRD.BILLCALC SUCCESSFULLY BOUND
DSNT231I -DSN1 PLAN = BILLPLAN SUCCESSFULLY BOUND
Quiesce a tablespace, copy, restart
A 4-step admin recipe for a clean image-copy without down-time for the application.
-START DATABASE(ALICEDB) SPACENAM(ORDERTS) ACCESS(RO)
//* Run IMAGCOPY utility here (DSN1COPY or DSN1UTILS)
-START DATABASE(ALICEDB) SPACENAM(ORDERTS) ACCESS(RW)
-DISPLAY DATABASE(ALICEDB) SPACENAM(ORDERTS) USE
Output:
DSNT392I -DSN1 DISPLAY DATABASE SUMMARY
NAME TYPE PART STATUS CONNID CORRID
ORDERTS TS 0001 RW - -
Use SPUFI to test a row-level rollback
Set AUTOCOMMIT NO in the Defaults panel, run an UPDATE, run a SELECT, then ROLLBACK — perfect for verifying expected before-and-after behavior without touching production.
SELECT STATUS FROM ALICE.ORDERS WHERE ORDER_ID = 1247380;
UPDATE ALICE.ORDERS
SET STATUS = 'XX'
WHERE ORDER_ID = 1247380;
SELECT STATUS FROM ALICE.ORDERS WHERE ORDER_ID = 1247380;
ROLLBACK;
SELECT STATUS FROM ALICE.ORDERS WHERE ORDER_ID = 1247380;
Output:
STATUS
------
OK
STATUS
------
XX
DSNE617I COMMIT PERFORMED, SQLCODE IS 0 -- ROLLBACK confirmation
STATUS
------
OK
Hunt rows by approximate value with EXPLAIN feedback
You have a slow query and you want to see whether the optimizer picks the right index after creating a new one. EXPLAIN before and after, compare the plans.
CREATE INDEX ALICE.ORDERS_X2
ON ALICE.ORDERS (ORDER_DATE, STATUS);
EXPLAIN PLAN SET QUERYNO = 2001 FOR
SELECT * FROM ALICE.ORDERS
WHERE ORDER_DATE >= CURRENT_DATE - 7 DAYS
AND STATUS = 'OK'
;
SELECT QUERYNO, METHOD, ACCESSTYPE, ACCESSNAME, MATCHCOLS
FROM ALICE.PLAN_TABLE WHERE QUERYNO = 2001
ORDER BY PLANNO;
Output:
QUERYNO METHOD ACCESSTYPE ACCESSNAME MATCHCOLS
2001 0 I ORDERS_X2 2
MATCHCOLS=2 confirms both ORDER_DATE and STATUS predicates index-match against ORDERS_X2.
Nightly catalog health check from JCL
A JCL job that runs every night, snapshots SYSPACKAGE validity and SYSCOPY recency, and emails a report via SMTP — a sample of how SPUFI-style SQL plus DSNTEP2 plus JCL combine into production-grade automation.
//ALICEAD2 JOB (ACCT),'ALICE DEV',CLASS=A,MSGCLASS=X,NOTIFY=&SYSUID
//STEP1 EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB DD DISP=SHR,DSN=DSN.SDSNLOAD
//SYSTSPRT DD DISP=(NEW,CATLG),DSN=ALICE.NIGHTLY.CATCHK,
// SPACE=(CYL,(1,1)),DCB=(RECFM=FB,LRECL=133)
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN1)
RUN PROGRAM(DSNTEP2) PLAN(DSNTEP10) LIB('DSN.RUNLIB.LOAD')
END
/*
//SYSIN DD *
SELECT 'INVALID PACKAGE' AS PROBLEM, COLLID, NAME, VERSION
FROM SYSIBM.SYSPACKAGE
WHERE VALID = 'N' OR OPERATIVE = 'N'
ORDER BY COLLID, NAME;
SELECT 'STALE COPY' AS PROBLEM, DBNAME, TSNAME,
CURRENT_TIMESTAMP - TIMESTAMP(DATE(ICDATE), TIME(ICTIME))
FROM SYSIBM.SYSCOPY
WHERE ICTYPE = 'F'
AND TIMESTAMP(DATE(ICDATE), TIME(ICTIME)) <
CURRENT_TIMESTAMP - 7 DAYS
ORDER BY DBNAME, TSNAME;
COMMIT;
/*
Output (in ALICE.NIGHTLY.CATCHK):
PROBLEM COLLID NAME VERSION
INVALID PACKAGE ALICECOL BILLCALC V2026.05.10
PROBLEM DBNAME TSNAME (AGE)
STALE COPY ALICEDB PAYMENTS 9 03:14:22
Drive Db2 from Zowe (off-platform)
For the full off-platform pattern see zowe. Once the Db2 plug-in is installed, the SQL flow becomes:
zowe plugins install @zowe/db2-for-zowe-cli@latest
zowe db2 execute sql \
--command "SELECT COUNT(*) FROM ALICE.ORDERS" \
--hostname db2.example.com --port 5040 \
--database DSN1 --user ALICE --password "$DB2PW"
Output:
1
-------
1247385
SQL Data Insights — AI_* built-ins (Db2 13 FL 500+)
SQL Data Insights ships with Db2 13 and lets you embed ML-style similarity, semantic clustering, and analogy queries directly into SQL. The built-ins resolve in the Db2 engine — no data movement — and rely on a trained model per table created with CREATE_MODEL. As of FL 504, AI_COMMONALITY(expression) returns how typical a value is relative to its column's centroid; together with AI_SIMILARITY and AI_SEMANTIC_CLUSTER these are the core building blocks.
-- Once the model exists for ALICE.ORDERS:
SELECT ORDER_ID,
AI_COMMONALITY(STATUS) AS COMMONALITY_SCORE
FROM ALICE.ORDERS
WHERE AI_COMMONALITY(STATUS) < 0.30
ORDER BY COMMONALITY_SCORE ASC
FETCH FIRST 20 ROWS ONLY
;
SELECT A.CUSTOMER_ID, B.CUSTOMER_ID,
AI_SIMILARITY(A.CUSTOMER_ID, B.CUSTOMER_ID) AS SIM
FROM ALICE.ORDERS A, ALICE.ORDERS B
WHERE A.CUSTOMER_ID < B.CUSTOMER_ID
ORDER BY SIM DESC
FETCH FIRST 10 ROWS ONLY
;
Output:
ORDER_ID COMMONALITY_SCORE
1247521 0.124
1247590 0.187
...
Values close to 0 mark outliers (anomalies); values near 1 mark very typical rows. Use this as a first pass for fraud / data-quality screening directly from SPUFI.
Trace package usage to find dead code
Use SYSPACKDEP to find packages that depend on a table you want to drop — every dependency you must clean up first.
SELECT DCOLLID, DNAME, BNAME, BTYPE
FROM SYSIBM.SYSPACKDEP
WHERE BQUALIFIER = 'ALICE'
AND BNAME = 'ORDERS'
;
Output:
DCOLLID DNAME BNAME BTYPE
ALICECOL BILLCALC ORDERS T
ALICECOL LEDGER01 ORDERS T
REPORTSC MONTHLY ORDERS T
Find which thread is blocking yours
When SPUFI hangs on LOCK_TIMEOUT, the holder is somewhere in -DISPLAY THREAD. The LUWID + corresponding MON_GET_LOCKS row link them up.
-DISPLAY THREAD(*) DETAIL
SELECT W.HOLDING_AUTHID, W.HOLDING_PARTITION, W.HOLDING_TYPE,
T.AUTHID AS WAITING_FOR
FROM TABLE(SYSPROC.MON_GET_APPL_LOCKWAIT(NULL,-1)) AS W
JOIN TABLE(SYSPROC.MON_GET_CONNECTION(NULL,-1)) AS T
ON T.APPLICATION_HANDLE = W.HOLDING_APPLICATION_HANDLE
;
Output:
HOLDING_AUTHID HOLDING_PARTITION HOLDING_TYPE WAITING_FOR
BOB 0 X ALICE
You now know BOB is holding the X-lock that ALICE is waiting on — call him.