Lesson 1.1: Querying data interactively

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

1. From the ISPF/PDF Primary Option Menu, type the option from the menu that includes Db2, and press Enter.
   Your site probably uses a customized version of this menu. In the following example, option D is selected.

      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:

   • The DB2I Primary Option Menu
   • One or more customized selection panels
2. If any customized selection panels are displayed, select the options for Db2, and press Enter.
   For example, in the following panel, and Db2 is option 1.

   ------------------------- 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.
3. Select the Db2 subsystem.
   1. From the DB2I Primary Option Menu, type option D in the COMMAND field and press Enter to display the DB2I Defaults.

                                 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 

   2. In the DB2I Defaults panel, type the name of the Db2 subsystem that contains the sample tables in the DB2 NAME field, and press Enter.
      In the following example, the DB2A subsystem name is specified.

                                 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  

   3. Press Enter again until you return to the DB2I Primary Option Menu.
   The SSID field contains the subsystem name that you specified. In the following example, shows that the DB2A subsystem is identified at the top of the panel.

                               DB2I PRIMARY OPTION MENU          SSID: DB2A 
   COMMAND ===>                                                             
   ...

4. From the DB2I Primary Option Menu, type option 1 in the COMMAND field and press Enter to open SPUFI.
5. In SPUFI, enter the following values:

   1 DATA SET NAME

   Specify the name of the data set and member to store the SQL statement that you will create in the next step. If the member does not already exist, Db2 creates it for you. In the following example, a partitioned data set named USER.SRCLIB.TUTORIAL is used, and the member name SQLTEXT is specified.

   4 DATA SET NAME

   Specify a name for your output data set. Db2 creates the output data set for you when you run the SQL statement. The output data set is qualified by your authorization ID.

   5 CHANGE DEFAULTS

   Type NO to prevent the SPUFI Defaults panel from being displayed. You can change this option later if you want to change the default values.

   Fields 6 - 9

   Ensure that these fields contain YES.

                              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.
6. Press Enter to open the Edit panel; then, type a SELECT statement.
   Tip: The ISPF editor works somewhat differently than text editors on platforms other than z/OS. If you are not familiar with this editor, see the following information:

   • The ISPF editor (z/OS Basic Skills)
   • Editing a data set (TSO/E Primer)
   The following statement selects every row of the sample EMP table, which contains sample employee data. For more information about how to create a SELECT statement, see subselect. For more information about the format of the sample EMP table and that data that it contains, see Employee table (DSN8C10.EMP).

      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 DSN8C10.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           

7. When you finish typing the SELECT statement, press PF3 to redisplay the SPUFI panel.
   The SPUFI panel contains the following message:

                              SPUFI                              SSID: DB2A 
   ===>                                                                     
   DSNE808A EDIT SESSION HAS COMPLETED. PRESS ENTER TO CONTINUE          

8. Press Enter to issue the SQL statement.
   The Browse panel is displayed and shows the result of the SELECT statement.

    BROWSE    TUTOR01.MYOUTPUT                           Line 00000000 Col 001 080 
    Command ===>                                                  Scroll ===> PAGE 
   ********************************* Top of Data **********************************
   ---------+---------+---------+---------+---------+---------+---------+---------+
    SELECT * FROM DSN8C10.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.

   Tip: If SQL statements contain syntax errors or refer to object or column names that do not exist, the result might contain error messages or SQL codes. You can use the information in these messages and codes to troubleshoot the SQL statements. For more information about how to look up the meanings of messages and SQL codes, see Introduction to troubleshooting problems in Db2 for z/OS.
9. Press PF3 to redisplay the SPUFI panel, and press Enter.
   Tip: You can repeat steps 6 - 8 to issue successive SQL statements interactively from SPUFI.

   1. When you first press Enter from the SPUFI panel, the Edit panel is displayed. Add or revise your SQL statements.
   2. When you press PF3 to exit from the Edit panel, the SPUFI panel is redisplayed. The 6 EDIT INPUT field now contains an asterisk (*), which indicates that the statements in the data set are ready to be issued.
   3. Press Enter again to run the statements. The results are shown in a BROWSE panel.
   4. Press PF3 to exit the BROWSE panel. The SPUFI panel is redisplayed, and field 6 EDIT INPUT now contains YES. When you press Enter again, the Edit panel is displayed. You are now ready to edit the SQL statements again, as in step 10.a.

   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.

10. Comment out the original SELECT statement and type a new statement.
    1. Insert two hyphens (--) to comment at the beginning of the statement to comment it out.
    2. Type the new statement several lines below the original. The new statement in the following example returns the employee numbers and calculates the total salary of each employee.

     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 DSN8C10.EMP;                                          
     000200                                                                         
     ''''''     SELECT EMPNO, SALARY + COMM AS TOTAL_SAL                           
     ''''''       FROM DSN8C10.EMP                                                   
     ''''''       ORDER BY TOTAL_SAL;                                                
     ''''''                                                                         
     ''''''                                                                         
     ****** **************************** Bottom of Data ****************************

11. When you finish typing the new statement, press PF3 to return to SPUFI, and press Enter to issue the statement.
    The results are shown in a BROWSE panel.

       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 DSN8C10.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.