Scenario: Migrating pureQuery client optimization from a test environment to a production environment

You enable an application with pureQuery client optimization in a test environment. After you complete your test requirements, such as testing the application functionality and performance, and reviewing the SQL statements and bind procedures, you are ready to deploy the application in a production environment.
This topic describes how to move or create the packages to be bound to database in the production environment and contains the following sections:

In the ideal environment, you run the application that is enabled with pureQuery client optimization, capture SQL data, and test the execution of the SQL statically running in an environment that is identical to the production environment. However, having identical test and production environments might not be possible. For example, test systems might use DB2® for Linux, UNIX and Windows, while the production environment uses DB2 for z/OS®.

When migrating an application that is enabled with pureQuery client optimization, you might be required to change characteristics of the DB2 packages such as the package and collection names that are in the pureQueryXML file. After you run the Configure utility on the pureQueryXML file with production-specific options, you can use the StaticBinder utility to create the packages in the production database and bind the packages to the database.

You also migrate the pureQuery Runtime properties and pureQueryXML data to be used in the production environment. You might need to modify the finalRepositoryProperties property, the captureMode property, and the allowDynamicSQL property.

If you use a repository, you also create a repository in the production environment, create the runtime group version for the application, and upload pureQuery Runtime property information and the pureQueryXML file to the repository.

Example of migrating SQL statements with the pureQuery Configure and StaticBinder utilities

In the previous tasks in the scenario, you configured an application with pureQuery client optimization on a test system. After testing is completed, you now want to bind the SQL statements in the pureQueryXML file to the production database. The following examples assume that the production collection is PRODCOL and QUALIFIER PROD. Accordingly, you will update the data source with the correct packagePath setting so that the packages are recognized. Also, the examples in this topic assume that the production database uses the package name PRODPKG package naming convention.

As part of the migration, you retrieve the pureQueryXML file created in the test environment. If the file is in a repository, you extract the file with the pureQuery ManageRepository utility. You run a command similar the following one:
java com.ibm.pdq.tools.ManageRepository 
  -extract runtimeGroup 
  -repositoryDriverClass com.ibm.db2.jcc.DB2Driver 
  -repositoryURL "jdbc:db2://testserver.test.com:32706/sample" 
  -repositoryUsername "myuser" -repositoryPassword "mypwd" 
  -runtimeGroupId testApp -runtimeGroupVersion V2 
  -outputDirectory C:\TEMP\out 
  –pureQueryXml prodApp.pdqxml 

After you retrieve the file, you run the pureQuery Configure and StaticBinder utilities on the pureQueryXML file with the appropriate options.

Running the following Configure utility changes the root package name to PRODAPPL and changes the default collection to PRODCOL. The option -cleanConfigure true specifies that all the SQL statements in the pureQueryXML file are processed, which includes any existing SQL statements in addition to new SQL statements. With the option -validateXml true, the Configure utility ensures that the input file is a valid pureQueryXML file by performing XML schema validation on the file:
java com.ibm.pdq.tools.Configure -pureQueryXml prodApp.pdqxml 
  –validateXml true
  –collection PRODCOL 
  -rootPkgName PRODAPPL 
  -cleanConfigure true
Running the StaticBinder utility against the target database server with the following options creates packages that contain SQL statements from the pureQueryXML file in the database and binds the packages:
java com.ibm.pdq.tools.StaticBinder 
  –url jdbc:db2://prodserver.prod.com:446/STLEC1” 
  -username "myuser" -password "mypwd" 
  -isolationLevel CS 
  -bindOptions "QUALIFIER PROD" 
  -pureQueryXml prodApp.pdqxml 
  -showDetails true

When you specify the option -showDetails true, the StaticBinder utility displays summary information regarding the DB2 packages that it produces.

The StaticBinder option -isolationLevel CS specifies that the StaticBinder create a single package with isolation level cursor stability (CS). The CS isolation level is the default level for DB2 for Linux, UNIX and Windows databases.

The output to the StaticBinder command is similar to the following example:
The StaticBinder utility is beginning to bind the pureQueryXml file capture.xml.

Starting to process options : -username "*****" -password "*****" 
 -url "jdbc:db2://prodserver.prod.com"
 -pureQueryXml prodApp.pdqxml 
 -isolationLevel "CS" 
 -bindOptions "QUALIFIER PROD" 
 

The StaticBinder utility successfully bound the package 'PRODAPPL2' for the isolation level 'CS'.

Changes to Static SQL identification information when migrating to a production environment

When migrating an application to a production environment, static SQL identification information such as consistency tokens and package versions might require changes.

Changes to consistency tokens and package versions
To migrate a DB2 package to a different DB2 subsystem or database while maintaining the same consistency token and package version information, run the StaticBinder utility with the pureQueryXML file to bind to additional DB2 databases or subsystems. During the migration, you can ensure that consistency tokens in the pureQueryXML file and in the DB2 package versions continue to match if you do not modify the pureQueryXML file. If you modify the file and use the Configure utility, the consistency token might change. For information about consistency tokens and DB2 package versions, see DB2 package identification information.
Note: The pureQuery Configure utility changes consistency tokens when changes to statement sets are required. If you need to configure the pureQueryXML file and do not want to change consistency tokens, you must ensure that the Configure utility does not change existing, named statement sets in the pureQueryXML file. For example, do not configure the file with the -setPreStatusOfAllPkgs REQUIRED option or with the -cleanConfigure trueoption.
Changes to JDBC properties
If the collection ID differs on the test system and production system, you must update the corresponding runtime environment to specify the correct collection during the migration of packages between DB2 subsystems, The collection is specified as part of the WebSphere® data source definition with a currentPackageSet property (or currentPackagePath property for DB2 for Linux, UNIX and Windows or DB2 for z/OS).

Example

At run time, if the WebSphere JDBC provider custom property currentPackageSet is not set to PRODCOL1B on the WebSphere Application Server data source, the collection PRODCOL in the pureQueryXML file is used. The package will not be found on STLEC1B and SQLCODE 805 will be returned to the application.

If you have dynamic SQL statements, then you must specify the data source property currentPackagePath. If JDBC dynamic packages are bound to collection NULLID you need to set the value of the currentPackagePath property on the WebSphere Application Server data source to PRODCOOL1B,NULLID.

Migrating on a DB2 for z/OS database
In addition to the StaticBinder utility, DB2 for z/OS provides the DSN remote BIND COPY command, which can be used to rebind the package on a different DB2 for z/OS system. You issue the BIND PACKAGE command with the remote location name in the BIND PACKAGE argument and specify the COPY option.

Example

If the SQL statements in the pureQueryXML file are bound to a location for testing, for example, STLEC, with the collection PRODCOL. You can use the BIND COPY command to COPY the package to the production location STLEC1B with collection PRODCOL1B


Feedback