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
- Installation SYSOPR authority (catalog tables and system tables only)
- 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
- 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
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
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.
If 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:
- 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
- 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
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
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.