Spufi

From Wikipedia, the free encyclopedia

SQL Processing 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.

1 SPUFI defaults
2 Example Query
- 2.1 SQL command
- 2.2 Results set
3 References

[edit] 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)

[edit] Example Query

[edit] 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

[edit] Results set

Typical result from this type of query.

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