Building SQL SELECT, INSERT, UPDATE and DELETE prototypes
The prototyping facility in Db2 Admin Tool helps you to build dynamic SQL statements without requiring you know the exact syntax. Using prototyping, you can build SQL SELECT, INSERT, UPDATE and DELETE statements interactively.
Procedure
To build SQL SELECT, INSERT, UPDATE and DELETE prototypes:
Examples
- Example of building a SELECT statement
-
For this example, assume that you want to build a SELECT statement that returns the name and department number of all employees with a salary greater than $30,000.
- On the Tables, Views, and Aliases
(ADB223T) panel, use the SEL
line command to select the EMP table, and press
Enter:
ADB223T n ----------- DD1A Tables, Views, and Aliases -------- Row 1 to 7 of 7 Command ===> Scroll ===> PAGE Line commands: SEL - Select for SQL SELECT prototype T - Table DEL - DELETE prototype INS - INSERT prototype UPD - UPDATE prototype Select Name Schema T * * * ------- -----------------> -------- - _______ AA1122 OWNER1 T _______ AARVV1145600_ANDR OWNER1 T _______ EEMP DSN8810 T _______ EEPA DSN8810 T __SEL__ EMP DSN8810 T _______ EMPPROJACT DSN8810 T _______ EPROJ DSN8810 T _______ EPROJACT DSN8810 T _______ MAP_TBL DSN8810 T _______ NEWDEPT DSN8810 T _______ NEWPHONE DSN8810 T _______ PARTS DSN8810 T _______ PROJ DSN8810 T _______ PROJACT DSN8810 T _______ STAFF DSN881SA T _______ STAFFV1 DSN881SA V _______ TCONA DSN8810 T _______ TDSPTXT DSN8810 T _______ TESTSTUFF DSN881SA T _______ TOPTVAL DSN8810 T _______ VACT DSN8810 V _______ VASTRDE1 DSN8810 V _______ VASTRDE2 DSN8810 V _______ VCONA DSN8810 V _______ VDEPMG1 DSN8810 V _______ VDEPT DSN8810 V _______ VDSPTXT DSN8810 V _______ VEMP DSN8810 VThe Build SQL SELECT Prototype (ADB21TSE) panel is displayed with a partially built SQL statement at the top.
- Use the S line command to include columns in your SELECT statement and the
<oper><expr> line command to specify the salary range of
greater than 30,000, and press
Enter:
ADB21TSE --------- DD1A Build SQL SELECT Prototype -------------- Row 1 of 14 Command ===> Scroll ===> PAGE SELECT ? FROM DSN8810.EMP T FOR? WHERE ? ORDER BY ? GROUP BY ? Commands: EDIT RESET * DRAW QUOTE INS UPD DEL COUNT COUNT_BIG Line commands: S - Show SA - Show ASC SD - Show DESC AVG, COUNT, COUNT_BIG, MAX, MIN, STDDEV, SUM, VARIANCE - Aggregate functions <oper><expr>, OR <pred>, IN list, BETWEEN <expr>,<expr> - WHERE predicates ? - Show all line commands Select Column Name Col Type Length * * * ---------------------------------- ------------------ -------- ----------- EMPNO CHAR 6 S FIRSTNME VARCHAR 12 S MIDINIT CHAR 1 S LASTNAME VARCHAR 15 S WORKDEPT CHAR 3 PHONENO CHAR 4 HIREDATE DATE 10 JOB CHAR 8 EDLEVEL SMALLINT 2 SEX CHAR 1 BIRTHDATE DATE 10 >30000 SALARY DECIMAL 9 BONUS DECIMAL 9 COMM DECIMAL 9 ******************************* END OF DB2 DATA ******************************
The SELECT statement is updated with these changes. - Use the SD line command to add the ORDER BY clause to the SELECT statement, and press
Enter:
ADB21TSE --------- DD1A Build SQL SELECT Prototype -------------- Row 1 of 14 Command ===> Scroll ===> PAGE SELECT FIRSTNME,MIDINIT,LASTNAME,WORKDEPT,SALARY FROM DSN8810.EMP T FOR? WHERE SALARY>30000 ORDER BY ? GROUP BY ? Commands: EDIT RESET * DRAW QUOTE INS UPD DEL COUNT COUNT_BIG Line commands: S - Show SA - Show ASC SD - Show DESC AVG, COUNT, COUNT_BIG, MAX, MIN, STDDEV, SUM, VARIANCE - Aggregate functions <oper><expr>, OR <pred>, IN list, BETWEEN <expr>,<expr> - WHERE predicates ? - Show all line commands Select Column Name Col Type Length * * * ---------------------------------- ------------------ -------- ----------- EMPNO CHAR 6 *S FIRSTNME VARCHAR 12 *S MIDINIT CHAR 1 *S LASTNAME VARCHAR 15 *S WORKDEPT CHAR 3 PHONENO CHAR 4 HIREDATE DATE 10 JOB CHAR 8 EDLEVEL SMALLINT 2 SEX CHAR 1 BIRTHDATE DATE 10 SD SALARY DECIMAL 9 BONUS DECIMAL 9 COMM DECIMAL 9 ******************************* END OF DB2 DATA *******************************
The SELECT statement is updated again as shown:
ADB21TSE --------- DD1A Build SQL SELECT Prototype -------------- Row 1 of 14 Command ===> Scroll ===> PAGE SELECT FIRSTNME,MIDINIT,LASTNAME,WORKDEPT,SALARY FROM DSN8810.EMP T FOR? WHERE SALARY>30000 ORDER BY SALARY DESC GROUP BY ? Commands: EDIT RESET * DRAW QUOTE INS UPD DEL COUNT COUNT_BIG Line commands: S - Show SA - Show ASC SD - Show DESC AVG, COUNT, COUNT_BIG, MAX, MIN, STDDEV, SUM, VARIANCE - Aggregate functions <oper><expr>, OR <pred>, IN list, BETWEEN <expr>,<expr> - WHERE predicates ? - Show all line commands Select Column Name Col Type Length * * * ---------------------------------- ------------------ -------- ----------- EMPNO CHAR 6 FIRSTNME VARCHAR 12 MIDINIT CHAR 1 LASTNAME VARCHAR 15 WORKDEPT CHAR 3 PHONENO CHAR 4 HIREDATE DATE 10 JOB CHAR 8 EDLEVEL SMALLINT 2 SEX CHAR 1 BIRTHDATE DATE 10 *SD SALARY DECIMAL 9 BONUS DECIMAL 9 COMM DECIMAL 9 ******************************* END OF DB2 DATA *******************************
The SQL statement is now ready to be run. Do not specify any line commands when running the statement.
- Press Enter to display the result of the SELECT
statement:
DB2 Admin -------------- DB2 Result of the SQL SELECT ------------- Row 1 of 8 Command ===> Scroll ===> PAGE L FIRSTNME MIDINIT LASTNAME WORKDEPT SALARY * * * * * - ------------ ------- --------------- -------- ----------- CHRISTINE I HAAS A00 52750.00 DIAN J HEMMINGER A00 46500.00 VINCENZO G LUCCHESI A00 46500.00 MICHAEL L THOMPSON B01 41250.00 JOHN B GEYER E01 40175.00 SALLY A KWAN C01 38250.00 EVA D PULASKI D21 36170.00 IRVING F STERN D11 32250.00 ******************************* END OF DB2 DATA *******************************
- On the Tables, Views, and Aliases
(ADB223T) panel, use the SEL
line command to select the EMP table, and press
Enter:
- Example of building a SELECT statement when creating a view
-
You can also use prototyping to create a SELECT statement when creating a view. From the Create View (ADB26CV) panel, in the SELECT stmt field, enter ? to begin navigating to the Build SQL SELECT Prototype (ADB21TSE) panel where you can build a SELECT statement.
ADB26CV n ----------------------- DD1A Create View ---------------------- 10:4 Command ===> CREATE VIEW Owner ===> > (optional, default is ISTJE) Name ===> TAB1_VW > (? to look up) ( (optional column list) Col names ===> ) AS (? to use SELECT prototype) SELECT stmt===> ? WITH ? CHECK OPTION (check INSERTS/UPDATES with VIEW definition) Check opt ===> (Y-include, N-omit, blank-omit (default), C-CASCADED, L-LOCAL)