DB2 - SPUFI Option

The first option in the DB2I main menu is SPUFI (SQL Processor Using File Input).

SPUFI is intended primarily for application programmers who wish to test the SQL portions of their programs or administrators who wish to perform SQL operations.

SPUFI reads SQL statements contained as text in a sequential file or in a member of a PDS, processes those statements and places the results in an ISPF browse session. By specifying the input and output data sets and selecting the appropriate options, we can execute SQL statements in an online mode.

The SPUFI Panel is as follows.

You need to enter Input data set name and Output data set name.

Input Data Set:

Input dataset must be allocated before invoking SPUFI. This can be a member of Partitioned Data Set or a Sequential Data Set. This can be empty and can be edited as part of the SPUFI session.

It is recommended to maintain a partitioned data set to keep track of SQL statements used. Input dataset can be defined as a fixed, blocked data set with an LRECL of 80.

SQL statements can be written in all but the last 8 bytes of each input record; this area is reserved for sequence numbers. This can contain multiple SQL statements, as long as they are separated by semicolons. Comments are preceded by two hyphens. If the input file contains multiple SQL statements, SPUFI will stop execution of those statements as soon as it encounters an error in any one of them.

Sample SQL statements in the input data set as follows:

--DELETE FROM THYD001.TEST
--WHERE TEST_NO = 'A114';
--
SELECT * FROM THYD001.TEST;
--

Output Data Set:

The output data set need not be allocated before using SPUFI. If the output data set does not exist, SPUFI creates a virtual, blocked sequential data set with an LRECL of 4092.

The output file will contain a sequence of results, one for each statement (including the relevant SQLCODE), followed by a summary of the overall execution (including, in particular, an indication as to which of Commit and Roll Back occurred). There are some specific defaults to be set for Output data set.

When the SQL is executed and browsed, an output data set like the following appears:

Other Options:

After entering Input data set name and Output data set name, we need to specify following options.

CHANGE DEFAULTS: When Y is specified, the SPUFI defaults panel appears as follows:

Options of “CURRENT SPUFI DEFAULTS”:

1. Typically, defaults are changed only once—the first time someone uses SPUFI. ISPF saves the defaults entered from session to session.

2. Be sure to specify the following defaults:

   Isolation Level: Always set this option to CS (Cursor Stability).

   (Note: You will study about Isolation Level in detail in your forthcoming sessions.)

   Max Select Lines: Set to an appropriate number. If we will be selecting from large tables that return more than 250 rows, the installation default value of 250 is insufficient. SPUFI stops returning rows after reaching the specified limit, and it issues a message indicating so.

3. All the remaining installation defaults are appropriate. So keep them as they are.

4. EXECUTE: When Y is specified, the SQL in the input file is read and executed.

5. AUTOCOMMIT: When Y is specified, a COMMIT is issued automatically after the successful execution of the SQL in the input file.

   When you specify N, SPUFI prompts you about whether a COMMIT should be issued. If the COMMIT is not issued, all changes are rolled back.

   Note: You will study about commit and rollback in our forthcoming sessions.

6. BROWSE OUTPUT: When Y is specified, SPUFI places you in an ISPF browse session for the output data set. You can view the results of the SQL that was executed.

Note: Specifying Y for all these options (from 6 to 9) except Change Defaults (5) is common.

If you have any doubts or queries related to this chapter, get them clarified from our Mainframe experts on ibmmainframer Community!