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.

Authorization for select-statement

See Authorization for queries.

Syntax for select-statement

Read syntax diagramSkip visual syntax diagramWITH,common-table-expression fullselect1 update-clauseread-only-clause2optimize-clauseisolation-clausequeryno-clauseSKIP LOCKED DATA3
Notes:
  • 1 If fullselect is a VALUES clause, common-table-expression, update-clause, read-only-clause, optimize-clause, isolation-clause, queryno-clause, and SKIP LOCKED DATA must not also be specified
  • 2 The read-only-clause must not be specified if update-clause is specified.
  • 3 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 Distributed queries against ASCII or Unicode tables.

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.