牛牛做牌基本手法

書名： IBM DB2 9.7 Advanced Application Developer Cookbook
作者名： Sanjay Kumar Mohankumar Saraswatipura
本章字數： 1444字
更新時間： 2021-08-20 15:42:22

Implementing dynamic SQL in DB2

When an SQL statement or some part of it is not known until the execution time, then these statements are executed dynamically. In the case of dynamic SQL, the statement is compiled at runtime. It also means that the access plan is created at runtime, which can benefit from the latest statistics. When the database manager runs a dynamic SQL or XQuery statement, it creates an access plan based on the current system statistics and current configuration parameters. This access plan might change from one execution of the statements, application program to the other.

Dynamic SQL can be used when:

A complete SQL statement or some part of it is not known at compile time
Objects referenced in the SQL statement are not available at compile time
We want to use the latest statistics available to optimize the access plan
We might change the environment such as the changing database or database manager configuration parameters, special registers, and so on, which might improve the performance

How to do it...

To implement dynamic SQL, an SQL statement is first prepared and then executed. This preparation and execution happens at runtime. It is also possible to prepare a statement once and execute it multiple times.

Every language provides the means to prepare and execute SQL statements. In this recipe, we will see how to implement dynamic SQL in Java. We will also see how to use parameter markers.

Creating a database connection: Before we can execute any SQL statement against the database, we need to create a connection object. For that, we need to load the database driver first, and then create a connection object:

Use Class.forName() method to load the database driver.
Class.forName("COM.ibm.db2.jdbc.app.DB2Driver").newInstance();
Use getConnection() method to get the connection.
String url = "jdbc:db2:sample";
Connection con = DriverManager.getConnection(url);

Declaring a variable: Because we are trying to get results from a query, we need variables to store the results. Let's declare a few variables that we might need:
```
String empNo, firstName, lastName; Float salary; 
```
Preparing a statement: To run an SQL statement dynamically, we first need to prepare it. Once a statement is prepared, we can run it multiple times. The SQL statement to be used for preparing the statement need not be available at compile time. We can use the PreparedStatement class to store a prepared statement. We can either use a string or a variable to pass the SQL query to the prepareStatement() method:
```
PreparedStatement pstmt1 = con.prepareStatement("select empno, firstname, lastname, salary from employee where empno = '200310'");
```

Executing a statement: Now, we have a prepared statement. We can execute it as many times as we want. We can use the executeQuery() method to execute a prepared statement and process the results as desired:

ResultSet rs1 = pstmt1.executeQuery();
while (rs1.next())
{
empNo = rs1.getString(1);
firstName = rs1.getString(2);
lastName = rs1.getString(3);
salary = rs1.getFloat(4);
System.out.println("EmpNo: " + empNo +
"; First Name: " + firstName +
"; Last Name: " + lastName +
"; Salary: " + salary);
}

If we have an SQL statement that has to be executed multiple times with different values of variables, then we can use parameter markers. The parameters are identified by a question mark (?) in the SQL statement. Once an SQL statement with parameter markers is prepared, it can be executed against different values for a parameter marker without having to prepare it again:
```
PreparedStatement pstmt2 = con.prepareStatement("select empno, firstname, lastname, salary from employee where empno = ?"); pstmt2.setString(1, "200310"); ResultSet rs2 = pstmt2.executeQuery(); pstmt2.setString(1, "200330"); ResultSet rs3 = pstmt2.executeQuery(); pstmt2.setString(1, "200340"); ResultSet rs4 = pstmt2.executeQuery(); 
```
The sample output for our example looks as follows:

How it works...

DB2 provides Dynamic SQL support statements, which are required to transform the host variable containing SQL text into an executable form. These statements can be directly embedded into any host language. These support statements are:

EXECUTE IMMEDIATE: Prepares and executes a statement that does not use any host variables. This can also be used as an alternative to PREPARE and EXECUTE statements.
PREPARE: Turns the character string form of the SQL statement into an executable form of the statement. It assigns a statement name, and optionally places information about the statement in an SQLDA structure.
EXECUTE: Executes a previously prepared SQL statement. The statement can be executed repeatedly within a connection.
DESCRIBE: Places information about a prepared statement into an SQLDA.

Dynamic SQL statements are prepared from an SQL statement and a statement name. We can also have an SQL statement in a variable. The SQL statements are not processed when the application is pre-compiled. This also means that the SQL statement need not even exist at pre-compile time. Because the host variable information, such as the data type, length, and so on, is not available during the pre-compilation time, and we cannot use host variables in dynamic SQL, we can use parameter markers instead of host variables. A parameter marker is indicated by a question mark (?) or a colon followed by a name (:name). This indicates where to substitute a variable inside the SQL statement. We can have more than one parameter marker in an SQL statement. In such cases, the values of the variables will be substituted in the order of parameter markers.

There's more…

Similar to Static SQL, we can use the REOPT option in BIND or PREPARE to control the access plan generation of dynamic SQL statements. If the SQL statements have parameter markers or global variables, we can postpone the compilation of these statements until OPEN or EXECUTE statements are encountered. The benefit of using the REOPT option is that the statements will be compiled with the real values of the variables, which means the access plan chosen would be better. We can choose between REOPT ONCE and REOPT ALWAYS. With REOPT ONCE, the access plan is cached after the first OPEN or EXECUTE request and will be available for subsequent execution of this statement. With REOPT ALWAYS, the access plan is generated for every OPEN or EXECUTE statement using the latest value for the variables, resulting in even better access plans but at the cost of recompilation time. With REOPT ALWAYS, the statement concentrator is disabled.

DB2 can avoid recompiling a dynamic SQL statement that has been run previously by caching the access section and statement text in the dynamic statement cache. A subsequent PREPARE request for this statement will attempt to find the access section in the dynamic statement cache, avoiding compilation. However, statements that differ in literals used in predicates will not match.

Tip

If the FLUSH PACKAGE CACHE statement is issued, then all the cached dynamic SQL statements are removed. When these statements are invoked again, they are recompiled implicitly.

Statement concentrator for dynamic SQL

The dynamic SQL statements are compiled at the execution time. In an OLTP environment, we have a large number of similar SQL statements executed dynamically. They only differ in the literal values. DB2 9.7 introduced a statement concentrator for dynamic SQL statements, which can share the access plan for such statements. This saves a lot of compilation time. This behavior can be enabled by setting the value for the stmt_conc database configuration parameter as follows:

UPDATE DB CFG FOR SAMPLE USING STMT_CONC LITERALS

This is disabled or set to OFF by default.

When enabled, the following SQL statements will share the same access plan:

SELECT DEPTNAME, MGRNO FROM DEPT WHERE DEPTNO='A00'; SELECT DEPTNAME, MGRNO FROM DEPT WHERE DEPTNO='B01';

Choosing between static and dynamic SQL

Static SQL statements are precompiled while dynamic SQL statements are compiled at runtime which is an overhead. It's a tradeoff between a better access plan and the compilation time. If our SQL query is pretty complex and its execution time is much greater than compilation time, then we might want to use dynamic SQL, as it will add compilation time as an overhead; but in return, it will result in a better access plan and hence lesser execution time, resulting in a better overall response time. In general, if the expected runtime is less than 2 seconds, static SQL can be used, and if it is higher than 10 seconds, then dynamic SQL would be a better choice. For intermediate ones, we can choose either.

Static SQL works better when underlying data distribution is uniform.

If the query has many range predicates, then dynamic SQL would perform better.

If the SQL statement is likely to run multiple times (like more than 10), then dynamic SQL can be preferred, as the compilation time becomes less as compared to multiple execution times.

If the query is very random in nature, then dynamic SQL will be better.

官术网_书友最值得收藏!

IBM DB2 9.7 Advanced Application Developer Cookbook