PART II - DYNAMIC SQL PROGRAMMING - IBM Mainframe

Static SQL is hard-coded in the application program and the only values that can change are the values of the host variables in the predicates. Dynamic SQLs are characterized by the capability to change columns, tables and predicates during a program's execution.

Dynamic SQL and Performance

Compared to static SQLs, even though dynamic SQLs offer a great deal of flexibility, it always requires more overheads to process. Dynamic SQLs incurs overheads because the cost of dynamic bind or PREPARE must be added to the processing time of all dynamic SQL programs. However, dynamic SQLs generally reduces the number of SQL statements to be coded, they uses the most recent RUNSTATS statistics, they can use new indexes without a REBIND, etc. So even though it is always better to use a static SQL, do not rule out the possibility of using a dynamic SQL. With the improvements in the recent versions of DB2, the dynamic SQLs, if coded properly, can be used without much reduction in performance.

Dynamic SQL Coding Guidelines

  • Favor Static SQL. Static SQL might be more efficient than dynamic SQL because dynamic SQL requires the execution of the PREPARE statement during the program execution. Static SQLs are prepared or bound before execution. Static SQLs are enough for more than 90% of the application requirements. But if you want the flexibility and static SQLs does not provide enough design flexibility for the design of changeable SQL statements consider dynamic SQLs. Another time to consider dynamic SQLs is when, due to the limitations in the DB2 optimizer's capability to determine the values of host variables and dynamic SQLs will boost performance. In all other cases use static SQLs.
  • Avoid using dynamic SQLs for the SQL statements like OPEN, DECLARE, DESCRIBE, EXECUTE, EXECUTE IMMEDIATE, FETCH, OPEN/INCLUDE, PREPARE, WHENEVER, etc.,
  • Use parameter markers instead of host variables. Dynamic SQL s cannot contain host variables, but instead a device called parameter marker shown as a question mark (?) is used.
  • Be cautious when using LIKE. When using a LIKE in a static SQL the optimizer cannot use the index because it cannot determine which value to place in the host variable if it starts with a '-' or'%'. But when dynamic SQL is used the optimizer can determine the first character and can use the index, and due to the indexed access the dynamic SQL execution can be faster, sometimes. But in DB2 V2.3 the optimizer can enable the indexed access for static SQL statement for the LIKE predicate also. SO if you are using DB2 V2.3 then static SQL itself can be used.
  • Use dynamic SQLs to access dynamic data. Dynamic SQL can be more efficient for accessing very active tables that fluctuate between many rows and few rows between plan rebinding. If you cannot increase the frequency of rebinding consider using dynamic SQL, which uses the current RUNSTATS for optimizing.
  • Use Dynamic SQLs for flexibility. Dynamic SQL programs respond rapidly to business rules that change frequently. Because dynamic SQL is formulated as the program runs, the flexibility is greater than with static SQL programs. Users can react more quickly to changing business conditions by changing*their selection criteria.
  • Avoid dynamic SQLs for active applications. Do not use dynamic SQLs in systems with many users. The internal DB2 control structure is locked during the dynamic bind and the DB2 catalog also is locked. Both situations will cause deadlock-time-out problems for unrelated applications.

Types of Dynamic SQL Statements

There are for classes of dynamic SQL statements. They are EXECUTE IMMEDIATE, non-SELECT dynamic SQL, fixed-list SELECT and varying-list SELECT.

EXECUTE IMMEDIATE

EXECUTE IMMEDIATE implicitly prepares and executes complete SQL statements coded in host variables. Only a sub-set of SQL statements is available when you use this command, the most prominent being SELECT, and so this call of dynamic SQLs cannot be used for data retrieval. So if you do want any data to be retrieved then the SQL portion of your program consists of two steps. First moving the complete text for the statement to be executed into the host variable and second issue an EXECUTE IMMEDIATE command giving the host variable as an argument. The statement is prepared and executed automatically. Consider the following example:

EXECUTE IMMEDIATE

The following statements are supported by the EXECUTE IMMEDIATE statement: ALTER, COMMENT ON, COMMIT, CREATE, DELETE, DROP, EXPLAIN, GRANT, INSERT, LABEL ON, LOCK TABLE, REVOKE, ROLLBACK, SET and UPDATE.

Even though the EXECUTE IMMEDIATE statement is simple to use try to avoid it because of two main reasons. First, it does not support the SELECT statement. Second, it can result in poor performance, if the same SQL statement is used many times. This is because, after an EXECUTE IMMEDIATE statement is performed, the executable form of the SQL is destroyed and the program has to prepare the executable form when it executed again.

Non-SELECT Dynamic SQL

This statement is used to prepare and execute the SQL statements in an application program. These classes of SQL statement uses PREPARE and EXECUTE to issue the SQL statements. As the name suggests this class also does not support the SELECT statements and cannot be used for querying tables. The following example shows how this type of SQL is used:

Example

Non-SELECT Dynamic SQL

You can replace the DELETE statement in the string with ALTER, COMMENT ON, COMMIT. CREATE, DROP, EXPLAIN, GRANT, INSERT, LABEL ON, LOCK TABLE, REVOKE, ROLLBACK, SET and UPDATE.

Non-SELECT SQL statements can use the feature of dynamic SQL known as the parameter marker. which is a placeholder for the host variables in the SQL. This is explained in the example given below:

Non-SELECT Dynamic SQL

Here the value of the predicate is replace with the parameter marker (?), and when the statement is executed the values can be moved to the host variable and is coded as a parameter to the CURSOR with the USING clause. While execution the host variable values replaces the parameter marker.

Fixed-List SELECT

A fixed-list SELECT statement can be used to explicitly prepare and execute SQL SELECT statements when the column to be selected is known and unchanging. This is necessary to create the working storage declaration for the host variable in the program. How the fixed-list SELECT dynamic SQL is coded in the following example:

Fixed-List SELECT

Fixed-List SELECT

Varying-List SELECT

Varying-list SELECT SQL statements are used when you do not know the columns that will be retrieved by an application program. This class of SQLs provides more flexibility than any other dynamic SQLs. You can change table, columns and predicated during the execution. Since everything about this SQL can change during execution, the number and type of host variables cannot be known beforehand. This is why, this class of SQL is the most complicated among the dynamic SQLs. Coding of varying-list SELECT dynamic SQLs is a very complex procedure and is beyond the scope of this book. But more information can be obtained form the following IBM manuals:

  • SC26-4045, VS COBOL II Application Programming Guide.
  • SC26-4889, DB2 Application Programming and SQL Guide.
  • SC26-4890, DB2 SQL Reference.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

IBM Mainframe Topics