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.
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).
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:
- Select the following columns by typing 'S' next to the column name:
- First Name
- Middle Initial
- Last Name
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
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
- 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
- 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
- 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
- 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.