Static and dynamic execution of SQL statements with pureQuery client optimization

In a DB2® database, you can execute SQL statements statically or dynamically. Static execution requires configuring the database to run an SQL statement but delivers more consistent performance. Dynamic execution of SQL statements is more flexible because it does not require any special preparation on the database.

The difference between static and dynamic execution of SQL statements similar to the difference between running applications that are written in a compiled programming language and an interpreted programming language. When you use a compiled language, the executable code is generated before run time. When you use an interpreted language, the executable code is generated during run time. Similar to running an interpreted language, there is a CPU cost associated with analyzing an SQL statement and creating an access plan. Dynamic SQL execution can hinder application performance. The primary difference between the static and dynamic SQL execution is that static SQL execution requires program preparation and binding before an application ultimately runs. Dynamic SQL execution is prepared during run time. Dynamic SQL execution potentially incurs the extra processing overhead of PREPARE and DESCRIBE each time it executes.

Dynamic SQL statement caches can help avoid the extra overhead of PREPARE and DESCRIBE of SQL statements. An SQL statement cache can be at the application server or on the DB2 database or both. However, achieving high cache hit ratios typically requires careful tuning. The SQL statements must be coded correctly to maximize the chances of a cache hit without wasting the cache memory space. In typical environments, the total optimization is difficult to achieve.

In many cases, static SQL execution can provide faster performance, more consistent response times, and significant security benefits. The SQL statements are all bound into a DB2 database package, so there are no cache misses for an individual SQL statement. The package is given execute permissions so that granting permissions on underlying database objects to all users is not required. Also, workload management goal-based resource allocation defined for static packages and the access paths that are defined when you bind packages help in achieving consistent response times.

Some process planning is required to prepare for the relative differences of a dynamic, SQL-based JDBC application versus a static, SQL-based JDBC application. Although pureQuery client optimization introduces new terms and tools, the basic premise and concepts of managing traditional static application deployments, such as a COBOL or an SQLJ application, also apply to static applications with pureQuery client optimization. Many options which can affect deployment depending on the application scenario, and careful planning and consideration of the options is needed for a successful deployment.

DB2 package identification information

A DB2 package is identified by four components. At program execution time, the pureQuery Runtime uses the four components to identify the package to execute SQL statically on a DB2 database server.
Location name
The name is determined by the target server specified at bind time. For pureQuery client optimization, the location is specified in the database name component of the StaticBinder URL option when the pureQuery StaticBinder utility is invoked.

At run time, the location name value is determined by the application connection information at execution time. Location names for DB2 for z/OS® are equivalent to database names on DB2 for Linux, UNIX®, and Windows®.

Collection ID
This ID is specified during the configure step. The collection ID helps to group a set of related packages together. The SQL statements for an application consist of one or more collections of packages. It is not recommended to bind all packages from different applications into a single collection. Collections should be considered as a group of related modules for an application that are mapped into packages in the DB2 database. The collection ID value should be used to group logically related packages. If the collection ID value is not set, the default value is the string NULLID.

At run time, the collection ID value can be set as a property of the data source that is used to acquire a connection. In the application, the CURRENT PACKAGESET or CURRENT PACKAGE PATH special registers can be set to override the default value. If these special registers are used in the application care must be taken to bind the packages into the collections specified by the special register setting.

Package name
The package name is derived from the statement set name in the pureQueryXML file when the pureQuery StaticBinder utility creates packages on a database. To execute an SQL statement statically at run time, pureQuery Runtime matches the SQL statement with a statement in the pureQueryXML file. pureQuery Runtime uses the statement set name that contains the statement to determine the package name.

When you configure a pureQueryXML file, you specify the base string to use to create the SQL statement set names with the pureQuery Configure utility -rootPkgName option.

At run time, the package name that was created during the configure process cannot be changed.

Consistency token
The token is implemented as a hexadecimal timestamp. The consistency token is determined by the Java™ timestamp when you run the pureQuery Configure utility on a pureQueryXML file.

You can use the same pureQueryXML file to create packages on different target databases. If you use the same pureQueryXML file to create packages on different target databases, the same consistency token is used at the different target databases to identify the package at run time.

If the consistency token is changed in the pureQueryXML file after creating packages on a target database, pureQuery Runtime will not use the packages on the database to run SQL statements statically.

At run time, the value is fixed. After you have created packages on a database by running the StaticBinder utility with a pureQueryXML file, you should not change the consistency tokens in the pureQueryXML file. If you change a token, pureQuery Runtime will not be able to identify the package associated with the SQL statement in the file.

The mapping between captured SQL data and DB2 packages

pureQuery Runtime determines when to execute an SQL statement statically by comparing the SQL statement and SQL information such as SQL string, cursor, and prepare attributes such as the result set type, concurrency, holdability. When executing SQL statements statically, pureQuery Runtime maps SQL statements that are issued by the application to the location, collection ID, package name, and consistency token information in the pureQueryXML file. pureQuery Runtime uses that mapping to access the DB2 package containing the SQL statements and executes the SQL statement statically.

With the pureQuery Configure utility, you specify characteristics of the DB2 packages that you will create that contain the SQL statements. The characteristics include the collection ID, and statement set that the statement belongs to. These characteristics are stored in the pureQueryXML file. The Configure utility also stores a consistency token with the package information.

The pureQuery StaticBinder utility uses the statement set name in the pureQueryXML file, collection ID, and consistency token, to create the package identifier on the DB2 database. The location is determined by the target server that you specify when you run the StaticBinder utility. The package name is based on the statement set name. For information about how the StaticBinder utility creates a package name from a statement set name, see the -rootPkgName option in Configure utility

You use Configure utility and the StaticBinder utility to create packages on a DB2 database server. You can specify Configure and StaticBinder options to create package collections and packages versions to manage packages on a DB2 database server.


Feedback