The degree to which routines can execute SQL statements is determined by the SQL access level of the routine. The SQL access level for a routine is determined by both what is permitted for the particular type of routine and what limitation is specified explicitly within the CREATE statement that defines a routine.
This SQL access level clause is used to provide information to the database manager about the statement so that the statement can be executed safely by the database manager and with the best possible performance.
Routine type | Default SQL access level | Maximum allowed SQL access level |
---|---|---|
SQL procedures | MODIFIES SQL DATA | MODIFIES SQL DATA |
SQL functions (scalar functions) | READS SQL DATA | READS SQL DATA |
SQL functions (table functions) | READS SQL DATA | MODIFIES SQL DATA |
External procedures | MODIFIES SQL DATA | MODIFIES SQL DATA |
External functions (scalar functions) | READS SQL DATA | READS SQL DATA |
External functions (table functions) | READS SQL DATA | READS SQL DATA |
Optimal performance of routines is achieved when the most restrictive SQL access clause that is valid is specified in the routine CREATE statement.