GUIDELINE
Review Date
08/2002
1.0 SUBJECT: Dynamic SQL
2.0 DISTRIBUTION: All Current Users
3.0 FROM: Bruce Roberts, Director of DISC
4.0 PURPOSE: To provide direction in the use of Dynamic SQL.
5.0 BACKGROUND: When using Dynamic SQL, the preparation for execution (dynamic bind) of the plan is performed at execution time. Conversely, a bind with static SQL is carried out during the compile process. This process can be performed in a batch mode, during off hours, when it will not inhibit online performance.
5.1 Dynamic SQL requires that the bind process be completed for every execution of an SQL statement.
5.2 Performing the bind process using Dynamic SQL may result in a ten-fold or more increase in the number of instructions that are executed online, thus increasing response time.
5.3 Dynamic SQL requires that additional I/Os be performed in order to satisfy all authorization checks and path selection requirements needed to accomplish the dynamic bind.
5.4 A user executing Dynamic SQL must have the necessary authorization to access all the referenced tables. Without the proper authority, the execution will fail.
6.0 GUIDELINE: Because the overhead connected with the preparation for execution of Dynamic SQL is so high, its use should be limited to only the most extreme or unusual cases.
6.1 The use of any product that utilizes Dynamic SQL requires careful consideration.
6.2 If a system design appears to call for a Dynamic SQL process, it should first be analyzed for the possibility of using multiple static calls.
7.0 HISTORY: This publication was originally issued as Guideline 3610.00, 2/10/1992. Re-issued #3610.01, 7/5/200. Re-issued #3610.02, 8/15/2002.
8.0 CONTACT: Deputy Director, Bureau of Information Systems, 785-296-3343