Using basic SQL prototyping
Basic SELECT prototyping uses two panels: you use the primary panel to specify the table (or tables) that contain the data you want to retrieve (FROM clause), and the secondary panel to specify the columns you want to show, any qualifying information (WHERE clause), and the sequence in which you want the data to be shown (ORDER BY clause).
To display the primary Basic SELECT Prototyping panel, select option 1 (Basic) on the SQL Prototyping, Execution and Analysis panel.
Process Options Utilities Help
──────────────────────────────────────────────────────────────────────────────
FM/Db2 (DFG2) Basic SELECT Prototyping
Enter the name(s) of the table(s) from which to retrieve data:
Owner Name
1 DSN81010 + EMP + Location
2 DSN81010 + DEPT + Database
3 + + Tbl spc.
4 + +
5 + +
6 + +
7 + +
8 + +
9 + +
10 + +
11 + +
12 + +
13 + +
14 + +
15 + +
Command ===>
F1=Help F2=Split F3=Exit F9=Swap F12=Cancel
On this panel you can specify up to 15 tables, views, synonyms, or aliases that can be part of the SELECT statement being prototyped. If you specify more than one object, the columns for all objects specified are shown on the secondary Basic SELECT Prototyping panel when you press Enter; see Secondary Basic SELECT Prototyping panel.
You cannot modify the entry-line numbers at the left of the panel (1, 2, and so on). The purpose of the entry-line number is to provide a suffix for the correlation name used to identify the Db2® object. Db2® correlation names are discussed in detail in the SQL Reference. The complete Db2® correlation name is shown in the "Tab" column on the secondary Basic SELECT Prototyping panel when you press Enter. The complete correlation name will be a capital letter, followed by the entry-line number. The capital letter will usually be 'T', but may be another letter if any of the Db2® object names start with 'T'. The Basic Prototyper ensures that the letter selected for the correlation name reference is not the same as the first letter of any Db2® object name included on the primary Basic SELECT Prototyping panel.
The columns and rows included in the result table are determined by the SELECT and WHERE clauses specified on the secondary Basic SELECT Prototyping panel, which is displayed when you specify the objects and press Enter. The examples in this section use the tables DSN81010.EMP and DSN81010.DEPT.
Tables and views are specified by optionally entering values in the Location, Database, Tbl spc. (table space), and Owner fields, and entering values in the Name fields. Synonyms are specified by entering values in the Name fields. If a value is entered in the Owner field for a synonym, it must be the current SQLID. All of the fields support catalog searches by specifying a pattern in the field. After you have specified the table names on the primary Basic SELECT Prototyping panel, press Enter to display the secondary Basic SELECT Prototyping panel.
Process Options Utilities Help
────────────────────────────────────────────────────────────────────────────────
FM/Db2 (DFG2) Basic SELECT Prototyping Row 1 of 19
SELECT ?
FROM ?
WHERE ?
ORDER BY ?
Row count 0 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 EMPNO CHAR(6)
T1 FIRSTNME VARCHAR(12)
T1 MIDINIT CHAR(1)
T1 LASTNAME VARCHAR(15)
T1 WORKDEPT CHAR(3)
T1 PHONENO CHAR(4)
Command ===> Scroll PAGE
F1=Help F2=Split F3=Exit F6=Execute F7=Backward F8=Forward
F9=Swap F12=Cancel
When initially displayed, the secondary Basic SELECT Prototyping
panel shows all of the columns for the Db2® objects
entered on the primary panel. You can scroll up or down if there
are more columns than will fit on the display. The SQL statement
being prototyped is shown at the top of the panel as 4 clauses (SELECT,
FROM, WHERE and ORDER BY). An empty clause is indicated by a ?
.
FM/Db2 automatically updates one or more of these clauses as you
enter either primary or line commands. Each of the clause fields
is an ISPF expandable field. If the field is too short to display
all of the clause then a +
will be shown at the end of the
field. You can position the cursor anywhere in the field and press
the ISPF EXPAND key to show the complete clause in a pop-up window.
To limit the number of rows returned in the result set when the SQL statement is executed, specify the maximum number of rows in the Row count field.
The following primary commands are available when you use basic SELECT prototyping:
- ALL
- Use this command to select all columns for a Db2® object. You can specify an optional parameter #n to include only the columns of the nth Db2® object displayed.
- CANCEL
- Use this command to return to the primary Basic SELECT Prototyping panel
- EXECUTE
- Use this command to run the SQL statement being prototyped
- RESET
- Use this command to abandon all changes to the SQL statement. All clauses are returned to empty status.
- SQL
- Use this command to show the complete SQL statement in an ISPF editor session
- UNDO
- Use this command to back-out the last change made.
The following line commands can be entered against any column:
- S or /
You type either S or / against the entry for a column to add that column to the list of columns for the SQL statement. Data from the column will be included in the result table when the statement is executed.
You can select multiple columns at once, however your entries are processed from top to bottom, so you might need to select columns individually if the order of columns in the result table is important.
- A and D
You type either A or D against the entry for a column to add an ORDER BY clause for that column. If the specified column does not exist in the column list, the column is also added to the column list. Data from the column will be included in the result table when the statement is executed, and rows will by sorted in ascending or descending order on that data.
Entering a A results in the ORDER BY clause including the column name without modification, this defaults to ascending (ASC keyword).
Entering a D results in the ORDER BY clause including the column name followed by the DESC keyword, this indicates the result table should be sorted in descending order based on the data for the column.
- U
You type U against the entry for a column to remove the last column reference from the list of columns in the result table. When the column is removed and the column is no longer specified in the list of columns:
- All ORDER BY clause references to the column are also removed.
- All predicate references to the column are also removed.
If the column has been added to the column list multiple times, use the U command repeatedly to remove all references.
- UO
You type UO against the entry for a column to remove the last ORDER BY reference.
Removing an ORDER BY reference for the column does not remove the column from the column list, nor does it remove any predicates referencing the column from the WHERE clause.
If the column has been added to the ORDER BY clause multiple times, use the UO command repeatedly to remove all references.
- UW
You type UW against the entry for a column to remove the last predicate that references the column.
Removing a WHERE clause reference for the column does not remove the column from the column list, nor does it remove any references for the column from the ORDER BY clause.
If there are multiple predicates containing the column, use the UW command repeatedly to remove all references.