select-statement

The select-statement is the form of a query that can be directly specified in a DECLARE CURSOR statement or FOR statement, prepared and then referenced in a DECLARE CURSOR statement, or directly specified in an SQLJ assignment clause. It can also be issued using SPUFI or the command line processor which causes a result table to be displayed at your terminal. In any case, the result table specified by a select-statement is the result of the fullselect.

Read syntax diagram
>>-+-----------------------------------+--fullselect------------>
   |       .-,-----------------------. |               
   |       V                         | |               
   '-WITH----common-table-expression-+-'               

   .--------------------------.       
   V                          | (2)   
>----+----------------------+-+--------------------------------><
     +-update-clause--------+         
     |                  (1) |         
     +-read-only-clause-----+         
     +-optimize-clause------+         
     +-isolation-clause-----+         
     +-queryno-clause-------+         
     '-SKIP LOCKED DATA-----'         
Notes:
  1. The read-only-clause must not be specified if update-clause is specified.
  2. The same clause must not be specified more than one time.

The tables and view identified in a select statement can be at the current server or any DB2® subsystem with which the current server can establish a connection.

For local queries on DB2 for z/OS® or remote queries in which the server and requester are DB2 for z/OS, if a table is encoded as ASCII or Unicode, the retrieved data is encoded in EBCDIC. For information on retrieving data encoded in ASCII or Unicode, see DB2 Application Programming and SQL Guide.

A select statement can implicitly or explicitly invoke user-defined functions or implicitly invoke stored procedures. This technique is known as nesting of SQL statements. A function or procedure is implicitly invoked in a select statement when it is invoked at a lower level. For instance, if you invoke a user-defined function from a select statement and the user-defined function invokes a stored procedure, you are implicitly invoking the stored procedure. When a SELECT statement refers to a table, any SQL statements that are implicitly invoked (as a result of nested functions or procedures) must not result in an SQL data change statement that modifies the same table.

For example, suppose that you execute this SQL statement at level 1 of nesting: 
SELECT UDF1(C1) FROM T1;
You cannot execute this SQL statement at a lower level of nesting: 
INSERT INTO T1 VALUES(…);