Updated 13 September 2016 to use more correct wording around how and when the access plan is generated and reused.
As a new DB2 for LUW DBA or developer it can take a while to understand the difference between static and dynamic SQL. I’d like to use an example in a stored procedure to explain.
Example
The same SQL can be considered static or dynamic depending on how it is executed. In this post, I’m going to show how the same SQL statement can be done statically or dynamically. The example SQL statement we’ll use is:
UPDATE DB2.USER set EMAIL=upper(?) where user_id=?
The question marks are replaced with real values at execution time in both the static and the dynamic examples.
Static SQL
The basic concept with Static SQL is that the SQL itself does not change – only values supplied for host variables or parameter markers change. This allows DB2 to compile the SQL only once, store the access plan in the catalog, and use the same access plan over and over. The access plan is re-generated on REBIND, db2rbind, invalidation, or if the package is bound with parameters that affect access plan generation.
To execute our statement using static SQL, we would use syntax like this:
UPDATE db2.user SET email=upper(v_EMAIL) WHERE user_id=v_USER_ID;
v_Email and v_USER_ID represent the variables that hold the values. Note that I’m not even specifying prepare or execute or any of those details. The ONLY way this stored procedure can ever change this SQL is in the variable values.
Dynamic SQL
For Dynamic SQL, DB2 looks in the package cache to see if an access plan exists. If an access plan does not exist, it compiles the SQL and creates an access plan. This happens every execution, and access plan re-use is entirely dependent on what is in the package cache at execution time. The stored procedure can change the SQL statement each time it runs – the table being selected from, the syntax of the statement, etc. Dynamic SQL in our example might look like this:
SET v_USER_UPDATE='UPDATE db2.user SET email=upper(''' || v_EMAIL || ''') WHERE user_id=' || v_USER_ID;
EXECUTE IMMEDIATE v_USER_UPDATE;
Permissions and Stored Procedures
The syntax presented in the above two sections will execute exactly the same statement as static or as dynamic SQL. What’s different about these other than when compilation of the access plan occurs? Well, another difference is in whose permissions are checked. With static SQL in a stored procedure, the permissions of the user who created the stored procedure are checked after ensuring the executing user has EXECUTE permission on the stored procedure. For the dynamic SQL, the permissions of the user executing the stored procedure are checked. Thus if the executing user does not have UPDATE on DB2.USER, a stored procedure that runs static SQL (the first) would succeed, while it would fail for the dynamic SQL (the second).
