Creating static SQL by using CLI/ODBC static profiling

You can use the CLI/ODBC static profiling feature to replace dynamic SQL statements with static SQL statements, potentially improving runtime performance and security through the package-based authorization mechanism.

About this task

When you run an application with prebound static SQL statements, dynamic registers that control the dynamic statement behavior have no effect on the statements that are converted to static SQL statements.

If an application issues Data Definition Language (DDL) statements for objects that are referenced in subsequent Data Manipulation Language (DML) statements, all of these statements are in the capture file. The CLI/ODBC static package binding tool command, db2cap, attempts to bind them. The bind attempt is successful only for a database management system (DBMS) that supports the VALIDATE RUN bind option. If the DBMS does not support the VALIDATE RUN bind option, the application should not use static profiling.

You can edit the capture file to add, change, or remove SQL statements, based on application-specific requirements.

The following restrictions apply when you are running an application during the profiling session:

An SQL statement must have successfully run (generated a positive SQLCODE value) for it to be captured in a profiling session. In a statement-matching session, unmatched dynamic statements continue to execute as dynamic CLI/ODBC calls.
An SQL statement must be identical, character-by-character, to the one that was captured and bound to be a valid candidate for statement matching. Spaces are significant, for example, COL = 1 is considered to be different from COL=1. Use parameter markers in place of literals to improve match hits.

Not all dynamic CLI/ODBC calls can be captured and grouped into a static package. Possible reasons why calls are not captured and grouped are as follows:

The application does not regularly free environment handles. During a capture session, statements that are captured under a particular environment handle are written to the capture file or files only when that environment handle is freed.
The application has complex control flows that make it difficult to cover all runtime conditions in a single application run.
The application executes SET statements to change registry variable values. These statements are not recorded. There is a limited capability in match mode to detect dynamic SET SQLID and SET SCHEMA statements and suspend running static statements accordingly. However, other SET statements and subsequent SQL statements that depend on the registry variables being set might not behave correctly.
The application issues DML statements. Depending on application complexities and the nature of these statements, they might not be matched, or they might not execute correctly at run time.

Because dynamic and static SQL are different, always verify the behavior of the application in static match mode. Furthermore, static SQL does not offer improved runtime performance over dynamic SQL for all statements. If testing shows that static execution decreases performance for a particular statement, you can force that statement to be dynamically executed by removing the statement from the capture file. In addition, static SQL, unlike dynamic SQL, might require occasional rebinding of packages to maintain performance, particularly if the database objects that are referred to in the packages frequently change. If CLI/ODBC static profiling does not fit the type of application that you are running, there are other programming methods that you can use to obtain the benefits of static SQL, such as embedded SQL and stored procedures.

Procedure

To create static SQL statements from dynamic SQL statements:

Profile the application by capturing all the dynamic SQL statements that the application issues.
This process is known as running the application in static capture mode. To turn on static capture mode, set the following CLI/ODBC configuration keywords for the CLI/ODBC data source in the db2cli.ini configuration file before running the application:
- StaticMode = CAPTURE
- StaticPackage = qualified_package_name
- StaticCapFile = capture_file_name
An example of the settings follows:
```
[DSN1]
StaticMode = CAPTURE
StaticPackage = MySchema.MyPkg
StaticCapFile = E:\Shared\MyApp.cpt
```
Note: For the StaticPackage keyword, ensure that you specify a schema name (MySchema in the previous sample). If you do not specify a schema, the database object name that you provide is considered to be the container name instead of the package name, and the package name is blank.
The resulting static profile takes the form of a text-based capture file, containing information about the SQL statements that are captured.
The previous example file yields the following results for Data Source Name 1 (DSN1):
- Capture mode will be used.
- The package will be named MySchema.MyPkg
- The capture file, MyApp.cpt, will be saved in the E:\Shared\ directory.
Until you change the StaticMode keyword to a value other than CAPTURE, such as DISABLED(which turns off static capture mode), each subsequent run of the application captures SQL statements and appends them to the capture file (MyApp.cpt in the example. Only unique SQL statements are captured, however; duplicate executions are ignored.
Optional: To generate a CLI/ODBC static profiling log file, set the StaticLogFileCLI/ODBC configuration keyword.
This file contains useful information to determine the state of the statement capturing process.
Run the application.
Unique SQL statements are captured in the capture file. Duplicate statements are ignored.
Disable static capture mode by setting the StaticModeCLI/ODBC configuration keyword to DISABLED or by removing the keywords that you set in the first step.
From the Command Line Processor, issue the db2cap command.
The db2cap command generates a static package that is based on the capture file. If the db2cap command does not return a message indicating successful completion, a statement in the capture file could not be statically bound. Remove the failing statement from the capture file, and run the db2cap command again.
Make a copy of the capture file that you processed with the db2cap command available to each user of the application.
You can give the file to the users or, if all users use the same client platform, place a read-only copy of this file in a network directory that is accessible to all users.
Enable your application for dynamic-to-static SQL statement mapping, known as static match mode, by setting the following configuration keywords:
- StaticMode = MATCH
- StaticCapFile = capture_file_name
An example of settings follows:
```
[DSN1]
StaticMode = MATCH
StaticCapFile = E:\Shared\MyApp.cpt
```
Optional: Set the CLI/ODBC StaticLogFile configuration keyword to log useful information such as how many statements were matched (therefore statically executed) and how many statements were unmatched (therefore dynamically executed) during a match session. You should use this information to verify that static profiling in match mode is yielding an acceptable match ratio.
Run the application.