Run DSNTEP2 in DB2 for z/OS

What DSNTEP2 does

DSNTEP2 is an IBM-supplied sample program that issues dynamic SQL. Under the covers it is a PL/I application that connects to a Db2 subsystem, prepares and executes the statements you supply, and prints results. Shops use it for overnight extracts, one-off DDL in change windows, smoke tests after promotions, and any SQL work that should be auditable as a batch job rather than a TSO session.

Think of SPUFI as the interactive cousin and DSNTEP2 as the batch cousin. Both execute SQL; DSNTEP2 adds JCL scheduling, SYSOUT capture, and return codes that operations teams already know how to monitor. DSNTEP4 is a related sample that can fetch many rows at once when you need better SELECT throughput.

Prerequisites

Before you submit anything, confirm these items with your site standards. Guessing library or plan names is the fastest path to a failed attach or a plan-not-found abend.

• A Db2 for z/OS subsystem identifier (SSID) you are allowed to use, usually a test subsystem for first practice
• STEPLIB or JOBLIB access to prefix.SDSNLOAD and, when required, prefix.SDSNEXIT
• The load library that contains the DSNTEP2 module (often prefix.RUNLIB.LOAD) and the bound plan name for that release
• Authority to execute the plan and to run the SQL you intend (SELECT, INSERT, DDL, and so on)
• A valid JOB card, accounting information, and output class for your installation
• SQL that has been reviewed—especially anything that changes data or creates objects

Installation jobs usually bind DSNTEP2 during Db2 setup. Plan names often include a version hint (for example DSNTEP81, DSNTEP91, or a Db2 12/13 style name). Never copy a plan name from a blog without confirming it exists on your subsystem.

Steps: build and run a DSNTEP2 job

1. Start from a standard JCL skeleton

The usual pattern executes IKJEFT01, the TSO Terminal Monitor Program. SYSTSIN contains DSN commands. SYSIN contains SQL. SYSPRINT receives DSNTEP2 output. SYSTSPRT receives TSO/DSN messages.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
//RUNTEP2 JOB (ACCT),'DSNTEP2 SQL',CLASS=A,MSGCLASS=X,
//             NOTIFY=&SYSUID
//*--------------------------------------------------
//* Run dynamic SQL with DSNTEP2 (batch SPUFI)
//* Confirm SSID, plan, and libraries with your DBA
//*--------------------------------------------------
//JOBLIB   DD DISP=SHR,DSN=DSN.V13R1M0.SDSNEXIT
//         DD DISP=SHR,DSN=DSN.V13R1M0.SDSNLOAD
//STEP1    EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN  DD *
  DSN SYSTEM(DB2T)
  RUN PROGRAM(DSNTEP2) PLAN(DSNTEP13) -
      LIB('DSN.V13R1M0.RUNLIB.LOAD')
  END
/*
//SYSIN    DD *
  SELECT CURRENT TIMESTAMP AS RUN_TS,
         CURRENT SQLID AS SQLID
  FROM SYSIBM.SYSDUMMY1;
/*

Replace DB2T, the DSN high-level qualifiers, and DSNTEP13 with values from your shop. The continuation hyphen after PLAN keeps the RUN command on two lines for readability.

2. Code SYSIN SQL correctly

DSNTEP2 reads fixed 80-byte records and uses columns 1–72. Put meaningful SQL in that area. End each statement with a semicolon. Statements may span multiple lines. Do not put SQL only in columns 73–80; those columns are ignored for statement text.

1
2
3
4
5
6
7
8
9
10
-- Simple catalog check
SELECT NAME, CREATOR, TYPE
  FROM SYSIBM.SYSTABLES
 WHERE CREATOR = 'TRAINING'
 FETCH FIRST 20 ROWS ONLY;
 
-- Sample DML (use only on test tables you own)
UPDATE TRAINING.EMPLOYEE
   SET LAST_UPD = CURRENT TIMESTAMP
 WHERE EMPNO = '000010';

You can also point SYSIN at a sequential or PDS member that holds the SQL so the JCL stays short and the script can be version-controlled:

1
//SYSIN DD DISP=SHR,DSN=YOUR.SQL.LIB(SMOKETST)

3. Optional PARMS for formatting and behavior

The RUN command can pass PARMS that control alignment, mixed DBCS data, warning tolerance, and how many errors to allow before stopping. Exact options depend on the sample version installed at your site. A common pattern looks like this:

1
2
3
  RUN PROGRAM(DSNTEP2) PLAN(DSNTEP13) -
      PARMS('/ALIGN(LHS)') -
      LIB('DSN.V13R1M0.RUNLIB.LOAD')

Use PARMS only when you understand what they change. For a first successful run, omit them and keep the SQL tiny.

4. Submit and watch the job

Submit with SDSF, ISPF option 3.4 / SUBMIT, or your shop's scheduler. Wait for the step to end, then open SYSTSPRT and SYSPRINT. Do not assume success from a green job name alone—always read the SQLCODE section.

Verify results

Verification has three layers: JCL status, DSNTEP2 messages, and business confirmation.

• Return code 0 — statements completed without SQL errors (warnings may still appear as RC 4)
• SYSTSPRT — confirms DSN connected to the intended SYSTEM and that RUN found the program and plan
• SYSPRINT — shows each statement, SQLCODE, and SELECT result tables
• Follow-up query — after INSERT/UPDATE/DELETE, run a SELECT (in the same job or a second job) to prove the rows changed as expected

Typical DSNTEP2 return codes: 0 successful completion, 4 SQL warning, 8 SQL error, 12 severe condition (very long statement, severe SQLCODE class, or message formatting failure). Treat any non-zero RC as “read the spool before you declare victory.”

1
2
3
4
-- After a test UPDATE, confirm the row
SELECT EMPNO, LAST_UPD
  FROM TRAINING.EMPLOYEE
 WHERE EMPNO = '000010';

Common errors

Plan or package not found

Symptoms include bind-related SQLCODEs such as -805 or messages that the plan does not exist. Cause: wrong PLAN name, plan bound on another subsystem, or RUNLIB pointing at the wrong release. Fix: get the current plan and library list from the DBA or from your site's Db2 standards document.

Subsystem attach failures

Wrong SYSTEM(ssid), inactive Db2, or missing SDSNLOAD on STEPLIB produces attach failures before any SQL runs. Fix the SSID and libraries first; SQL debugging comes later.

SQLCODE authorization errors (-551 / -552)

Your ID can run DSNTEP2 but still lack SELECT or INSERT on the object. Grant the needed privilege or switch to a table you own in a training schema.

Statement truncated or “too long”

Text past column 72 is ignored, which can silently break a statement or leave a missing semicolon. Severe length or complexity errors can drive return code 12. Keep lines within 72 and split long predicates cleanly.

Partial success on multi-statement change scripts

If one UPDATE fails and another succeeds, you can leave data in an inconsistent state because DSNTEP2 is not a full application transaction framework. Prefer small scripts, test tables, and explicit verification. For production changes, use change control and understand your site's commit behavior for dynamic SQL jobs.

Empty SYSPRINT

Missing SYSPRINT DD, wrong DD name, or the job failing in IKJEFT01 before RUN starts can leave you with nothing useful to read. Allocate SYSPRINT and SYSTSPRT on every job.

Good habits for batch SQL

• Start with a harmless SELECT against SYSIBM.SYSDUMMY1 or a training table
• Qualify objects with schema names so CURRENT SQLID surprises do not bite you
• Keep change scripts short and restartable
• Store SQL in a PDS member so reviews and audits see the exact text
• Capture SYSOUT in your scheduler history for every production run

Explain It Like I'm Five

Imagine Db2 is a giant filing cabinet. SPUFI is walking up to the cabinet yourself and reading a folder. DSNTEP2 is writing a note that says “please open these folders and print what you find,” then giving that note to a batch helper who works while you do something else. The helper needs the right building (subsystem), the right badge (authority), and a clear note written in the lines of the page it can read (columns 1–72, ending with a semicolon). When the helper finishes, it leaves a report (SYSPRINT) and a grade (return code) so you know whether the work went well.

Exercises

1. Copy the sample JCL, replace SSID/plan/libraries with your site values, and run a SELECT on SYSIBM.SYSDUMMY1. Record the return code and the printed timestamp.
2. Move the same SELECT into a PDS member and change SYSIN to DISP=SHR against that member. Confirm SYSPRINT still shows the result.
3. Intentionally omit the terminating semicolon and observe how DSNTEP2 reports the problem, then fix it.
4. On a training table you own, run a SELECT, then a small UPDATE, then a verifying SELECT in one SYSIN. Explain what you would check if the middle statement failed.
5. Ask your DBA for the local DSNTEP2 plan name and document it in your personal runbook next to the correct RUNLIB data set.