How to code Dynamic SQL(DS) in a COBOL DB2 Program
Dynamic SQL is prepared and executed at the run time as opposed to embedding it in the host language program as in a static one.
Using dynamic SQL in the application programs has proved many advantages. For ex: In some cases the businesses requirement demands various predicates to be used in a SQL statement which may vary depending upon the requirement or the input PARM value (i.e; In one case, it requires only one predicate to be used and in another it may require more to be used). Dynamic SQL proved very convenient and effective to implement these type of business requirements.
It can be used where the SQL statements to be used in the select query are not known (or not fixed) before the execution of the application.
In general, an application that uses DS has a higher start-up (or initial) cost per SQL statement due to the need of compiling the statements before using them. Once compiled, the execution time for dynamic compared to static is more or less equal and, in some cases, faster due to better access plans being chosen by the optimizer. Each time a dynamic statement is executed, the initial compilation cost becomes less of a factor. If multiple users are running the same dynamic application with the same statements, only the first application to issue the statement realizes the cost of statement compilation.
More Info:
Static queries are hard-coded, and only the values of host variables in predicates can change. Dynamic SQL is characterized by its capability to change columns, tables, and predicates during a program’s execution. This flexibility requires different techniques for embedding dynamic SQL in application programs.
Notes:
- You should avoid dynamic SQL(DS) when the SQL statements to be used are just a series of static SQL statements in disguise.
- The static one takes more time to code but less time to execute.
- Another reason for avoiding dynamic is that it almost always requires more overhead to process than equivalent static queries. DS incurs overhead because the cost of the dynamic bind, or PREPARE, must be added to the processing time of all DS programs. The actual time required to perform the dynamic PREPARE will vary with the complexity of the SQL statement. In general, the more complex the statement, the longer DB2 will take to optimize it. So be sure to test the queries of varying complexity.
- In addition, DS generally reduces the number of SQL statements coded in your application program, thereby reducing the size of the plan and increasing the efficient use of system memory. If you have a compelling reason to use DS, ensure that the reason is sound and complies with the considerations listed in the following section.
Example and important parts will be discussed in the next topics.
Feel free to post via comments. if you have more information on this topic.
– Admin
