>>-+---------------------------------------+--fullselect-------->
| .-,--------------------------. |
| V | |
'-WITH-----common-table-expression---+--'
>-----+-----------------+--------------------------------------->
'-order-by-clause-'
.------------------------.
V | (1) (2)
>--------+------------------+--+-------------------------------><
+-update-clause----+
+-read-only-clause-+
+-optimize-clause--+
'-isolation-clause-'
Notes:
|
The select-statement is the form of a query that can be directly specified in a DECLARE CURSOR statement, or prepared and then referenced in a DECLARE CURSOR statement. It can also be issued interactively, using the interactive facility (STRSQL command), causing a result table to be displayed at your work station. In either case, the table specified by a select-statement is the result of the fullselect.
>>-table-name--+----------------------------+---AS--(--subselect--)-->
| .-,--------------. |
| V | |
'-(-----column-name---+---)--'
>--------------------------------------------------------------><
|
A common-table-expression permits defining a result table with a table-name that can be specified as a table name in any FROM clause of the fullselect that follows. The table-name must be unqualified. Multiple common table expressions can be specified following the single WITH keyword. Each common table expression specified can also be referenced by name in the FROM clause of subsequent common table expressions.
If a list of columns is specified, it must consist of as many names as there are columns in the result table of the fullselect. Each column-name must be unique and unqualified. If these column names are not specified, the names are derived from the select list of the fullselect used to define the common table expression.
The table-name of a common table expression must be different from any other common table expression table-name in the same statement. A common table expression table-name can be specified as a table name in any FROM clause throughout the fullselect. A table-name of a common table expression overrides any existing table, view, or alias (in the catalog) with the same qualified name.
If more than one common table expression is defined in the same statement, cyclic references between the common table expressions are not permitted. A cyclic reference occurs when two common table expressions dt1 and dt2 are created such that dt1 refers to dt2 and dt2 refers to dt1.
A common-table-expression is also optional prior to the fullselect in the CREATE VIEW and INSERT statements.
A common-table-expression can be used:
If a subselect of a common table expression contains a reference to itself in a FROM clause, the common table expression is a recursive table expression. Recursive common table expressions are not supported in DB2 UDB for AS/400.
.-,--------------------.
V .-ASC--. |
>>-ORDER BY-------sort-key--+------+--+------------------------><
'-DESC-'
sort-key
|---+-simple-column-name--+-------------------------------------|
+-simple-integer------+
'-sort-key-expression-'
|
The ORDER BY clause specifies an ordering of the rows of the result table. If a single sort-key is identified, the rows are ordered by the values of that sort-key. If more than one sort-key is identified, the rows are ordered by the values of the first identified sort-key, then by the values of the second identified sort-key, and so on.
If a sort sequence other than *HEX is in effect when the statement that contains the ORDER BY clause is executed and if the ORDER BY clause involves sort-keys that have SBCS, UCS-2, or mixed data, the comparison for those sort-keys is done using weighted values. The weighted values are derived by applying the sort sequence to the values of the sort-keys.
A named column in the select list may be identified by a sort-key that is a simple-integer or a simple-column-name. An unnamed column in the select list may be identified by a simple-integer or by a sort-key-expression. A column is unnamed if the AS clause is not specified in the select-list and if it is derived from a constant, an expression with operators, or a function. If the fullselect includes a UNION operator, see "fullselect" for the rules on named columns in a fullselect.
If the fullselect includes a UNION or UNION ALL, the column name cannot be qualified.
The simple-column-name may also identify a column name of a table, view or nested table identified in the FROM clause if the query is a subselect. An error occurs if the subselect produces a grouped result and the simple-column-name is not a grouping-expression.
The sort-key-expression specified in these clauses must exactly match an expression in the select list; except that blanks are not significant. The sort-key-expression cannot contain RRN, PARTITION, NODENAME, or NODENUMBER if the fullselect includes a UNION or UNION ALL. The result of the sort-key-expression must not be a LOB or DATALINK.
If the subselect is grouped, the sort-key-expression can:
Ordering is performed in accordance with the comparison rules described in "Language Elements". The null value is higher than all other values. If your ordering specification does not determine a complete ordering, rows with duplicate values of the last identified sort-key have an arbitrary order. If the ORDER BY clause is not specified, the rows of the result table have an arbitrary order.
The ORDER BY clause can contain up to 10000-n sort-keys or 10000-n bytes (where n is the number of sort-keys specified that allow nulls).
>>-FOR UPDATE----+-------------------------+-------------------><
| .-,--------------. |
| V | |
'-OF-----column-name---+--'
|
The UPDATE clause identifies the columns that can be updated in a subsequent Positioned UPDATE statement. Each column-name must be unqualified and must identify a column of the table or view identified in the first FROM clause of the fullselect. If the UPDATE clause is specified without column names, all updateable columns of the table or view identified in the first FROM clause of the fullselect are included. The clause can appear either before or after an accompanying optimize-clause.
The FOR UPDATE OF clause must not be specified if the result table of the fullselect is read-only (for more information see DECLARE CURSOR), if the FOR READ ONLY clause is used, or if the SCROLL keyword is specified without the DYNAMIC keyword on the DECLARE CURSOR statement.
Positioned UPDATE statements identifying the cursor associated with a select-statement can update all updateable columns, if:
The UPDATE clause is a performance option that is not part of ISO/ANSI SQL.
>>-FOR----+-READ--+--ONLY--------------------------------------><
'-FETCH-'
|
The FOR READ ONLY or FOR FETCH ONLY clause indicates that the result table is read-only. For example, its cursor is not used for Positioned DELETE or UPDATE statements.
Some result tables are read-only by nature (for example, a table based on a read-only view). FOR READ ONLY can still be specified for such tables, but the specification has no effect.
For result tables in which updates and deletes are allowed, specifying FOR READ ONLY can possibly improve the performance of FETCH operations by allowing the database manager to do blocking and avoid exclusive locks. For example, in programs that contain dynamic SQL statements without the FOR READ ONLY or ORDER BY clause, the database manager might open cursors that have not specified SCROLL without the DYNAMIC keyword as if the FOR UPDATE OF clause was specified.
A read-only result table must not be referred to in an UPDATE or DELETE statement, whether it is read-only by nature or specified as FOR READ ONLY.
The FOR READ ONLY clause cannot appear in a statement containing an UPDATE clause. The clause can appear either before or after an accompanying optimize-clause.
>>-OPTIMIZE FOR--+-integer-+---+-ROW--+------------------------><
'-ALL-----' '-ROWS-'
|
The optimize-clause tells the database manager to assume that the program does not intend to retrieve more than integer rows from the result table. Without this clause, or with the keyword ALL, the database manager assumes that all rows of the result table are to be retrieved, and it optimizes accordingly. Optimizing for integer rows, or at a minimum, the number of rows that are fetched, could improve performance. The clause does not change the result table or the order in which the rows are fetched. Any number of rows can be fetched, but performance can possibly degrade after integer fetches. The clause can appear either before or after an accompanying update-clause or read-only-clause.
(1)
>>-WITH----+-NC-----------------------+------------------------><
| (2) |
+-UR-----------------------+
+-CS--+------------------+-+
| | (3) | |
| '-KEEP LOCKS-------' |
| (4) |
+-RS-----------------------+
'-RR-----------------------'
Notes:
|
specifies an isolation level on the SELECT, SELECT INTO, INSERT, UPDATE, DELETE, and DECLARE CURSOR statements. This isolation level is in effect only for the execution of the statement containing the isolation clause. For more information on isolation level see "Isolation Level".
The KEEP LOCKS clause specifies that any read locks acquired will be held for a longer duration. Normally, read locks are released when the next row is read. If the isolation clause is associated with a cursor, the locks will be held until the cursor is closed or until a COMMIT or ROLLBACK statement is executed. Otherwise, the locks will be held until the completion of the SQL statement. The KEEP LOCKS clause is allowed on an SQL SELECT, SELECT INTO, or DECLARE CURSOR statement. It is not allowed on updateable cursors.
Select all columns and rows from the EMPLOYEE table.
SELECT * FROM EMPLOYEE
Select the project name (PROJNAME), start date (PRSTDATE), and end date (PRENDATE) from the PROJECT table. Order the result table by the end date with the most recent dates appearing first.
SELECT PROJNAME, PRSTDATE, PRENDATE
FROM PROJECT
ORDER BY PRENDATE DESC
Select the department number (WORKDEPT) and average departmental salary (SALARY) for all departments in the EMPLOYEE table. Arrange the result table in ascending order by average departmental salary.
SELECT WORKDEPT, AVG(SALARY)
FROM EMPLOYEE
GROUP BY WORKDEPT
ORDER BY 2
Declare a cursor named UP_CUR, to be used in a PL/I program, that updates the start date (PRSTDATE) and the end date (PRENDATE) columns in the PROJECT table. The program must receive both of these values together with the project number (PROJNO) value for each row. The declaration specifies that the access path for the query be optimized for the retrieval of a maximum of 2 rows. Even so, the program can retrieve more than 2 rows from the result table. However, when more than 2 rows are retrieved, performance could possibly degrade.
EXEC SQL DECLARE UP_CUR CURSOR FOR
SELECT PROJNO, PRSTDATE, PRENDATE
FROM PROJECT
FOR UPDATE OF PRSTDATE, PRENDATE
OPTIMIZE FOR 2 ROWS ;
Select items from a table with an isolation level of Repeatable Read (RS, ALL).
SELECT NAME, SALARY FROM PAYROLL WHERE DEPT = 704 WITH RS