Merge utility

This topic contains the following sections:

• Overview
• Syntax of the Merge command
• Descriptions of options
• Notes about the merge process

Overview

There are two ways that you can merge pureQueryXML files.

Use a pureQueryXML file as a base file when you want the file that results from the merge to preserve the statement sets from that file. SQL statements from the other files in the merge go into an unnamed statement set that pureQuery creates at the end of the resulting file.

For example, you might capture SQL statements from an application in a file called capture.pdqxml. You modify this file to arrange the SQL statements in the file into the statement sets that you want either by hand or by using the Configure utility. The capture.pdqxml becomes the base file when using the Merge utility.

Recommendation: IBM® strongly recommends that you edit pureQueryXML files by using the workbench only. If you edit a pureQueryXML file outside of the workbench, you risk making the file unusable.

If you must edit a pureQueryXML file outside of the workbench, in the <statementSet> tag of the statement set in which an edit occurs set the value of the configureStatus attribute to REQUIRED, like this: configureStatus="REQUIRED" Then, run the Configure utility on the file.

A statement set within a pureQueryXML file is a set of statements that form the basis of one or four DB2® packages, depending on whether you want a package for one isolation level or one package for each of the four isolation levels.

For Java™ applications, you run the Configure utility on capture.pdqxml, so that the Configure utility can name the statement sets. You then give copies of this file to other members of your team so that they can incrementally capture statements from other logical paths in the same application. Each member uses their copy as an input file, specifying the file as the value of the pureQueryXml property. Each member sets the value of the outputPureQueryXml property to the name of the file to contain the additional SQL statements that they capture. Those team members give you their output files, which you run the Merge utility on to create a new file that contains the SQL statements from the files that contributed to the merge. If you open the new file, you see that it still contains the statement sets that were in the base file. The SQL statements from the output files that your team members gave you are in a new, unnamed statement set that is at the end of the new file.

You can merge two or more pureQueryXML files without specifying any of them as a base file.

In the file that results from the merge, the Merge utility combines the SQL statements from all of the pureQueryXML files into a single, unnamed statement set.

Note: If the -baseFile option is not specified, the first file in the list of pureQueryXML files is used as the base file. The file cannot be a pureQueryXML file that was created to capture additional SQL statements when both the outputPureQueryXml and the pureQueryXML properties were specified. When both properties are specified, the outputPureQueryXml contains captured SQL statements that do not appear in the pureQueryXML file that is specified by the pureQueryXML property.

Syntax of the Merge command

    (1)                                                       
>>-------java com.ibm.pdq.tools.Merge--+------------------+----->
                                       '- -baseFile--file-'   

                          .------.      
                          V      |      
>--+- -inputPureQueryXml----file-+--+--------------------------->
   '- -inputPureQueryXmlGroup--file-'   

>-- -outputPureQueryXml--file--+-----------------------+-------->
                               |                   (2) |   
                               '-| Trace options |-----'   

>--+--------------------------+--------------------------------->
   |                .-FALSE-. |   
   '- -showDetails--+-TRUE--+-'   

>--+--------------------------------------+--------------------->
   '- -removeSQLInactiveForDays----days---'   

>--+--------------------------+--+--------+--------------------><
   |                .-FALSE-. |  '- -help-'   
   '- -validateXml--+-TRUE--+-'               

1. You can specify the options in any order.
2. For the syntax, see the description of these options.

To understand the conventions that are used in the diagram, see How to read syntax diagrams.

Descriptions of options

-baseFile file

Specifies the pureQuery file that contains the statement sets that you want to preserve in the file that the Merge utility creates, if you want to preserve any statement sets. You must run the Configure utility on this file before you run the Merge utility on it.

If an SQL statement in one of the files that you merge together performs positioned updates, and the statement that declares the cursor for those positioned updates is not in the same file, the Merge utility looks for the declaration of the cursor in the files that you are merging.

For example, suppose that you captured SQL statements from a clustered Web application and produced the three pureQueryXML files A, B, and C. For the merge process, you use file C as the base file. File A contains SQL statement S1, which performs a positioned update. However, file A does not contain SQL statement S2, which declares the cursor that S1 uses. Therefore, the Merge utility looks in files B and C for an SQL statement that declares this cursor. If such a statement does not exist in files B or C, the Merge utility does not merge the positioned update statement S1, and a logs a warning.

If you do not specify a base file, in the file that results from the merge the Merge utility combines the SQL statements from all of the pureQueryXML files into a single, unnamed statement set.

Note: If you do not specify a base file, the first input pureQueryXML file listed is the default base file. The file cannot be a pureQueryXML file that was created to capture additional SQL statements when both the outputPureQueryXml and the pureQueryXML properties were specified. When both properties are specified, the outputPureQueryXml contains captured SQL statements that do not appear in the pureQueryXML file that is specified by the pureQueryXML property.

-inputPureQueryXml file

Specifies the pureQueryXML files to merge together, the names separated by spaces. You can specify multiple absolute or relative paths. If you specify a relative path, the path must be relative to the directory where you run the command.

If the path or name of a file contains a space, enclose the entire entry for the file in quotation marks.

To specify multiple files, the file name can contain the * wildcard character.

-inputPureQueryXmlGroup file

Specifies the pureQueryXML files to merge together. The file must have the extension .grp. You can specify one file name on each line of the file, giving each an absolute or a relative path. If you specify a relative path, the path must be relative to the directory where you run the command.

If the path or name of a file contains a space, you do not need to enclose the entire entry for the file in quotation marks.

-outputPureQueryXml file

Specifies the name of the new file that you want the Merge utility to create from the pureQueryXML files that you specify in the preceding options. You can specify an absolute or relative path. If you specify a relative path, the path must be relative to the directory where you run the command.

-removeSQLInactiveForDays days

Specifies the number of days after which an unused SQL statement is removed based the statement timestamp. If the days variable is 0, all SQL statements are written to the output file. The default setting is to write all SQL statements to the output file. If the days variable is a positive number, the Merge utility does not write SQL statements to the output file if the statement's last used timestamp is the number of days prior to the current date or earlier, inclusive. If the days variable is 1, SQL statements are not written if the timestamp is the previous day or earlier.

For example, if the days variable is 5, and the current date is 11/25, November 25, SQL statements with a timestamp 11/20, November 20 and earlier, are not written.

If the SQL statement does not have a timestamp, the SQL statement is written to the output file with timestamp information using the current time. For example, if a previous version of a pureQueryXML file is an input file, a SQL statement does not have a timestamp. For a previous version of a pureQueryXML file, version 3 or earlier, the option is ignored, all SQL statements are written to the output file with the current time timestamp, and a message is displayed.

If the statement set has a configureStatus of FINAL, statements that meet the criteria for inactive days are written to the output pureQueryXML file, and the Merge utility writes a message to the log file with the statement and reason for keeping the statement.

A message is written to the log file for the SQL statements that are not written to the output pureQueryXML file. If the -showDetails option is true, all of the statements that have not been written to the output file are displayed.

Note: SQL statements are not written to the output pureQueryXml file, but the statements are not removed from DB2 packages that have previously been bound from the statement set. Future bind operations using the statement set will not bind the removed SQL statements, but might leave an empty section in the package. One method to remove empty sections is to use the Configure utility with -cleanConfigure option on the pureQueryXML file. Then use the StaticBinder utility to create and bind packages from the pureQueryXML file.

-showDetails TRUE|FALSE

Specifies whether the Merge utility displays a summary of its activity.

The default value is FALSE.

Trace options

You can specify the file to log messages in and the level of information to log.

>>-+------------------------+--+---------------------------+---><
   '- -traceFile--file-name-'  |               .-OFF-----. |   
                               '- -traceLevel--+-ALL-----+-'   
                                               +-SEVERE--+     
                                               +-WARNING-+     
                                               +-INFO----+     
                                               +-CONFIG--+     
                                               +-FINE----+     
                                               +-FINER---+     
                                               '-FINEST--'     

-traceFilefile-name

Specifies the absolute or relative path and name of the file to use for logging information about the operation.

If the file already exists, pureQuery® appends new messages to the existing content of the file. As the default, the entries are written to System.err.

-traceLevel OFF|SEVERE|WARNING|INFO|CONFIG|FINE|FINER|FINEST|ALL

Specifies the type of information to log. The default level is OFF. If you do not specify a file in which to write the log entries and you set this option to any value other than OFF, the entries are written to System.err or the Console view.

-validateXml TRUE|FALSE

Specifies whether XML schema validation is performed on the input pureQueryXML files using the pureQueryXML schema. If the value is TRUE, validation is performed. If the value is FALSE or if the option is not specified, validation is not performed. The default value is FALSE.

If a pureQueryXML file fails XML schema validation, the merge operation fails. The file name and error that caused the schema validation failure will be reported. Subsequent files will not be validated. The first validation error is reported and the merge failure is reported.

Only current release or previous version pureQueryXML files are validated (version 4 or 3). If an earlier version of pureQueryXML is detected, validation is not performed on that file.

-help

Displays summary usage information. Not valid in an options file.

Notes about the merge process