SQL access levels in routines

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.

The SQL access levels follow:
  • NO SQL
  • CONTAINS SQL
  • READS SQL
  • MODIFIES SQL

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.

The default and maximal SQL access levels for different types of routines are shown in the following table:
Table 1. Default and maximum SQL access levels for routines
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.

In the CREATE statement for a routine:
  • If you explicitly specify READS SQL DATA, no SQL statement in the routine can modify data.
  • If you explicitly specify CONTAINS SQL DATA, no SQL statement in the routine can modify or read data.
  • If you explicitly specify NO SQL, there must be no executable SQL statements in the routine.