DSNTEP2 aka Batch SPUFI

By | In Blog, DB2, SQL Server | February 19th, 2015

DSNTEP2 is an application program that can be used to issue DB2 dynamic SQL statements. It is sometimes referred to as “Batch SPUFI” because it allows you to submit SQL in batch similar to how SPUFI allows online SQL execution.

The following sample JCL demonstrates the capability of DSNTEP2 to issue DCL, DDL, and DML dynamically.

The DNSTEP2 program is written in the PL/I programming language. Prior to DB2 V6, you needed to have a PL/I compiler to use DSNTEP2. However, as of V6 IBM now provides both the source code and an object code version of DSNTEP2 with DB2. So, you no longer need a PL/I compiler to use DSNTEP2.

Because DSNTEP2 is an application program, and the PL/I source code is provided with DB2, a knowledgeable PL/I programmer can easily modify the code. After doing so, of course, it must be compiled, linked, and bound before it can be used.

DSNTEP2 can process almost every SQL statement that can be executed dynamically. DSNTEP2 accepts

  • The GRANT and REVOKE DCL statements,
  • The ALTER, COMMENT ON, CREATE, and DROP DDL statements,
  • The DELETE, INSERT, SELECT, and UPDATE DML statements, and
  • The COMMIT, ROLLBACK, EXEC SQL, EXPLAIN, and LOCK statements.

The only important statement that DSNTEP2 does not support is the LABEL ON DDL statement. Of course, DSNTEP2 can be modified to support this statement if you have PL/I knowledge and a PL/I compiler.

When Does DSNTEP2 Commit?

Well, the simple answer to that question is that the results of the SQL are committed upon completion of all the SQL. A helpful answer is a little longer.

First off, you need to know that DSNTEP2 has an internal parameter named MAXERRORS that controls the number of failing statements that can occur before it stops. A failing statement is one which returns a negative SQLCODE. The value of MAXERRORS is set to 10 inside the program, so DSNTEP2 will allow 9 failing SQL statements but when it hits the 10th failing statement, it will exit, COMMITting all other work.

This is ugly because it can wreak havoc on the integrity of your data. I mean, who wants to figure out what was run, what was impacted, and then try to rebuild a job to fix data and/or restart at the right place? To rerun DSNTEP2, remember that all SQL statements that completed with a 0 SQL code were committed. These statements should not be rerun. All SQL statements completed with a negative SQL code must be corrected and reprocessed.

Certain severe errors cause DSNTEP2 to exit immediately. One severe error is a -101 “SQL statement too long or too complex”.

If any SQL errors occurred during the execution of DSNTEP2, a return code of 8 is returned by the job step.

At any rate, DSNTEP2 never issues an explicit COMMIT or ROLLBACK by itself. A COMMIT occurs at the end unless the program abends.

Specify Your SQL Statements Properly

The SQL to be run by DSNTEP2 is specified in SYSIN. Be sure to code the DSNTEP2 input properly.

DSNTEP2 reads SQL statements from an input data set with 80-byte records. The SQL statements must be coded in the first 72 bytes of each input record. SQL statements can span multiple input records and are terminated by a semicolon (;). Semicolons are not permitted in the text of the SQL statement.

Liberally Comment DSNTEP2 Input

Comments can be passed to DSNTEP2 in the SQL statements using two hyphens in columns 1 and 2 or a single asterisk in column 1. Doing so is good form and helps others to understand what your DSNTEP2 job is attempting to accomplish.

Bottom Line

DSNTEP2 is especially useful for running one-off SQL statements. Use DSNTEP2 when you have ad hoc SQL to run in a batch environment. DSNTEP2 is easier than writing your own quick and dirty programs to run ad hoc SQL in batch. It is simple to set up and saves time. But be careful if you have multiple SQL modification statements (INSERT, UPDATE, DELETE) because of the above-mentioned possibility of causing data integrity problems if some of the statements fail and others succeed.

Originally published on Craig Mullins’ blog http://db2portal.blogspot.com/.

Learn more about DB2 performance and check out our white paper Best Practices for Optimizing DB2 Performance: A guide for DBA Managers. It offers multiple guidelines and tips for improving performance within the three major performance tuning categories required of every DB2 implementation: the application, the database and the system.

Read more posts from DB2 expert Craig Mullins 

Craig Mullins
Consultant at Mullins Consulting, Inc
Craig S. Mullins is working with Datavail and its DB2 practice to expand offerings. He is president and principal consultant at Mullins Consulting, Inc. and the publisher of The Database Site. Mullins has 30 years of experience in all facets of database management and is the author of two books: “DB2 Developer’s Guide” currently in its 6th edition and “Database Administration: The Complete Guide to DBA Practices and Procedures,” the industry’s only guide to heterogeneous DBA.

Leave a Reply

Your email address will not be published.
Required fields are marked (*).

3 thoughts on “DSNTEP2 aka Batch SPUFI”
  1. I used to own the book: The Complete Guide to DBA Practices and Procedures, and it has been one of the most accomplished textbooks on DBMS, across platforms, for me. Hopefully with the new 2012 edition, the book proves even more innovative and resourceful for DBAs.However,for JAVA and J2EE compliant DBMS platforms, it is simply impossible anymore for Enterprise DBAs and DBA Architects not to know enterprise applications and application developments with open, enterprise standards tools such as Eclipse IDE and programming language such as Java and J2EE or .Net irrespective of platforms such as UNIX,LINUX or Windows OS; and with the increasing need to access and manage backend systems, web services – SOAP, UDDI, WSDL, SAML and web applications with Application servers such as Weblogic, Apache Tomcat, Websphere, JBoss, Netweaver, etc. DBAs must always strive for innovations in enterprise applications and enterprise information systems. Java in the database is the ice in the cake for enterprise applications developments and deployments. Thank you for the new book and I would get it again, pretty soon. Hope you do like it very much at DatAvail! And I am always proud of your works in the DBMS world!

  2. Hello Martins:

    Thanks for the kind words about my book… and I agree with your sentiments about DBAs needing to understand modern programming languages and tools (IDEs) for development. As DBAs, we must always be learning new things. You simply cannot sit still and expect to thrive… or even survive!

    Regarding DatAvail, yes, they are good group of folks with a lot of technical acumen and database knowledge. I am enjoying working with them to help expand and support their DB2 offerings!

    Cheers,
    Craig

  3. We could use maxerrors 0 where the job abends in the first error. Although it is useful, i agree that DSNTEP2 is a little risky but unavoidable in todays world.

    Regards
    Eswar