IBM SPUFI

SPUFI (SQL Processor Using File Input) is a database facility invented by IBM for interfacing with their Db2 system. It is accessed from within TSO ISPF from the DB2I Primary Option menu.

SPUFI allows direct input of SQL commands in the TSO environment, rather than having them embedded within a program.

SPUFI defaults

Once set up the SPUFI defaults are unlikely to be changed. Their values are very similar across installations, a typical example is shown below.

                           CURRENT SPUFI DEFAULTS             SSID: DDBA
 ===> ____________________________________________________________________________

 Enter the following to control your SPUFI session:
  1  SQL TERMINATOR .. ===> ;          (SQL Statement Terminator)
  2  ISOLATION LEVEL   ===> CS         (RR=Repeatable Read, CS=Cursor Stability)
  3  MAX SELECT LINES  ===> 250        (Maximum number of lines to be
                                        returned from a SELECT)
 Output data set characteristics:
  4  RECORD LENGTH ... ===> 4092       (LRECL=Logical record length)
  5  BLOCK SIZE ...... ===> 4096       (Size of one block)
  6  RECORD FORMAT ... ===> VB         (RECFM=F, FB, FBA, V, VB, or VBA)
  7  DEVICE TYPE ..... ===> SYSDA      (Must be DASD unit name)

 Output format characteristics:
  8  MAX NUMERIC FIELD ===> 33         (Maximum width for numeric fields)
  9  MAX CHAR FIELD .. ===> 80         (Maximum width for character fields)
 10  COLUMN HEADING .. ===> NAMES      (NAMES, LABELS, ANY or BOTH)

Mode of use

Although it is essentially an interactive tool, SPUFI operates using a pair of datasets. (A dataset on z/OS is equivalent to a file on other operating systems.) In the main SPUFI screen one specifies an input dataset and an output dataset; these can be specified once and then reused repeatedly. When the user moves on from the main screen, the standard ISPF editor is opened on the input dataset. At this point the user can enter the required SQL statements using the familiar editor. On exiting from the editor the main SPUFI screen reappears; when the user moves on this time the contents of the input dataset are executed. The results are placed in the output dataset and the ISPF editor is opened (in read-only "browse" mode) on that output. This is how the user reads their results. Interactive use of SPUFI continues around these steps; in summary the cycle is:

... Main → edit → Main → view output → Main → edit → Main → view output → Main ...

Because SPUFI uses normal datasets for the commands and the output, it is possible to pre-populate the commands or operate on the output by accessing the datasets independently of the SPUFI tool. Using datasets also means that a possibly-complicated set of SQL commands will persist from session to session rather than being lost when the user exits the tool.

Example query

SQL command

A simple query with comments.

 -- Select specific fields from the EMPLOYEE table
 -- for staff in Department 01.

   SELECT FIRST_NAME, LAST_NAME, DATE_JOINED
     FROM EMPLOYEE
    WHERE DEPARTMENT = '01'
    ORDER BY LAST_NAME DESC, FIRST_NAME

Results set

Typical result from this type of query.

 -----------+-----------+-----------+-----------+
 FIRST_NAME LAST_NAME   DATE_JOINED
 -----------+-----------+-----------+-----------+
 Joe        Briggs       2001-10-01
 Bob        Brown        2002-05-06
 Fred       Brown        2000-01-03
 -----------+-----------+-----------+-----------+
 NUMBER OF ROWS AFFECTED IS 3
 STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
 -----------+-----------+-----------+-----------+

The Output dataset contains the resultant rows(in case of SELECT) along with additional details like number of rows affected by the SQL query, SQLCODE returned on execution of the SQL query. In case of any SQL errors the details about the error will be given.

References

DB2 Developer's Guide, Craig S Mullins, Sams Publishing, ISBN 0-672-31168-2