DB2 package management with pureQuery client optimization

With pureQuery Runtime, you can use package collections and package versions to manage DB2® packages when an application that is enabled with pureQuery client optimization executes SQL statements statically on a DB2 database.
In the pureQuery client optimization process, the bind step creates DB2 packages on the database server. A DB2 package stores SQL information that is needed for static execution of an application at a DB2 server. A set of one or more packages on a DB2 database contains the SQL statements that are executed by the application. pureQuery Runtime maps packages to the SQL statements in the pureQueryXML file that is used by the application enabled with pureQuery client optimization. The binding process also performs the following tasks:
  • Determining security requirements
  • Verifying the existence of database objects
  • Determining access path selection, index usage, and in what order tables should be joined
  • Determining if query rewrites are needed
  • Determining if work files are required
  • Evaluating whether parallelism can be used
By setting Configure utility options and BIND options appropriately and with proper matching of the pureQueryXML files, you can implement flexible implementations. Many static applications that are used for different purposes can coexist in a single environment, for example:
  • A single application that uses unqualified table names can execute within the same system against different sets of tables.
  • Multiple application instances can run against different sets of tables concurrently.
  • Multiple versions of a production application can execute against the same tables with the same package names but different package versions.
Important: If your application SQL statements reference multiple locations by using three-part table names, you must bind the package at all of the referenced locations.

Package collections

You can create a collection of packages, which is a named set of packages, on the DB2 database. You use the pureQuery Configure utility with the -collection option to specify collection name for the SQL statements in a pureQueryXML file. Then, you run the pureQuery StaticBinder utility on the configured pureQueryXML file. To bind the SQL statements in the pureQueryXML file into multiple collections, you can invoke the StaticBinder utility with the -bindOptions "COLLECTION(collection-name)" option.

Package versions

During the bind step of the pureQuery client optimization process, the default bind action is to replace an existing package. The new package is bound with the same three package identifiers: location, collection ID, and package name.

During a BIND operation, a package exists in the target server location with the same location, collection ID, and package name, prior packages will be removed. Only the new package will exist with the newly updated consistency token. After this action occurs, previous versions of the application (which, for pureQuery client optimization means a previous version of a pureQueryXML file) with a different consistency token will not be able to execute.

To allow multiple versions to exist in a system concurrently, without the application providing the logic for each version, you can use the -pkgVersion option when you run the Configure utility. Specifying the -pkgVersion option creates a copy of the pureQueryXML file with a version tag appended to the file name. Every time Configure is invoked with the -pkgVersion option, a new consistency token is generated and a copy of he file saved with that consistency token.

Note: The version string information is never used by the application or pureQuery Runtime to find the matching package. The version information is used only for managing the number of packages at a server and to allow multiple consistency tokens for the application.

When you use multiple versions of packages at run time, you must maintain separate pureQueryXML files to invoke different package versions. You must ensure that the application that is enabled with pureQuery client optimization is using the correct pureQueryXML file to execute the correct package version on the database server.


Feedback