- IBM Spufi
SQL Processing Using File Input is a
database facility invented byIBM for interfacing with their DB2 system. It is accessed from within TSOISPF 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.PUFI 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)
Example Query
QL 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 -----------+-----------+-----------+-----------+ 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 -----------+-----------+-----------+-----------+
References
DB2 Developer's Guide, Craig S Mullins, Sams Publishing, ISBN 0-672-31168-2
Wikimedia Foundation. 2010.
