SELECT statement

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 SPUFI or the command line processor which causes a result table to be displayed at your terminal. In any case, the table specified by select-statement is the result of the fullselect.

Authorization for SELECT

For any form of a query, the privilege set that is defined below must include one of the following authorities or privilege sets:

  • For each table or view identified in the statement, the privilege set must include one of the following:
    • Ownership of the table or view
    • The SELECT privilege on the table or view
    • DBADM authority for the database (tables only)

    If the database is implicitly created, the database privileges must be on the implicit database or on DSNDB04.

  • SYSADM authority
  • SYSCTRL authority (catalog tables only)
  • DATAACCESS authority
  • Start of changeInstallation SYSOPR authority (catalog tables and system tables only)End of change
If a query includes a user-defined function, the privileges that are held by the authorization ID of the statement must include at least one of the following:
  • For each user-defined function that is identified in the statement, one of the following:
    • The EXECUTE privilege on the function
    • Ownership of the function
  • SYSADM authority
  • DATAACCESS authority
Start of changeIf a query includes a sequence object, the privileges that are held by the authorization ID of the statement must include at least one of the following:
  • For each sequence object that is identified in the statement, one of the following:
    • The USAGE privilege on the sequence object
    • Ownership of the sequence object
  • DATAACCESS authority
End of change

If the select-statement is part of a DECLARE CURSOR statement, the privilege set is the privileges that are held by the authorization ID of the owner of the plan or package.

If the select-statement contains an SQL data change statement, the privilege set must include the SELECT privilege and the appropriate privileges for the SQL data change statement (insert, update, or delete privileges) on the target table or view.

If the select-statement references a table that contain an active row or column access control, and row permissions or column masks are defined for the table, the authorization ID or role of the statement does not need authority to reference objects that are specified in the definitions of those row permissions or column masks.

Privilege sets for queries

Start of change If the statement is embedded in an application program, the privilege set is the privileges that are held by the owner of the plan or package. End of change

Start of changeIf the statement is dynamically prepared, and the application is bound in a trusted context, the privilege set is the set of privileges that are held by that role. Otherwise, the privilege set depends on the dynamic SQL statement behavior, which is specified by option DYNAMICRULES:End of change

Run behavior
The privilege set is the union of the privilege sets that are held by each authorization ID of the process.
Bind behavior
The privilege set is the privileges that are held by the authorization ID of the owner of the plan or package.
Define behavior
The privilege set is the privileges that are held by the authorization ID of the owner of the stored procedure or user-defined function.
Invoke behavior
The privilege set is the privileges that are held by the authorization ID of the invoker of the stored procedure or user-defined function.

For a list of the DYNAMICRULES values that specify run, bind, define, or invoke behavior, see Table 1.

When any form of a query is used as a component of another statement, the authorization rules that apply to the query are specified in the description of that statement. For example, see the authorization rules that apply to the subselect in a CREATE VIEW statement in CREATE VIEW statement.

If your installation uses the access control authorization exit (DSNX@XAC), that exit might be controlling the authorization rules instead of the rules that are listed here.

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.

Description for 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. For for more information, see select-statement.

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.

Syntax for fullselect

Read syntax diagramSkip visual syntax diagramsubselect( fullselect)values-clause1UNIONEXCEPTINTERSECTDISTINCTALLsubselect( fullselect)order-by-clauseoffset-clausefetch-clause
values-clause
Read syntax diagramSkip visual syntax diagram VALUES sequence-reference(, sequence-reference)
Notes:
  • 1 If values-clause is specified, UNION, EXCEPT, INTERSECT, order-by-clause, or fetch-clause must not also be specified. If fullselect contains a values-clause, the fullselect must only be specified in a select-statement that is referenced by statement-name in a PREPARE statement.

Description for fullselect

The fullselect is a component of the select-statement, ALTER TABLE statement for the definition of a materialized query table, CREATE TABLE statement, CREATE VIEW statement, DECLARE GLOBAL TEMPORARY TABLE statement, INSERT statement, UPDATE statement, and MERGE statement. For more information, see fullselect

Syntax for subselect

Read syntax diagramSkip visual syntax diagram select-clause from-clause where-clause group-by-clause having-clause order-by-clause offset-clause fetch-clause

Description for subselect

The subselect is a component of the fullselect. A subselect specifies a result table that is derived from the tables or views that are identified in the FROM clause. For more information, see subselect.