Scenario: Capturing, retrieving, and merging the SQL data

In this scenario, the application is not complex. You run the application by running the deployed servlet a couple of times to capture SQL statements issued by the application. After you run the application, you extract and process the SQL data captured and stored in the repository.

About this task

For this scenario, the application runs with three application clients. All three application clients write to the repository and create incremental capture records.

You extract the records containing the captured SQL data to files on your local system with the pureQuery ManageRepository utility, merge the captured SQL files to create a single file with the Merge utility. You process the single file with the pureQuery Configure and StaticBinder utilities.

Procedure

To capture, retrieve, and merge the SQL data:

  1. Run the WebSphere® application to capture SQL data.

    Run the deployed application to execute the SQL statements in the application.

    pureQuery Runtime captures SQL data from the deployed application.
  2. Extract the captured SQL data.
    To extract the captured SQL files stored in the repository, run the following command:
    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 V1 
    -outputDirectory C:\TEMP\out 
    –incrementalCapture myCapture 

    The value myCapture for the ManageRepository option -incrementalCapture, causes the names of the captured SQL file names to be myCapture_n.pdqxml, where n is an integer. The files are in the directory C:\TEMP\out.

  3. Extract the pureQueryXML data for the runtime group version.
    Run the following command:
    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 V1 
    -outputDirectory C:\TEMP\out 
    –pureQueryXml baseTestApp.pdqxml 

    The command extracts the pureQueryXML data for the runtime group version and stores the data in the file C:\TEMP\out\baseTestApp.pdqxml. The file contains only the base pureQueryXML information; it does not contain any SQL data.

  4. Run the Merge utility on the incremental capture files and the file baseTestApp.pdqxml. The Merge utility combines the data from the pureQueryXML files into a single pureQueryXML file. The output file can be used by the pureQuery Configure utility.
    The following Merge utility command uses the wildcard character to merge the captured SQL files:
    java com.ibm.pdq.tools.Merge 
       -inputPureQueryXml C:\TEMP\out\myCapture_*.pdqxml 
       -baseFile C:\TEMP\out\baseTestApp.pdqxml
       -outputPureQueryXml C:\TEMP\out\testApp.pdqxml

    You must run the Merge utility to merge the incremental capture files and create a file that can be processed by the Configure utility. The Configure utility cannot directly configure the pureQueryXML files that are created when extracting the captured SQL from a repository.

    The Merge utility creates the file testApp.pdqxml from the input files.

  5. Delete the captured SQL data from the repository.
    Run the following ManageRepository command:
    java com.ibm.pdq.tools.ManageRepository 
       -delete incremental 
       -runtimeGroupId group1 
       -runtimeGroupVersion V1 
       -incrementalCapture myCapture
       -inputDirectory C:\TEMP\out 
    The ManageRepository utility compares the data in the pureQueryXML files that have the prefix myCapture with the captured SQL data in the specified runtime group version. If the data matches, the data in the repository is deleted.

Results

You created a pureQueryXML testApp.pdqxml in the current directory that contains the data from the captured SQL files.

What to do next

In the next task, you will configure the pureQueryXML file to prepare the SQL statements in the file to be bound to the target database. You will also create packages that contain the SQL statements and bind the packages to the database.


Feedback