Lesson checkpoint
In this lesson, you learned how to complete the following tasks interactively in SPUFI:
- Open SPUFI in ISPF
- Set default values in DB2I
- Issue SQL statements interactively and see the results
One of the most basic database tasks is to query the data that the database contains. Even if you are new to DB2® for z/OS®, you might already know how to query data in other databases. In fact, you can use many different methods and tools to access data that is stored in DB2 for z/OS databases, including tools that provide modern graphical user-interfaces. However, this lesson provides an opportunity for you learn how to query data by using the commands and menus of the panel-based SPUFI facility in the z/OS terminal interface.
Procedure
Menu Utilities Compilers Options Status Help
ISPF/PDF Primary Option Menu
Option ===> D
0 Settings Terminal and user parameters User ID . : TUTOR01
1 View Display source data or listings Time. . . : 11:10
2 Edit Create or change source data Terminal. : 3278A
3 Utilities Perform utility functions Screen. . : 1
4 Foreground Interactive language processing Language. : ENGLISH
5 Batch Submit job for language processing Appl ID . : ISR
6 Command Enter TSO or Workstation commands TSO logon : UPROC01
7 Dialog Test Perform dialog testing TSO prefix: TUTOR01
8 LM Facility Library administrator functions System ID : UTEC203
9 IBM Products IBM program development products MVS acct. : D1001
10 SCLM SW Configuration Library Manager Release . : ISPF 6.3
11 Workplace ISPF Object/Action Workplace
12 z/OS system z/OS system programmer applications
13 z/OS user z/OS user applications
D DB2/DXT/QMF Display DB2/DXT/QMF Selection Panel
G SWG/SVL Display SWG/SVL Selection Panel
F1=Help F2=Split F3=Exit F7=Backward F8=Forward F9=Swap
F10=Actions F12=Cancel
Depending on the customizations at your site, one of the following panels is displayed:------------------------- SITE SELECTION PANEL -----------------------------
OPTION ===> 1
TUTOR01
1 DB2 - Perform DATABASE 2 Interactive Functions
2 DVR110 ADMIN- Invoke DataRefresher V1 Administrative Dialogs
3 DVR110 EUSER- Invoke DataRefresher V1 End User Dialogs
4 QMF - QMF Query Management Facility
5 QMFTOOL - QMF Message Tool
6 DB2PM - DB2 Performance Monitor
Press END to return to ISPF/PDF Primary Option Menu.
The DB2I Defaults is
displayed. DB2 Interactive (also known as
"DB2I") is an interactive program that
runs under TSO and uses ISPF services. You can use DB2I to interact with DB2 by entering values in panels. DB2I PRIMARY OPTION MENU SSID: DSN
COMMAND ===> D
Select one of the following DB2 functions and press ENTER.
1 SPUFI (Process SQL statements)
2 DCLGEN (Generate SQL and source language declarations)
3 PROGRAM PREPARATION (Prepare a DB2 application program to run)
4 PRECOMPILE (Invoke DB2 precompiler)
5 BIND/REBIND/FREE (BIND, REBIND, or FREE plans or packages)
6 RUN (RUN an SQL program)
7 DB2 COMMANDS (Issue DB2 commands)
8 UTILITIES (Invoke DB2 utilities)
D DB2I DEFAULTS (Set global parameters)
X EXIT (Leave DB2I)
F1=HELP F2=SPLIT F3=END F4=RETURN F5=RFIND F6=RCHANGE
F7=UP F8=DOWN F9=SWAP F10=LEFT F11=RIGHT F12=RETRIEVE
DB2I DEFAULTS PANEL 1
COMMAND ===>
Change defaults as desired:
1 DB2 NAME ............. ===> DB2A (Subsystem identifier)
2 DB2 CONNECTION RETRIES ===> 0 (How many retries for DB2 connection)
3 APPLICATION LANGUAGE ===> IBMCOB (ASM, C, CPP, IBMCOB, FORTRAN, PLI)
4 LINES/PAGE OF LISTING ===> 60 (A number from 5 to 999)
5 MESSAGE LEVEL ........ ===> I (Information, Warning, Error, Severe)
6 SQL STRING DELIMITER ===> DEFAULT (DEFAULT, ' or ")
7 DECIMAL POINT ........ ===> . (. or ,)
8 STOP IF RETURN CODE >= ===> 8 (Lowest terminating return code)
9 NUMBER OF ROWS ....... ===> 20 (For ISPF Tables)
10 AS USER ===> (Userid to associate with the trusted
connection)
F1=HELP F2=SPLIT F3=END F4=RETURN F5=RFIND F6=RCHANGE
F7=UP F8=DOWN F9=SWAP F10=LEFT F11=RIGHT F12=RETRIEVE
DB2I PRIMARY OPTION MENU SSID: DB2A
COMMAND ===>
...
SPUFI SSID: DB2A
===>
Enter the input data set name: (Can be sequential or partitioned)
1 DATA SET NAME ... ===> 'USER.SRCLIB.TUTORIAL(SQLTEXT)'
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 data set)
4 DATA SET NAME ... ===> MYOUTPUT
Specify processing options:
5 CHANGE DEFAULTS ===> NO (Y/N - Display SPUFI defaults panel?)
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 run?)
9 BROWSE OUTPUT ... ===> YES (Y/N - Browse output data set?)
For remote SQL processing:
10 CONNECT LOCATION ===>
You are now ready to edit the data set or member. File Edit Edit_Settings Menu Utilities Compilers Test Help
EDIT USER.SRCLIB.TUTORIAL(SQLTEXT) - 01.00 Columns 00001 00072
Command ===> Scroll ===> PAGE
****** ***************************** Top of Data ******************************
''''''
'''''' SELECT * FROM DSN8A10.EMP;
''''''
''''''
''''''
F1=Help F2=Split F3=Exit F5=Rfind F6=Rchange F7=Up
F8=Down F9=Swap F10=Left F11=Right F12=Cancel
. . . . . . . . . . . . . . . . . . . . . . . . . . .
Menu Help
SPUFI SSID: DB2A
===>
DSNE808A EDIT SESSION HAS COMPLETED. PRESS ENTER TO CONTINUE
BROWSE TUTOR01.MYOUTPUT Line 00000000 Col 001 080
Command ===> Scroll ===> PAGE
********************************* Top of Data **********************************
---------+---------+---------+---------+---------+---------+---------+---------+
SELECT * FROM DSN8A10.EMP 00010011
00020011
---------+---------+---------+---------+---------+---------+---------+---------+
EMPNO FIRSTNME MIDINIT LASTNAME WORKDEPT PHONENO HIREDATE J
---------+---------+---------+---------+---------+---------+---------+---------+
000010 CHRISTINE I HAAS A00 3978 1965-01-01 P
000020 MICHAEL L THOMPSON B01 3476 1973-10-10 M
000030 SALLY A KWAN C01 4738 1975-04-05 M
000050 JOHN B GEYER E01 6789 1949-08-17 M
000060 IRVING F STERN D11 6423 1973-09-14 M
000070 EVA D PULASKI D21 7831 1980-09-30 M
000090 EILEEN W HENDERSON E11 5498 1970-08-15 M
000100 THEODORE Q SPENSER E21 0972 1980-06-19 M
000110 VINCENZO G LUCCHESI A00 3490 1958-05-16 S
000120 SEAN O'CONNELL A00 2167 1963-12-05 C
000130 DOLORES M QUINTANA C01 4578 1971-07-28 A
F1=Help F2=Split F3=Exit F5=Rfind F7=Up F8=Down F9=Swap
F10=Left F11=Right F12=Cancel
Notice that the entire result set is not displayed in the panel. You can use the PFn keys that are listed at the bottom of the panel to scroll to see the rest of the data. For example, you can use PF8 to scroll down and PF11 to scroll right.
If you know that the data set already contains SQL statements that you want to run, you can skip the edit phase by changing the value of the 6 EDIT INPUT field to NO. When you press Enter, the statement runs immediately and the results are shown in a BROWSE panel. For more information about working with SPUFI, see Executing SQL by using SPUFI.
File Edit Edit_Settings Menu Utilities Compilers Test Help
EDIT USER.SRCLIB.TUTORIAL(SQLTEXT) - 01.12 Columns 00001 00072
Command ===> Scroll ===> PAGE
****** ***************************** Top of Data ******************************
000100 --SELECT * FROM DSN8A10.EMP;
000200
'''''' SELECT EMPNO, SALARY + COMM AS TOTAL_SAL
'''''' FROM DSN8A10.EMP
'''''' ORDER BY TOTAL_SAL;
''''''
''''''
****** **************************** Bottom of Data ****************************
Menu Utilities Compilers Help
BROWSE TUTOR01.MYOUTPUT Line 00000000 Col 001 080
Command ===> Scroll ===> PAGE
********************************* Top of Data **********************************
---------+---------+---------+---------+---------+---------+---------+---------+
--SELECT * FROM DSN8B10.EMP; 00010012
00020011
SELECT EMPNO, SALARY + COMM AS TOTAL_SAL 00030012
FROM DSN8A10.EMP 00040013
ORDER BY TOTAL_SAL; 00050012
---------+---------+---------+---------+---------+---------+---------+---------+
EMPNO TOTAL_SAL
---------+---------+---------+---------+---------+---------+---------+---------+
000290 16567.00
000310 17172.00
200310 17172.00
000260 18630.00
000300 19170.00
000210 19732.00
000250 20714.00
000320 21546.00
F1=Help F2=Split F3=Exit F5=Rfind F7=Up F8=Down F9=Swap
F10=Left F11=Right F12=Cancel
As before, the entire result set is not displayed on the panel. You can use the PF keys that are listed at the bottom of the screen to scroll to see the rest of the data.
Press PF3 to return to the Edit panel.
In this lesson, you learned how to complete the following tasks interactively in SPUFI: