Step 5. Use the ZDT/Db2 Basic SELECT Prototyper

Log on to ZDT/Db2, and connect to the same Db2® system specified in Step 1. Define Db2 objects to be used during verification. From the ZDT/Db2 main menu enter 4.1 to display the "Basic SELECT Prototyping" function.

Enter the names of the two ZDT/Db2 IVP tables as shown in Figure 1.
Figure 1. Basic SELECT Prototyping panel
  Process   Options   Utilities   Help
 ──────────────────────────────────────────────────────────────────────────────
 ZDT/Db2 (DFF2)              Basic SELECT Prototyping
 Command ===>                                                                  

 Enter the name(s) of the table(s) from which to retrieve data:
     Owner           Name
   1 HFM0USR       + Employee-Detail                 + Location                 
   2 HFM0USR       + Department-Information          + Database         
   3               +                                 + Tbl spc.         
   4               +                                 +
   5               +                                 +
   6               +                                 +
   7               +                                 +
   8               +                                 +
   9               +                                 +
  10               +                                 +
  11               +                                 +
  12               +                                 +
  13               +                                 +
  14               +                                 +
  15               +                                 +
  F1=Help      F2=Split     F3=Exit      F4=Expand    F7=Backward  F8=Forward
  F9=Swap     F10=Left     F11=Right    F12=Cancel

Press Enter to display the second "Basic SELECT Prototyping" panel. See Figure 2.

Figure 2. Basic SELECT Prototyping panel (2)
  Process   Options   Utilities   Help
 ──────────────────────────────────────────────────────────────────────────────
 ZDT/Db2 (DFF2)              Basic SELECT Prototyping                Row 1 of 19
 Command ===>                                                       Scroll CSR 

 SELECT ?
   FROM ?
   WHERE ?
   ORDER BY ?

 Row count  100            Number of rows to display

 Select columns (S/A/D) or enter predicates to build the SELECT statement:

 S  LOp ( Tab Column Name        Data Type(length)    Op Value                )
           T1 Employee Number    CHAR(6)                                       
           T1 First Name         VARCHAR(12)                                   
           T1 Middle Initial     CHAR(1)                                       
           T1 Last Name          VARCHAR(15)                                   
           T1 Work Department    CHAR(3)                                       
           T1 Telephone Number   CHAR(4)                                       
           T1 Commencement Date  DATE                                          
  F1=Help      F2=Split     F3=Exit      F4=Expand    F6=Execute   F7=Backward
  F8=Forward   F9=Swap     F10=Left     F11=Right    F12=Cancel

In this example, an SQL query is developed that shows the Employees (Names and Employee number only), and the Department name; for Department code = "A10". There are several steps required to prototype this statement. They are:

  1. Select the following columns by typing 'S' next to the column name:
    • First name
    • Middle Initial
    • Last Name

      Press Enter.

      The selected columns are added to the SELECT clause, which is displayed at the top of the panel.

    • Employee Number
    • Department Code

    Press Enter.

    See Figure 3

    Figure 3. Basic SELECT Prototyping panel (3)
      Process   Options   Utilities   Help
     ──────────────────────────────────────────────────────────────────────────────
     ZDT/Db2 (DFF2)              Basic SELECT Prototyping                Row 1 of 19
     Command ===>                                                       Scroll CSR 
    
     SELECT "First Name", "Middle Initial", "Last Name", "Employee Number", "Dep  +
       FROM "HFM0USR"."Employee-Detail", "HFM0USR"."Department-Information"
       WHERE ?
       ORDER BY ?
    
     Row count  100            Number of rows to display
    
     Select columns (S/A/D) or enter predicates to build the SELECT statement:
    
     S  LOp ( Tab Column Name        Data Type(length)    Op Value                )
               T1 Employee Number    CHAR(6)                                       
               T1 First Name         VARCHAR(12)                                   
               T1 Middle Initial     CHAR(1)                                       
               T1 Last Name          VARCHAR(15)                                   
               T1 Work Department    CHAR(3)                                       
               T1 Telephone Number   CHAR(4)                                       
               T1 Commencement Date  DATE                                          
      F1=Help      F2=Split     F3=Exit      F4=Expand    F6=Execute   F7=Backward
      F8=Forward   F9=Swap     F10=Left     F11=Right    F12=Cancel
  2. Specify the join between the two tables. To do this, type '#2."Department-Code"' in the "Value" column next to the entry for "Work Department" in the "Employee-Detail" table. You should also specify an '=' in the "Op" column. Press Enter. See Figure 4
    Figure 4. Basic SELECT Prototyping panel (4)
      Process   Options   Utilities   Help
     ──────────────────────────────────────────────────────────────────────────────
     ZDT/Db2 (DFF2)              Basic SELECT Prototyping                Row 1 of 19
     Command ===>                                                       Scroll CSR 
    
     SELECT "First Name", "Middle Initial", "Last Name", "Employee Number", "Dep  +
       FROM "HFM0USR"."Employee-Detail", "HFM0USR"."Department-Information"
       WHERE "Work Department" = '#2."Department-Code"'
       ORDER BY ?
    
     Row count  100            Number of rows to display
    
     Select columns (S/A/D) or enter predicates to build the SELECT statement:
    
     S  LOp ( Tab Column Name        Data Type(length)    Op Value                )
               T1 Employee Number    CHAR(6)                                       
               T1 First Name         VARCHAR(12)                                   
               T1 Middle Initial     CHAR(1)                                       
               T1 Last Name          VARCHAR(15)                                   
               T1 Work Department    CHAR(3)                                       
               T1 Telephone Number   CHAR(4)                                       
               T1 Commencement Date  DATE                                          
      F1=Help      F2=Split     F3=Exit      F4=Expand    F6=Execute   F7=Backward
      F8=Forward   F9=Swap     F10=Left     F11=Right    F12=Cancel
  3. Add a WHERE clause to show only the employees in the "A10" Department by typing 'A10' in the "Work Department" column. Press Enter. See Figure 5.
    Figure 5. Basic SELECT Prototyping panel (5)
      Process   Options   Utilities   Help
     ──────────────────────────────────────────────────────────────────────────────
     ZDT/Db2 (DFF2)              Basic SELECT Prototyping                Row 1 of 19
     Command ===>                                                       Scroll CSR 
    
     SELECT "First Name", "Middle Initial", "Last Name", "Employee Number", "Dep  +
       FROM "HFM0USR"."Employee-Detail", "HFM0USR"."Department-Information" T2
       WHERE "Work Department" = T2."Department Code"
       ORDER BY ?
    
     Row count  100            Number of rows to display
    
     Select columns (S/A/D) or enter predicates to build the SELECT statement:
    
     S  LOp ( Tab Column Name        Data Type(length)    Op Value                )
               T1 Employee Number    CHAR(6)                                       
               T1 First Name         VARCHAR(12)                                   
               T1 Middle Initial     CHAR(1)                                       
               T1 Last Name          VARCHAR(15)                                   
               T1 Work Department    CHAR(3)              =  A10                   
               T1 Telephone Number   CHAR(4)                                       
               T1 Commencement Date  DATE                                          
      F1=Help      F2=Split     F3=Exit      F4=Expand    F6=Execute   F7=Backward
      F8=Forward   F9=Swap     F10=Left     F11=Right    F12=Cancel
  4. Either type EXECUTE on the command line, or press the PF6 key to run the SQL statement and display the result table. See Figure 6.
    Figure 6. Select Statement Browse panel
      Process   Options   Utilities   Help
     ──────────────────────────────────────────────────────────────────────────────
     ZDT/Db2 (DFF2)              Select Statement Browse                    Top of 6
     Command ===>                                                       Scroll CSR 
     6 rows - End of object.                                            Format TABL
     First Name   Middle Initial Last Name       Employee Number Department Code
     #2           #3             #4              #1              #5
     VARCHAR(12)  CHARACTER(1)   VARCHAR(15)     CHARACTER(6)    CHARACTER(3)
     <---+----1-> -              <---+----1----> PU--+>          <->
     ****  Top of data  ****
     Charles<     A              Abercrombie<    000070          A10
     Kyle<        B              Giddens<        000230          A10
     Vincent<     E              Gomez<          000240          A10
     Larry<       M              Kuntz<          000250          A10
     Kathleen<    L              Miller<         000260          A10
     Diane<                      Konyn<          000270          A10
     ****  End of data  ****
    
    
    
    
    
      F1=Help      F2=Zoom      F3=Exit      F4=CRetriev  F5=RFind     F6=RChange
      F7=Up        F8=Down      F9=Swap     F10=Left     F11=Right    F12=Cancel

This completes the Basic SQL Prototyping part of the IVP. Press PF3 repeatedly to return to the ZDT/Db2 main menu.