DB2 Version 9.7 for Linux, UNIX, and Windows

select-statement


>>-+-----------------------------------+--fullselect--●--------->
   |       .-,-----------------------. |                  
   |       V                         | |                  
   '-WITH----common-table-expression-+-'                  

>--+------------------+--●--+---------------------+--●---------->
   +-read-only-clause-+     '-optimize-for-clause-'      
   '-update-clause----'                                  

>--+------------------+--●-------------------------------------->
   '-isolation-clause-'      

>--+-------------------------------------+--●------------------><
   '-concurrent-access-resolution-clause-'

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 through the use of dynamic SQL statements using the command line processor (or similar tools), causing a result table to be displayed on the user's screen. In either case, the table specified by a select-statement is the result of the fullselect.

The authorization for a select-statement is described in the Authorization section in "SQL queries".

common-table-expression


>>-table-name--+---------------------------+-------------------->
               |    .-,-----------.        |   
               |    V             |    (1) |   
               '-(----column-name-+--)-----'   

>--AS--(--fullselect--)----------------------------------------><

Notes:

If a common table expression is recursive, or if the fullselect results in duplicate column names, column names must be specified.

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. 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 (SQLSTATE 42726). If the common table expression is specified in an INSERT statement the table-name cannot be the same as the table or view name that is the object of the insert (SQLSTATE 42726). 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 (SQLSTATE 42835). 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.

If the fullselect of a common table expression contains a data-change-table-reference in the FROM clause, the common table expression is said to modify data. A common table expression that modifies data is always evaluated when the statement is processed, regardless of whether the common table expression is used anywhere else in the statement. If there is at least one common table expression that reads or modifies data, all common table expressions are processed in the order in which they occur, and each common table expression that reads or modifies data is completely executed, including all constraints and triggers, before any subsequent common table expressions are executed.

The common table expression is also optional prior to the fullselect in the CREATE VIEW and INSERT statements.

A common table expression can be used:

In place of a view to avoid creating the view (when general use of the view is not required and positioned updates or deletes are not used)
To enable grouping by a column that is derived from a scalar subselect or function that is not deterministic or has external action
When the desired result table is based on host variables
When the same result table needs to be shared in a fullselect
When the result needs to be derived using recursion
When multiple SQL data change statements need to be processed within the query

If the fullselect of a common table expression contains a reference to itself in a FROM clause, the common table expression is a recursive common table expression. Queries using recursion are useful in supporting applications such as bill of materials (BOM), reservation systems, and network planning.

The following must be true of a recursive common table expression:

Each fullselect that is part of the recursion cycle must start with SELECT or SELECT ALL. Use of SELECT DISTINCT is not allowed (SQLSTATE 42925). Furthermore, the unions must use UNION ALL (SQLSTATE 42925).
The column names must be specified following the table-name of the common table expression (SQLSTATE 42908).
The first fullselect of the first union (the initialization fullselect) must not include a reference to any column of the common table expression in any FROM clause (SQLSTATE 42836).
If a column name of the common table expression is referred to in the iterative fullselect, the data type, length, and code page for the column are determined based on the initialization fullselect. The corresponding column in the iterative fullselect must have the same data type and length as the data type and length determined based on the initialization fullselect and the code page must match (SQLSTATE 42825). However, for character string types, the length of the two data types may differ. In this case, the column in the iterative fullselect must have a length that would always be assignable to the length determined from the initialization fullselect.
Each fullselect that is part of the recursion cycle must not include any aggregate functions, group-by-clauses, or having-clauses (SQLSTATE 42836).
The FROM clauses of these fullselects can include at most one reference to a common table expression that is part of a recursion cycle (SQLSTATE 42836).
The iterative fullselect and the overall recursive fullselect must not include an order-by-clause (SQLSTATE 42836).
Subqueries (scalar or quantified) must not be part of any recursion cycles (SQLSTATE 42836).

When developing recursive common table expressions, remember that an infinite recursion cycle (loop) can be created. Check that recursion cycles will terminate. This is especially important if the data involved is cyclic. A recursive common table expression is expected to include a predicate that will prevent an infinite loop. The recursive common table expression is expected to include:

In the iterative fullselect, an integer column incremented by a constant.
A predicate in the where clause of the iterative fullselect in the form "counter_col < constant" or "counter _col < :hostvar".

A warning is issued if this syntax is not found in the recursive common table expression (SQLSTATE 01605).

Recursion example: bill of materials

Bill of materials (BOM) applications are a common requirement in many business environments. To illustrate the capability of a recursive common table expression for BOM applications, consider a table of parts with associated subparts and the quantity of subparts required by the part. For this example, create the table as follows:

  CREATE TABLE PARTLIST
                 (PART VARCHAR(8),
                  SUBPART VARCHAR(8),
                  QUANTITY INTEGER);

To give query results for this example, assume that the PARTLIST table is populated with the following values:

  PART     SUBPART  QUANTITY
  -------- -------- -----------
  00       01                 5
  00       05                 3
  01       02                 2
  01       03                 3
  01       04                 4
  01       06                 3
  02       05                 7
  02       06                 6
  03       07                 6
  04       08                10
  04       09                11
  05       10                10
  05       11                10
  06       12                10
  06       13                10
  07       14                 8
  07       12                 8

Example 1: Single level explosion

The first example is called single level explosion. It answers the question, "What parts are needed to build the part identified by '01'?". The list will include the direct subparts, subparts of the subparts and so on. However, if a part is used multiple times, its subparts are only listed once.

WITH RPL (PART, SUBPART, QUANTITY) AS
     (  SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
        FROM PARTLIST ROOT
        WHERE ROOT.PART = '01'
      UNION ALL
        SELECT CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY
        FROM RPL PARENT, PARTLIST CHILD
        WHERE  PARENT.SUBPART = CHILD.PART
     )
SELECT DISTINCT PART, SUBPART, QUANTITY
 FROM RPL
  ORDER BY PART, SUBPART, QUANTITY;

The above query includes a common table expression, identified by the name RPL, that expresses the recursive part of this query. It illustrates the basic elements of a recursive common table expression.

The first operand (fullselect) of the UNION, referred to as the initialization fullselect, gets the direct children of part '01'. The FROM clause of this fullselect refers to the source table and will never refer to itself (RPL in this case). The result of this first fullselect goes into the common table expression RPL (Recursive PARTLIST). As in this example, the UNION must always be a UNION ALL.

The second operand (fullselect) of the UNION uses RPL to compute subparts of subparts by having the FROM clause refer to the common table expression RPL and the source table with a join of a part from the source table (child) to a subpart of the current result contained in RPL (parent). The result goes back to RPL again. The second operand of UNION is then used repeatedly until no more children exist.

The SELECT DISTINCT in the main fullselect of this query ensures the same part/subpart is not listed more than once.

The result of the query is as follows:

  PART     SUBPART  QUANTITY
  -------- -------- -----------
  01       02                 2
  01       03                 3
  01       04                 4
  01       06                 3
  02       05                 7
  02       06                 6
  03       07                 6
  04       08                10
  04       09                11
  05       10                10
  05       11                10
  06       12                10
  06       13                10
  07       12                 8
  07       14                 8

Observe in the result that from part '01' we go to '02' which goes to '06' and so on. Further, notice that part '06' is reached twice, once through '01' directly and another time through '02'. In the output, however, its subcomponents are listed only once (this is the result of using a SELECT DISTINCT) as required.

It is important to remember that with recursive common table expressions it is possible to introduce an infinite loop. In this example, an infinite loop would be created if the search condition of the second operand that joins the parent and child tables was coded as:

   PARENT.SUBPART = CHILD.SUBPART

This example of causing an infinite loop is obviously a case of not coding what is intended. However, care should also be exercised in determining what to code so that there is a definite end of the recursion cycle.

The result produced by this example query could be produced in an application program without using a recursive common table expression. However, this approach would require starting of a new query for every level of recursion. Furthermore, the application needs to put all the results back in the database to order the result. This approach complicates the application logic and does not perform well. The application logic becomes even harder and more inefficient for other bill of material queries, such as summarized and indented explosion queries.

Example 2: Summarized explosion

The second example is a summarized explosion. The question posed here is, what is the total quantity of each part required to build part '01'. The main difference from the single level explosion is the need to aggregate the quantities. The first example indicates the quantity of subparts required for the part whenever it is required. It does not indicate how many of the subparts are needed to build part '01'.

WITH RPL (PART, SUBPART, QUANTITY) AS
   (
      SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
       FROM PARTLIST ROOT
       WHERE ROOT.PART = '01'
    UNION ALL
      SELECT PARENT.PART, CHILD.SUBPART, PARENT.QUANTITY*CHILD.QUANTITY
       FROM RPL PARENT, PARTLIST CHILD
       WHERE PARENT.SUBPART = CHILD.PART
   )
SELECT PART, SUBPART, SUM(QUANTITY) AS "Total QTY Used"
 FROM RPL
  GROUP BY PART, SUBPART
  ORDER BY PART, SUBPART;

In the above query, the select list of the second operand of the UNION in the recursive common table expression, identified by the name RPL, shows the aggregation of the quantity. To find out how much of a subpart is used, the quantity of the parent is multiplied by the quantity per parent of a child. If a part is used multiple times in different places, it requires another final aggregation. This is done by the grouping over the common table expression RPL and using the SUM aggregate function in the select list of the main fullselect.

The result of the query is as follows:

  PART     SUBPART  Total Qty Used
  -------- -------- --------------
  01       02                    2
  01       03                    3
  01       04                    4
  01       05                   14
  01       06                   15
  01       07                   18
  01       08                   40
  01       09                   44
  01       10                  140
  01       11                  140
  01       12                  294
  01       13                  150
  01       14                  144

Looking at the output, consider the line for subpart '06'. The total quantity used value of 15 is derived from a quantity of 3 directly for part '01' and a quantity of 6 for part '02' which is needed 2 times by part '01'.

Example 3: Controlling depth

The question may come to mind, what happens when there are more levels of parts in the table than you are interested in for your query? That is, how is a query written to answer the question, "What are the first two levels of parts needed to build the part identified by '01'?" For the sake of clarity in the example, the level is included in the result.

WITH RPL (LEVEL, PART, SUBPART, QUANTITY) AS
      (
         SELECT 1,               ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
          FROM PARTLIST ROOT
          WHERE ROOT.PART = '01'
       UNION ALL
         SELECT PARENT.LEVEL+1, CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY
          FROM RPL PARENT, PARTLIST CHILD
          WHERE PARENT.SUBPART = CHILD.PART
            AND PARENT.LEVEL < 2
      )
 SELECT PART, LEVEL, SUBPART, QUANTITY
   FROM RPL;

This query is similar to example 1. The column LEVEL was introduced to count the levels from the original part. In the initialization fullselect, the value for the LEVEL column is initialized to 1. In the subsequent fullselect, the level from the parent is incremented by 1. Then to control the number of levels in the result, the second fullselect includes the condition that the parent level must be less than 2. This ensures that the second fullselect only processes children to the second level.

The result of the query is:

  PART     LEVEL       SUBPART  QUANTITY
  -------- ----------- -------- -----------
  01                 1 02                 2
  01                 1 03                 3
  01                 1 04                 4
  01                 1 06                 3
  02                 2 05                 7
  02                 2 06                 6
  03                 2 07                 6
  04                 2 08                10
  04                 2 09                11
  06                 2 12                10
  06                 2 13                10

update-clause


>>-FOR UPDATE--+---------------------+-------------------------><
               |     .-,-----------. |   
               |     V             | |   
               '-OF----column-name-+-'

The FOR UPDATE clause identifies the columns that can appear as targets in an assignment clause 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 a FOR UPDATE clause is specified with a column-name list, and extended indicator variables are not enabled, then column-name must be an updatable column (SQLSTATE 42808).

If a FOR UPDATE clause is specified without a column-name list, then the implicit column-name list is determined as follows:

If extended indicator variables are enabled, all of the columns of the table or view identified in the first FROM clause of the fullselect are included.
If extended indicator variables are not enabled, all of the updatable columns of the table or view identified in the first FROM clause of the fullselect are included.

The FOR UPDATE clause cannot be used if one of the following is true:

The cursor associated with the select-statement is not deletable .
One of the selected columns is a non-updatable column of a catalog table and the FOR UPDATE clause has not been used to exclude that column.

read-only-clause


>>-FOR--+-READ--+--ONLY----------------------------------------><
        '-FETCH-'

The FOR READ ONLY clause indicates that the result table is read-only and therefore the cursor cannot be referred to in Positioned UPDATE and DELETE statements. FOR FETCH ONLY has the same meaning.

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 (or FOR FETCH ONLY) can possibly improve the performance of FETCH operations by allowing the database manager to do blocking. For example, in programs that contain dynamic SQL statements without the FOR READ ONLY or ORDER BY clause, the database manager might open cursors as if the FOR UPDATE clause were specified. It is recommended, therefore, that the FOR READ ONLY clause be used to improve performance, except in cases where queries will be used in positioned UPDATE or DELETE statements.

A read-only result table must not be referred to in a Positioned UPDATE or DELETE statement, whether it is read-only by nature or specified as FOR READ ONLY (FOR FETCH ONLY).

optimize-for-clause


>>-OPTIMIZE FOR--integer--+-ROWS-+-----------------------------><
                          '-ROW--'

The OPTIMIZE FOR clause requests special processing of the select statement. If the clause is omitted, it is assumed that all rows of the result table will be retrieved; if it is specified, it is assumed that the number of rows retrieved will probably not exceed n, where n is the value of integer. The value of n must be a positive integer (not zero). Use of the OPTIMIZE FOR clause influences query optimization, based on the assumption that n rows will be retrieved. In addition, for cursors that are blocked, this clause will influence the number of rows that will be returned in each block (that is, no more than n rows will be returned in each block). If both the fetch-first-clause and the optimize-for-clause are specified, the lower of the integer values from these clauses will be used to influence the communications buffer size. The values are considered independently for optimization purposes.

This clause does not limit the number of rows that can be fetched, or affect the result in any other way than performance. Using OPTIMIZE FOR n ROWS can improve performance if no more than n rows are retrieved, but may degrade performance if more than n rows are retrieved.

If the value of n multiplied by the size of the row exceeds the size of the communication buffer, the OPTIMIZE FOR clause will have no impact on the data buffers. The size of the communication buffer is defined by the rqrioblk or the aslheapsz configuration parameter.

isolation-clause


>>-+---------------------------------------+-------------------><
   '-WITH--+-RR--+---------------------+-+-'   
           |     '-lock-request-clause-' |     
           +-RS--+---------------------+-+     
           |     '-lock-request-clause-' |     
           +-CS--------------------------+     
           '-UR--------------------------'

The optional isolation-clause specifies the isolation level at which the statement is executed, and whether a specific type of lock is to be acquired.

RR - Repeatable Read
RS - Read Stability
CS - Cursor Stability
UR - Uncommitted Read

The default isolation level of the statement is the isolation level of the package in which the statement is bound. When a nickname is used in a select-statement to access data in DB2® family and Microsoft SQL Server data sources, the isolation-clause can be included in the statement to specify the statement isolation level. If the isolation-clause is included in statements that access other data sources, the specified isolation level is ignored. The current isolation level on the federated server is mapped to a corresponding isolation level at the data source on each connection to the data source. After a connection is made to a data source, the isolation level cannot be changed for the duration of the connection.

lock-request-clause


>>-USE AND KEEP--+-SHARE-----+--LOCKS--------------------------><
                 +-UPDATE----+          
                 '-EXCLUSIVE-'

The optional lock-request-clause specifies the type of lock that the database manager is to acquire and hold:

SHARE: Concurrent processes can acquire SHARE or UPDATE locks on the data.
UPDATE: Concurrent processes can acquire SHARE locks on the data, but no concurrent process can acquire an UPDATE or EXCLUSIVE lock.
EXCLUSIVE: Concurrent processes cannot acquire a lock on the data.

The lock-request-clause applies to all base table and index scans required by the query, including those within subqueries, SQL functions and SQL methods. It has no affect on locks placed by procedures, external functions, or external methods. Any SQL function or SQL method invoked (directly or indirectly) by the statement must be created with INHERIT ISOLATION LEVEL WITH LOCK REQUEST (SQLSTATE 42601). The lock-request-clause cannot be used with a modifying query that might invoke triggers or that requires referential integrity checks (SQLSTATE 42601).

concurrent-access-resolution-clause


>>-WAIT FOR OUTCOME--------------------------------------------><

The optional concurrent-access-resolution-clause specifies the concurrent access resolution to use for select-statement.

WAIT FOR OUTCOME specifies to wait for the commit or rollback when encountering data in the process of being updated or deleted. Rows encountered that are in the process of being inserted are not skipped. The settings for the registry variables DB2_EVALUNCOMMITTED, DB2_SKIPDELETED, and DB2_SKIPINSERTED are ignored. This clause applies when the isolation level is CS or RS and is ignored when an isolation level of UR or RR is in effect. This clause causes the default behavior for currently committed that is defined by the cur_commit configuration parameter to be overridden as well as any higher level setting such as bind options, CLI settings, JDBC settings, or lock modifications.

Examples of a select-statement

Example 1: Select all columns and rows from the EMPLOYEE table.

  SELECT * FROM EMPLOYEE

Example 2: 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

Example 3: 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

Example 4: Declare a cursor named UP_CUR to be used in a C program to update 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.

  EXEC SQL  DECLARE UP_CUR CURSOR FOR
              SELECT PROJNO, PRSTDATE, PRENDATE
                FROM PROJECT
                FOR UPDATE OF PRSTDATE, PRENDATE;

Example 5: This example names the expression SAL+BONUS+COMM as TOTAL_PAY

   SELECT SALARY+BONUS+COMM AS TOTAL_PAY
     FROM EMPLOYEE
     ORDER BY TOTAL_PAY

Example 6: Determine the employee number and salary of sales representatives along with the average salary and head count of their departments. Also, list the average salary of the department with the highest average salary.

Using a common table expression for this case saves the overhead of creating the DINFO view as a regular view. During statement preparation, accessing the catalog for the view is avoided and, because of the context of the rest of the fullselect, only the rows for the department of the sales representatives need to be considered by the view.

 WITH
    DINFO (DEPTNO, AVGSALARY, EMPCOUNT) AS
        (SELECT OTHERS.WORKDEPT, AVG(OTHERS.SALARY), COUNT(*)
          FROM EMPLOYEE OTHERS
          GROUP BY OTHERS.WORKDEPT
        ),
    DINFOMAX AS
        (SELECT MAX(AVGSALARY) AS AVGMAX FROM DINFO)
 SELECT THIS_EMP.EMPNO, THIS_EMP.SALARY,
        DINFO.AVGSALARY, DINFO.EMPCOUNT, DINFOMAX.AVGMAX
  FROM EMPLOYEE THIS_EMP, DINFO, DINFOMAX
  WHERE THIS_EMP.JOB = 'SALESREP'
  AND THIS_EMP.WORKDEPT = DINFO.DEPTNO

Example 7: Given two tables, EMPLOYEE and PROJECT, replace employee SALLY with a new employee GEORGE, assign all projects lead by SALLY to GEORGE, and return the names of the updated projects.

  WITH
    NEWEMP AS (SELECT EMPNO FROM NEW TABLE
                (INSERT INTO EMPLOYEE(EMPNO, FIRSTNME)
                    VALUES(NEXT VALUE FOR EMPNO_SEQ, 'GEORGE'))),
    OLDEMP AS (SELECT EMPNO FROM EMPLOYEE WHERE FIRSTNME = 'SALLY'),
    UPPROJ AS (SELECT PROJNAME FROM NEW TABLE
                (UPDATE PROJECT
                    SET RESPEMP = (SELECT EMPNO FROM NEWEMP)
                    WHERE RESPEMP = (SELECT EMPNO FROM OLDEMP))),
    DELEMP AS (SELECT EMPNO FROM OLD TABLE
                (DELETE FROM EMPLOYEE
                    WHERE EMPNO = (SELECT EMPNO FROM OLDEMP)))
  SELECT PROJNAME FROM UPPROJ;

Example 8: Retrieve data from the DEPT table. That data will later be updated with a searched update, and should be locked when the query executes.

  SELECT DEPTNO, DEPTNAME, MGRNO
    FROM DEPT
    WHERE ADMRDEPT ='A00'
    FOR READ ONLY WITH RS USE AND KEEP EXCLUSIVE LOCKS

Example 9: Select all columns and rows from the EMPLOYEE table. If another transaction is concurrently updating, deleting, or inserting data in the EMPLOYEE table, the select operation will wait to get the data until after the other transaction completes.

  SELECT * FROM EMPLOYEE WAIT FOR OUTCOME