You can merge two or more pureQueryXML
files together that contain SQL statements that were captured from
an application and that run against a single data source. XML
schema validation can be performed on the input pureQueryXML files,
and unused SQL statements can be eliminated from the output merged
file.
This topic contains the following sections:
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.
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.
Notes about the merge process
The following
list are notes about the merge process:
- When merging files, the files can contain the same SQL statement
with different attributes. The following list describes how the Merge
utility handles identical SQL statements with different attributes.
The descriptions assume that STMT_A and STMT_B are identical SQL statements
and that STMT_A is in the base file and STMT_B is in a file that is
merged into the base file:
- If the parameterType attribute for STMT_A is set to SINGLE_ROW_PARAMETERS
and the attribute for STMT_B is set to MULTI_ROW_PARAMETERS, the attribute
for STMT_A is set to MULTI_ROW_PARAMETERS.
- If the allowStaticRowsetCursors attribute for STMT_A is set to
false and the attribute for STMT_B is set to true, the attribute for
STMT_A is set to true.
- If the isBindable attribute for STMT_A is set to true and the
attribute for STMT_B is set to false, the attribute for STMT_A is
set to false.
- If a user defined cursor is not defined for STMT_A and it is defined
for STMT_B, the cursor name in STMT_A is set to the one defined in
STMT_B.
If the attribute values are reversed for STMT_A and STMT_B,
the attribute value for STMT_A is not changed, and a message is written
to the log file.
Moreover, if two statements in different pureQueryXML
files are identical in every respect except for their stack trace
entries, the differing stack trace entries are combined for the resulting
single statement in the file that results from the merge.
- If the file that results from the merge contains any unnamed statement
sets, you must run the Configure utility on that file before you can
run the StaticBinder utility on it.
- IBM strongly recommends
that you run Configure after merging files to ensure that the results
of the merge can be used with the StaticBinder utility.
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--+-'
Notes:
- You can specify the
options in any order.
- 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.
- -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--'
- -traceFile file-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.
- -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.