[USflag] The American Programmer [USflag]


Home
Books on Mainframe Programming
Mainframe Manuals and Tutorials
System Abend codes, Sqlcodes, VSAM/QSAM codes
Everything about the IBM AS/400 Midrange Computer
Everything about CICS
Everything about COBOL
Everything about DB2 and SQL
  DB2/SQL Singleton Select embedded in a COBOL program
  DB2/SQL Add Change Delete program
  DB2/SQL program to read table with cursor
  DB2/SQL load table program
  DB2/SQL program to read a table randomly
  DB2/SQL bare bones skeleton program
  DB2/SQL program to insert a row into a table
  DB2/SQL program to update a row
  DB2/SQL program for handling variable length column
  DB2/SQL program for handling column with null indicator
  Embedded SQL. Short tutorial.
  Examples of DCLGEN. Short tutorial.
  Examples of SQL Joins. Short tutorial.
  Examples of DB2 View. Short tutorial.
  Just Enough SQL/QMF/SPUFI To Be Dangerous
  Sample tables STAFF, ORG, EMP and DEPT
  QMF Procedures Written in REXX
  Using REXX Subroutines with the QMF Calc panel
  Using QMF to Execute Your SQL
  QMF Cheat Sheet. Edit codes. QMF Commands, Text Variables, Usage Codes
  Executing SQL in a batch job
  Using Spufi to Execute Your SQL
  Books on DB2 and SQL
  DB2 and SQL, Structured Query Language manuals
  SQLCODES and Their Causes
Everything about IMS
Everything about Java and JavaScript
Everything about JCL and JES
Everything about REXX
Everything about zOS, VSAM, Tivoli, Assembler
Everything about TSO, ISPF, Spufi
Site Map and Site Search

           Home   > DB2   > Using Spufi to Execute Your SQL

Using Spufi to Execute Your SQL

You are on MVS 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

[Books Computer]

Home Books for Computer Professionals Privacy Terms |
Site Map and Site Search Programming Manuals and Tutorials The REXX Files Top of Page |