Using Spufi to Execute Your SQL

You are on MVS (iSeries) TSO/ISPF and you want to run some structured query language to access data that is on DB2.

Create a PDS to hold your SQL (if not already done).    (Library is the English word used for a PDS. IBM uses the word "library" for something else. Allocate a PDS)
Go to ISPF option 3.2

------------------------- ISPF/PDF PRIMARY OPTION MENU 

OPTION  ===> 3.2
                                                            USERID 
   0  ISPF PARMS  - Specify terminal and user parameters    TIME   
   1  BROWSE      - Display source data or output listings  TERMINA
   2  EDIT        - Create or change source data            PREFIX 
   3  UTILITIES   - Perform utility functions               DATE   
   4  FOREGROUND  - Invoke foreground language processors   JULIAN 
   5  BATCH       - Submit job for language processing
   6  COMMAND     - Enter TSO command or CLIST
   7  DIALOG TEST - Perform dialog testing
   C  CHANGES     - Display summary of changes for this release
   T  TUTORIAL    - Display information about ISPF/PDF
   X  EXIT        - Terminate ISPF using log and list defaults

Enter END command to terminate ISPF.



-------------------------  DATA SET UTILITY  
OPTION  ===> M

   A - Allocate new data set                 C - Catalog data set
   R - Rename entire data set                U - Uncatalog data set
   D - Delete entire data set                S - Data set information (short)
   blank - Data set information              M - Enhanced data set allocation

ISPF LIBRARY:
   PROJECT ===>
   GROUP   ===>
   TYPE    ===>

OTHER PARTITIONED OR SEQUENTIAL DATA SET:
   DATA SET NAME  ===> ‘userid.DB2.SQL’
   VOLUME SERIAL  ===>          (If not cataloged, required for option "C")

DATA SET PASSWORD ===>          (If password protected)

					PRESS ENTER

						Record length of 80 is needed
Record format of FB
Make it a PDS (Partitioned Data Set)

-------------------------  ALLOCATE NEW DATA SET  
COMMAND ===>

DATA SET NAME: Userid.DB2.SQL

  MANAGEMENT CLASS     ===>             (Blank for default
  STORAGE CLASS        ===>             (Blank for default
    VOLUME SERIAL      ===>             (Blank for authorized default
  DATA CLASS           ===>             (Blank for default
    SPACE UNITS        ===> TRKS        (BLKS, TRKS, CYLS,
    PRIMARY QUANTITY   ===> 5           (In above units)
    SECONDARY QUANTITY ===> 5           (In above units)
    DIRECTORY BLOCKS   ===> 10          (Zero for sequential
    RECORD FORMAT      ===> FB
    RECORD LENGTH      ===> 80
    BLOCK SIZE         ===>
    DATA SET NAME TYPE ===> PDS         (LIBRARY, PDS, or blank)        
    EXPIRATION DATE    ===>             (YY/MM/DD, YYYY/MM/DD
                                         YY.DDD, YYYY.DDD in Julian
                                         DDDD for retention period 
                                         or blank)
    (* Specifying LIBRARY may override zero directory block)
				PRESS ENTER

Get into SPUFI
Different companies use different numbers for menu choices. Find out yours.
Set up your screen like this

SPUFI

Enter the input data set name:        (Can be sequential 
 1  DATA SET NAME ... ===> ‘userid.DB2.SQL(TEST1)’ <- new or existing member name
 2  VOLUME SERIAL ... ===>            (Enter if not cataloged)
 3  DATA SET PASSWORD ===>            (Enter if password protected)

Enter the output data set name:       (Must be a sequential
 4  DATA SET NAME ... ===> ‘userid.SPUFI.LIST’
Note that SPUFI.LIST can be any name, and it does not have to be preallocated

Specify processing options:
 5  CHANGE DEFAULTS   ===> NO         (Y/N - Display SPUFI defaults
 6  EDIT INPUT ...... ===> YES        (Y/N - Enter SQL statements?)
 7  EXECUTE ......... ===> YES        (Y/N - Execute SQL statements?)
 8  AUTOCOMMIT ...... ===> YES        (Y/N - Commit after successful
 9  BROWSE OUTPUT ... ===> YES        (Y/N - Browse output data set?)

For remote SQL processing:
10  CONNECT LOCATION  ===>

PRESS:  ENTER to process    END to exit       HELP for more information
 
				PRESS ENTER

This takes you to the ISPF editor.  
Type these commands on the command line, to set your preferences.
Experience has shown that these commands can’t get you into trouble. 

Then type in your SQL in the large completely blank area.


EDIT ---- Userid.DB2.SQL(TEST1) - 01.00 --------------------- COLUMNS 001 072
COMMAND ===> recovery on;number off;nulls all;reset           SCROLL ===> CSR
****** ***************************** TOP OF DATA 
...... SELECT *
......   FROM STAFF
......
......
......
......
......

****** **************************** BOTTOM OF DATA 


										PRESS PF3


SPUFI

Enter the input data set name:        (Can be sequential 
 1  DATA SET NAME ... ===> ‘userid.DB2.SQL(TEST1)’ <- new or existing member name
 2  VOLUME SERIAL ... ===>            (Enter if not cataloged)
 3  DATA SET PASSWORD ===>            (Enter if password protected)

Enter the output data set name:       (Must be a sequential
 4  DATA SET NAME ... ===> ‘userid.SPUFI.LIST’
Note that SPUFI.LIST can be any name, and it does not have to be preallocated

Specify processing options:
 5  CHANGE DEFAULTS   ===> NO       (Y/N - Display SPUFI defaults
 6  EDIT INPUT ...... ===> *        (Y/N - Enter SQL statements?)
	the asterisk means that you have already performed this action
 7  EXECUTE ......... ===> YES        (Y/N - Execute SQL statements?)
 8  AUTOCOMMIT ...... ===> YES        (Y/N - Commit after successful
 9  BROWSE OUTPUT ... ===> YES        (Y/N - Browse output data set?)

For remote SQL processing:
10  CONNECT LOCATION  ===>

PRESS:  ENTER to process    END to exit       HELP for more information
 
				PRESS ENTER. There is no need to type anything on this screen

BROWSE -- Userid.SPUFI.LIST ------- LINE 00000000 COL 001 080
COMMAND ===>                              SCROLL ===> PAGE
SELECT * 
FROM STAFF
--------+---------+---------+---------+---------+---------+--
   ID  NAME         DEPT  JOB     YEARS     SALARY       COMM
--------+---------+---------+---------+---------+---------+--

    10  SANDERS        20  MGR         7   18357.50  ---------
    20  PERNAL         20  SALES       8   18171.25     612.45
    30  MARENGHI       38  MGR         5   17506.75  ---------
    40  O'BRIEN        38  SALES       6   18006.00     846.55
    50  HANES          15  MGR        10   20659.80  ---------
    60  QUIGLEY        38  SALES  ------   16808.30     650.25
    70  ROTHMAN        15  SALES       7   16502.83    1152.00
    80  JAMES          20  CLERK  ------   13504.60     128.20
    90  KOONITZ        42  SALES       6   18001.75    1386.70
   More...
 
										PRESS PF3