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
P rocess O ptions U tilities H elp
──────────────────────────────────────────────────────────────────────────────
FM/Db2 (DFF2) Basic SELECT Prototyping
Command ===>
Enter the name(s) of the table(s) from which to retrieve data:
Owner Name
1 FMN 0USR + Employee-Detail + Location
2 FMN 0USR + 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)
P rocess O ptions U tilities H elp
──────────────────────────────────────────────────────────────────────────────
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". There are several steps required to prototype this statement.
They are:
Select the following columns by typing 'S' next to the column
name: Press Enter.
See Basic SELECT Prototyping panel
(3)
Figure 3 . Basic SELECT Prototyping panel
(3)
P rocess O ptions U tilities H elp
──────────────────────────────────────────────────────────────────────────────
FM/Db2 (DFF2) Basic SELECT Prototyping Row 1 of 19
Command ===> Scroll CSR
SELECT "First Name", "Middle Initial", "Last Name", "Employee Number", "Dep +
FROM "FMN 0USR"."Employee-Detail", "FMN 0USR"."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 '#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 Basic SELECT Prototyping panel
(4) Figure 4 . Basic SELECT Prototyping panel
(4)
P rocess O ptions U tilities H elp
──────────────────────────────────────────────────────────────────────────────
FM/Db2 (DFF2) Basic SELECT Prototyping Row 1 of 19
Command ===> Scroll CSR
SELECT "First Name", "Middle Initial", "Last Name", "Employee Number", "Dep +
FROM "FMN 0USR"."Employee-Detail", "FMN 0USR"."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
Add a WHERE clause to show only the employees in the "A10" Department
by typing 'A10' in the Work Department column. Press Enter.
See Basic SELECT Prototyping panel (5) . Figure 5 . Basic SELECT Prototyping panel (5)
P rocess O ptions U tilities H elp
──────────────────────────────────────────────────────────────────────────────
FM/Db2 (DFF2) Basic SELECT Prototyping Row 1 of 19
Command ===> Scroll CSR
SELECT "First Name", "Middle Initial", "Last Name", "Employee Number", "Dep +
FROM "FMN 0USR"."Employee-Detail", "FMN 0USR"."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
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 6 . Select Statement Browse panel
P rocess O ptions U tilities H elp
──────────────────────────────────────────────────────────────────────────────
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.