马马马加起来是什么字

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

Implementing static SQL in DB2

When the complete SQL statement is known at pre-compile time, it is known as static SQL. All aspects of the SQL statement, such as the table name, column names, filter criteria, and so on must be specified at pre-compile time. The only thing that can be passed at the time of execution are host variables. Even if we have host variables, the complete information such as data type, length, and so on, must be available at the pre-compile time. Static SQL statements can be embedded in all languages except interpreted languages. At the time of pre-compilation, the language pre-processor converts the static SQL code into database manager run-time service API calls which the compiler can understand. In the case of interpreted languages, there is no concept of pre-processing, so everything becomes dynamic SQL there. In this section, we will see how to implement static SQL.

The advantages of static SQL are:

Relatively simpler programming effort
End user doesn't need a direct privilege to execute SQL statements because DB2 uses the authorization ID of the person binding the application.
Static SQL statements are persistent, which means that the statements last for as long as the package exists.
Static SQL statements are precompiled and are ready for execution. This provides performance benefits as it saves SQL compilation time.

Getting ready

You can only implement static SQL if you know the complete SQL statement at pre-compile time.

We'll also need a host language which can be precompiled. We will use SQLJ for this recipe.

How to do it...

Let's see how static SQL can be implemented in SQLJ. The following example assumes that we have a database named SAMPLE. We will use the below query in our example:

SELECT EMPNO, FIRSTNME, LASTNAME, SALARY INTO :empNo:firstName:lastName:salary FROM EMPLOYEE WHERE EMPNO = '200340'

Implementing static SQL mainly involves the following steps:

Connecting to the database: Before we can execute any SQL against the database, we need to establish a connection. In SQLJ, we first need to load the database driver, and then create a connection object and a connection context 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);
Use setDefaultContext() method to set the default context
DefaultContext ctx = new DefaultContext(con);
DefaultContext.setDefaultContext(ctx);

Declaring the host variables: In this section, we will declare all the host variables that we need. Because we want to fetch the employee name, first name, last name, and salary from the EMPLOYEE table, we need four host variables:
```
String empNo, firstName, lastName; Float salary; 
```
Executing the SQL statements: This is the most crucial section. In this section, we will execute SQL statements statically. To do that, we need an SQL statement, which is complete in nature. All the statements that are enclosed in #sql {} are preprocessed by the pre-compiler and are replaced with the corresponding language API calls that the compiler can understand.
```
#sql { SELECT EMPNO, FIRSTNME, LASTNAME, SALARY INTO :empNo, :firstName, :lastName, :salary FROM SANJU.EMPLOYEE WHERE EMPNO = '200340'}; 
```

Processing the results if needed: Once we have the results from the SQL statement, we can process them as needed:

System.out.println ("\nEmployee Num: " + empNo +
"\nFirst Name: " + firstName +
"\nLast Name: " + lastName +
"\nSalary: " + salary);

Disconnecting from the database: Once we are done with the application, we need to disconnect the database connection. Use the close() method to disconnect from the database:

con.close();

Sample output for the previous example is shown next:

How it works…

When a static SQL statement is prepared, an executable form of the statement is created and stored in the database package. The package contains access plans selected by the DB2 optimizer for the static SQL statements in our application. The access plan contains the information required by the database manager to issue the static SQL statements in the most efficient manner as determined by the optimizer. The executable form can be constructed either at the pre-compile time, or at a later bind time. In either case, the preparation occurs before runtime. Because the access plan is created at pre-compile time, the DB2 optimizer uses the statistics available at that time.

There's more…

Starting with DB2 9.7, the access plan for static SQL can be reused. DB2 provides the mechanism to lock down the access plans for static SQL. Once an access plan is locked down, the DB2 optimizer attempts to reuse the same access plan across binds or rebinds, resulting in a predictable performance. The users can also ensure very similar performance across the fix pack upgrades and statistics updates. They can also expect some configuration-parameter changes as well. The locked access plan might not be the best access plan (maybe after statistics are updated), but still, it will ensure the same performance as earlier. The following is applicable for an access plan reuse:

To enable access plan reuse, use the ALTER PACKAGE command or use the APREUSE option for BIND, REBIND, and PRCOMPILE commands.
If significant changes are made to the schema or the environment, then it might not be possible to reuse the same access plan. Such changes could be dropping of an index, recompiling the statement at a different optimization level, and so on.
The statement-level optimization guideline takes precedence over access plan reuse.
A statement profile with empty guideline can be used to disable access plan reuse for a particular statement, keeping it still available for other statements.
If the access plan cannot be reused, a warning is raised (SQL20516W).

The isolation level for static SQL statements is specified as an attribute of a package and applies to the application processes that use that package. It can be specified by using the ISOLATION option in BIND or PRECOMPILE commands. The isolation clause in a select statement overrides all other isolation levels.

Over a period of time, static SQL packages become inefficient due to old statistics. In such cases, we can REBIND the package after updating the statistics. REBIND will create a fresh access plan based on the latest statistics.

If the static SQL statements has host variables or global variables, we can defer the compilation of these statements till EXECUTE or OPEN. We can use the REOPT bind option to specify when to compile such statements. The advantage of this approach is that the statements will be compiled with real values of the variables, which mean 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 the 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 the recompilation time.

We can use the DEGREE option of BIND and PREP commands to control the degree of intra-partition parallelism for static SQL.

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

IBM DB2 9.7 Advanced Application Developer Cookbook

Implementing static SQL in DB2

Getting ready

How to do it...

How it works…

There's more…