SQL data access level enforcement

The degree to which a routine (stored procedure or user-defined function) can execute SQL statements is determined by its SQL access level.

By default, SQL PL and PL/SQL routines enforce data access levels at compile time. If a stored procedure or user-defined function contains an SQL statement that requires a data access level that exceeds that of the routine, an error is returned when you create the routine. Similarly, if a stored procedure or user-defined function invokes another routine whose data access level exceeds that of the calling routine, an error is returned when you create the first routine. Additionally, if you define a compiled user-defined function as MODIFIES SQL DATA, you can use it only as the sole element on the right side of an assignment statement within a compound SQL (compiled) statement. This check is also performed when the statement is compiled.

Starting with Version 9.7 Fix Pack 3, you can have SQL PL and PL/SQL routines enforce data access levels at run-time instead of at compile time by using the DB2_COMPATIBILITY_VECTOR registry variable. When the DB2_COMPATIBILITY_VECTOR registry variable is set to support run-time routine SQL data access level enforcement, the enforcement is performed at run-time on a statement level instead. In this case, an error is returned when a statement that exceeds the current SQL data access level is performed. However, if a routine invokes another routine defined with a more restrictive SQL data access level, the called routine inherits the data access level of its parent. Additionally, if you define a compiled user-defined function as MODIFIES SQL DATA and it is not the sole element on the right side of an assignment statement within a compound SQL (compiled) statement, an error is returned only if the function issues an SQL statement that modifies SQL data.

In addition, starting with Version 9.7 Fix Pack 6, COMMIT and ROLLBACK statements are allowed in a compiled PL/SQL user-defined function and a compiled language SQL user-defined function that has been defined with the MODIFIES SQL DATA clause in a CREATE FUNCTION statement.