View Only

Batch SPUFI

By Emil Kotrc posted Sep 11, 2014 01:54 PM

Recommend

by: Emil Kotrc, Principal Software Engineer, CA Technologies

Introduction

One of the first tools every beginner meets in DB2 for z/OS is most probably SPUFI. Everyone likes abbreviations, so does IBM and therefore SPUFI stands for SQL Processing Using File Input.

All is good, you can issue your SQL using SPUFI online, interactively see the results, and start playing with SQL. Later, you may want to automate things and would start searching for something like a batch SPUFI. IBM documentation does not mention anything like that, but properly chosen keywords in a web search engine will likely get you across few pages mentioning DSNTEP2 or DSNTEP4 programs (see the references below).

This article will briefly explain the DSNTEP2 and DSNTEP4 programs, where to find them, and how to use them.

Getting DSNTEP2

DSNTEP2 (and DSNTEP4 - we will discuss the differences later) is just an IBM provided dynamic SQL sample program delivered with the base install of DB2 for z/OS, which supports DML, DDL, and DCL statements. It was originally a PL/1 program, but IBM provides the object module as well; in case you don’t have a PL/1 compiler. DSNTEP2 is usually built during the DB2 installation as a part of the Installation Verification Procedures (IVP). DSNTEP2 may be used to issue dynamic SQL statements to query the well known sample tables (like EMP, DEPT, ...) to verify the DB2 installation.

Where can you find your DSNTEP2 program? Of course it depends on your installation, so you may need to ask your system administrator, or you can just start chasing the program yourself. Let say your DB2 installation HLQ is prefix. The source code is prefix.SDSNSAMP(DSNTEP2), the object module is prefix.SDSNSAMP(DSNTEP2L). The JCL that builds the DSNTEP2 using the provided object module is prefix.SDSNSAMP(DSNTEJ1L); the JCL that includes the PL/1 compilation is prefix.SDSNSAMP(DSNTEJ1P).

As mentioned above, the preparation of these programs probably happened during the DB2 installation (the tailored installation jobs should be found in prefix.NEW.SDSNSAMP). From the sample JCL you could decrypt where the modules have been placed. Usually, the load modules of the sample programs are in prefix.RUNLIB.LOAD. Check it at your site, you should find prefix.RUNLIB.LOAD(DSNTEP2) program.

Running DSNTEP2

Now, when you are able to locate the DSNTEP2 load module, the next step is to run it. As you know, everything in DB2 needs its plan. You can find the plan used by DSNTEP2 in the JCL that builds the module, or you can even refer to the IBM documentation. The plan that DSNTEP2 uses usually depends on the DB2 version. For DB2 11 it is DSNTEPB1, for DB2 10 it is DSNTEPA1 by default, but check your installation as the plan names may differ. The job that BINDs DSNTEP2 is again DSNTEJ1L or DSNTEJ1P mentioned in the previous section. For the completeness, the corresponding DBRM for the object module is in prefix.SDSNSAMP(DSN@EP2L) and if you compile you should find DBRM in prefix.DBRMLIB.DATA(DSNTEP2).

Once you know the plan and the library where the final loadmodule is stored, you can just use DSN RUN to run DSNTEP2. The sample JCL step (refer to IBM documentation) that runs DSNTEP2 in DB2 11 is the following:

//RUNTEP2 EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(ssid)
RUN PROGRAM(DSNTEP2) PLAN(DSNTEPB1) -

       PARMS('/ALIGN(LHS) MIXED TOLWARN(YES)') -
       LIB('prefix.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN    DD *
SELECT * FROM SYSIBM.SYSDUMMY1;

/*

You provide the SQL in the SYSIN DD. It can contain multiple SQL statements, the statements can also span multiple lines, but be aware that DSNTEP2 reads only up to the column 72. The default separator is semicolon and the comment lines can start with double dash as you would expect.

DSNTEP2 writes its results to SYSPRINT DD. The output of the previous example follows and as you can see, DSNTEP2 provides the tabular output:

PAGE 1

***INPUT STATEMENT:

SELECT * FROM SYSIBM.SYSDUMMY1;

+---------+

| IBMREQD |

+---------+

1_| Y |

+---------+

SUCCESSFUL RETRIEVAL OF 1 ROW(S)

Using DSNTEP2

IBM is doing a good job of documenting their sample programs. Even though the programs are just samples, you can find the detailed information about DSNTEP2 (and other samples) in the Application programming and SQL guide. The basic usage is to provide your SQL and DSNTEP2 will execute it dynamically, but there are few options available. For example, you can change the termination character, which is particularly useful when you define a trigger, stored procedure, or UDF; you can set the alignment and you have few other options. You can provide the options using the PARMS keyword, see the example above.

Also, to learn more about the options you may refer to the source code that contains the description of the parameters in comments, and includes the pseudocode of internal processing as well.

DSNTEP4

DSNTEP2 has a sibling - DSNTEP4. The usage is the same for both and what was written above for DSNTEP2 is valid for DSNTEP4 also. Well, obviously except the plan, which is DSNTP411 for DB2 11 and DSNTP410 for DB2 10 by default, and except the module names.

The only and main functional difference between DSNTEP2 and DSNTEP4 is that DSNTEP4 uses multi row fetch for better performance. However, due to the multi-row fetch, parallelism might be disabled. See IBM documentation for DSNTEP2 and DSNTEP4 for more details.

Conclusion

DSNTEP2 and DSNTEP4 are very handy if you need to execute dynamic SQL in batch. They support DDL, DML, as well as DCL statements. Of course, there are many vendor tools that do the same and provide more options, but the main benefit is that DSNTEP2 and DSNTEP4 are present even in a clean DB2 base installation. So at least it is good to know they exist.

As a final note, please be aware that the sample programs mentioned in this blog are not the only sample programs provided by IBM. There are more and they are worth looking at. Maybe we will meet with some of them in the next blog.