Step 5. Use the FM/Db2 Basic SELECT Prototyper

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

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

 Enter the name(s) of the table(s) from which to retrieve data:
     Owner           Name
   1 FMN0USR       + Employee-Detail                 + Location                 
   2 FMN0USR       + 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 Basic SELECT Prototyping panel (2).

Figure 2. Basic SELECT Prototyping panel (2)
  Process   Options   Utilities   Help
 ──────────────────────────────────────────────────────────────────────────────
 FM/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". Complete the following steps to prototype this statement:

  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.

    Select the following additional columns:
    • Employee Number
    • Department Code
    Press Enter. The additional columns are added to the SELECT clause, after the columns selected previously.

    See the result in Basic SELECT Prototyping panel (3).

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

 SELECT "First Name", "Middle Initial", "Last Name", "Employee Number", "Dep  +
   FROM "FMN0USR"."Employee-Detail", "FMN0USR"."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 T2."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. The WHERE clause is updated to include "Work Department" = T2."Department-Code". See Basic SELECT Prototyping panel (4).
    Figure 4. Basic SELECT Prototyping panel (4)
      Process   Options   Utilities   Help
 ──────────────────────────────────────────────────────────────────────────────
 FM/Db2 (DFF2)              Basic SELECT Prototyping                Row 1 of 19
 Command ===>                                                       Scroll CSR 

 SELECT "First Name", "Middle Initial", "Last Name", "Employee Number", "Dep  +
   FROM "FMN0USR"."Employee-Detail", "FMN0USR"."Department-Information"
   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)                                       
           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. See Basic SELECT Prototyping panel (5).
    Figure 5. Basic SELECT Prototyping panel (5)
      Process   Options   Utilities   Help
 ──────────────────────────────────────────────────────────────────────────────
 FM/Db2 (DFF2)              Basic SELECT Prototyping                Row 1 of 19
 Command ===>                                                       Scroll CSR 

 SELECT "First Name", "Middle Initial", "Last Name", "Employee Number", "Dep  +
   FROM "FMN0USR"."Employee-Detail", "FMN0USR"."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. Press Enter to update the WHERE clause to include only the employees in the "A10" Department. See Basic SELECT Prototyping panel (6)
    Figure 6. Basic SELECT Prototyping panel (6)
      Process   Options   Utilities   Help
 ──────────────────────────────────────────────────────────────────────────────
 FM/Db2 (DFF2)              Basic SELECT Prototyping                Row 1 of 19
 Command ===>                                                       Scroll CSR 

 SELECT "First Name", "Middle Initial", "Last Name", "Employee Number", "Dep  +
   FROM "FMN0USR"."Employee-Detail", "FMN0USR"."Department-Information" T2
   WHERE "Work Department" = T2."Department-Code" AND "Work Department" = A10
   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
  5. Either type EXECUTE on the command line, or press the PF6 key to run the SQL statement and display the result table. See Select Statement Browse panel.
    Figure 7. Select Statement Browse panel
      Process   Options   Utilities   Help
 ──────────────────────────────────────────────────────────────────────────────
 FM/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 FM/Db2 main menu.