The GeneratePureQueryXml utility generates a pureQueryXML
file from an input file. The input file can be an SQL script file
that contains statements and cursor attribute information or an InfoSphere™ Optim™ Query Workload Tuner XML file.
The GeneratePureQueryXml utility reads a file containing
SQL statements and information about the statements and creates a
pureQueryXML file. After creating the file, use the Configure utility
to prepare the file for use with the pureQuery Runtime. After configuring
the pureQueryXML file, use the StaticBinder utility to create and
bind DB2® packages that contain
the SQL statements in the configured pureQueryXML file.
This
topic contains the following sections:
Syntax of the command

(1)
>>-------java--java com.ibm.pdq.tools.GeneratePureQueryXml------>
>-- -username--user-ID-- -password--password-- -url--JDBC-URL--->
>--+---------------------------------------------+-------------->
| .-com.ibm.db2.jcc.DB2Driver-. |
'- -driverName--+-JDBC-driver---------------+-'
>-- -pureQueryXml--fileName-- -inputSql--fileName--------------->
>--+------------------------------------------+----------------->
| (2).- - - ------------. |
'- -commentStart------+-commentIndicator-+-'
>--+---------------------------------------+-------------------->
| .-NOT_SET-. |
'- -sqlLiteralSubstitution--+-DISABLE-+-'
'-ENABLE--'
>--+-------------------------------------+---------------------->
| .-;---------. |
'- -statementDelimiter -+-delimiter-+-'
>--+------------------------------------------------------+----->
| .-HOLD_CURSORS_OVER_COMMIT-. |
'- -resultSetHoldability -+-CLOSE_CURSORS_AT_COMMIT--+-'
>--+----------------------------------------------+------------->
| .-CONCUR_READ_ONLY-. |
'- -resultSetConcurrency--+-CONCUR_UPDATABLE-+-'
>--+--------------------------------------------+--------------->
| .-TYPE_FORWARD_ONLY-----. |
'- -resultSetType--+-TYPE_SCROLL_SENSITIVE-+-'
'-TYPE_SCROLL_SENSITIVE-'
>--+-----------------------+--+--------+-----------------------><
| (3) | '- -help-'
'-| Trace options |-----'
Notes:
- You can specify the
options in any order.
- The default value is two dashes (--)
- 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.
Description of options
- -username user-ID
- The user-ID specifies the user ID to use for
authenticating with the database.
- -password password
- Specifies the password to use for authenticating with the database.
- -url JDBC-URL
- Specifies the JDBC URL to use to establish a connection with the
database.
- -driverName JDBC-driver
- Specifies the fully-qualified name of the JDBC driver to use for
establishing a connection to the database. The default value is com.ibm.db2.jcc.DB2Driver,
the driver for the IBM® Data
Server Driver for JDBC and SQLJ.
- -pureQueryXml file
- Specifies the absolute or relative path of the output pureQueryXML
file.
- -inputSql file
- Specifies the absolute or relative path of an SQL script file
with the extension .sql or an XML file with the extension .xml.
For
information about the format of the SQL script file, see Format of the SQL script file used by the GeneratePureQueryXml utility.
If an
XML file is specified, the file must be a valid InfoSphere Optim Query
Workload Tuner file. An error is reported if the XML file is not valid.
For information about exporting an Query Workload Tuner file, see
the InfoSphere Optim Query Workload Tuner documentation.
When capturing SQL statements, InfoSphere Optim Query Workload Tuner support
the following features:
- For DB2 for Linux, UNIX and Windows databases, you can capture
SQL statements from the package cache.
- For DB2 for z/OS® databases, you can capture SQL statements
from the statement cache. You can exclude SQL statements run by InfoSphere Optim Query Workload Tuner by enabling the
option Exclude dynamic queries issued by the capture process.
If an SQL statement is in the input file multiple times,
the SQL statement appears only once in the output pureQueryXML file.
For information on how duplicate SQL statement are determined, see Determining duplicate SQL statements in an SQL file.
- -commentStart commentIndicator
- For an SQL script file, specifies the character combination that
appear at the beginning of a line to indicate a comment line. A comment
line can contain statement cursor attributes that apply to the SQL
statement immediately following the comment. The default is two dashes
(--). The start and end comment characters /* and
*/ are not supported for this option.
This option
is not supported if the input file is an XML file. A warning message
is displayed stating that the specified option is ignored.
- -sqlLiteralSubstitution ENABLE|DISABLE|NOT_SET
- Specifies whether the GeneratePureQueryXml utility replaces literal
values in SQL statements with parameter markers. The default value
is NOT_SET, literal values are not replaced in
the SQL statements. The SQL literal substitution process is identical
to the substitution process that occurs when pureQuery Runtime captures
SQL statements with SQL literal substitution enabled.
The following
list describes the supported values:
- DISABLE
- Specifies that the utility does not perform literal substitution.
The SQL statements are not modified.
The value of SQL literal substitution
attribute in the output pureQueryXML file is set to DISABLE.
- ENABLE
- Specifies that the utility replace literals in the SQL statements
with parameter markers. SQL literals are not replaced if the utility
determines that replacing the literal would affect the results of
the SQL statement. When performing SQL literal substitution, comments
are removed from the statements. For SQL statements where SQL literal
replacement is performed, multiple white space characters, including
leading and trailing space characters, are replaced with a single
white space character.
The utility removes duplicate SQL statements
that were created during the SQL literal substitution process.
The
value of SQL literal substitution attribute in the output pureQueryXML
file is set not set.
- NOT_SET
- Specifies that literal substitution is not performed for this
file. This is the default value.
The output pureQueryXML file is
created without setting the SQL literal substitution attribute.
When pureQuery Runtime captures SQL statements
in a pureQueryXML file , the SQL literal substitution setting in the
file is the value of the pureQuery Runtime property sqlLiteralSubstitution when
the statements were captured.
For information about the pureQuery
Runtime SQL literal substitution, see the sqlLiteralSubstitution property.
For information
about SQL literal substitution attribute when pureQueryXML files are
merged, see the Merge utility.
- -statementDelimiter delimiter
- Specifies the character combination used in the SQL script file
that separates statements. Default character is a semicolon (;).
This
option is not supported if the input file is an InfoSphere Optim Query
Workload Tuner XML file. A warning message is displayed stating that
the specified option is ignored.
- -resultSetHoldability holdability
- Specifies the result set holdability to apply to all statements
in the file. The holdability is one of the following
values:
- CLOSE_CURSORS_AT_COMMIT sets the result set
holdablity to close at commit. This value sets the JDBC value java.sql.ResultSet.CLOSE_CURSORS_AT_COMMIT.
- HOLD_CURSORS_OVER_COMMIT sets the result set
holdablity to hold cursors over commit. This value sets the JDBC value java.sql.ResultSet.HOLD_CURSORS_OVER_COMMIT.
The default value is HOLD_CURSORS_OVER_COMMIT.
In
SQL script files, the holdability value can be overridden for a specific
SQL statement in the file by specifying the holdability in a comment
line immediately preceding the SQL statement.
For Query Workload
Tuner XML files from DB2 for z/OS, the result set holdability
for the initial prepare of an SQL statement is stored in the file.
When the result set holdability information is in the file, the utility
uses that value for the SQL statement in the output pureQueryXML file.
For information about the result set holdability for
SQL statements in an InfoSphere Optim Query Workload Tuner XML
file, see Notes about the output pureQueryXML file
- -resultSetConcurrency concurrency
- Specifies the result set concurrency to apply to all statements.
The concurrency is one of the following values:
- CONCUR_READ_ONLY sets the cursor concurrency
to be read only.
- CONCUR_UPDATABLE sets the cursor concurrency
to be updatable.
The default value is CONCUR_READ_ONLY.
In
SQL script files, the concurrency value can be overridden for a specific
SQL statement by specifying the concurrency in a comment line immediately
preceding the SQL statement.
For information about setting the
result set concurrency for SQL statements in an InfoSphere Optim Query
Workload Tuner XML file, see Notes about the output pureQueryXML file
- -resultSetType type
- Specifies the result set type to apply to all statements. The
type is one of the following values:
- TYPE_FORWARD_ONLY sets the result set type
to be forward only.
- TYPE_SCROLL_SENSITIVE sets the result set type
to be scroll sensitive.
- TYPE_SCROLL_INSENSITIVE sets the result set
type to be scroll insensitive.
The default value is TYPE_FORWARD_ONLY.
In SQL script files, the result set type value can be overridden for
a specific SQL statement by specifying the result set type in a comment
line immediately preceding the SQL statement.
For information
about setting the result set type for SQL statements in an InfoSphere Optim Query Workload Tuner XML file, see Notes about the output pureQueryXML file
- 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.
- -help
- Displays summary usage information.
Notes about the output pureQueryXML file
The
following list contains usage information about the pureQueryXML file:
- When creating a pureQueryXML file from an SQL script file or an InfoSphere Optim Query Workload Tuner XML file, ensure
that the following criteria is met for each SQL statement:
- The SQL statement text in the file matches the SQL statement issued
by the application. The capitalization and white space of the statements
must match.
- The result set holdability, concurrency, and type settings for
the SQL statement in the file match the settings for the SQL statement
issued by the application.
If the information does not match, pureQuery client optimization
does not match the SQL statement with the statement issued by the
application when the pureQuery runtime executionMode property
is set to STATIC, or when the pureQuery Runtime capturedOnly property
is set to TRUE.
The following items apply
when the input file is a Query Workload Tuner XML file:
- The XML file contains some special register information for SQL
statements. When the GeneratePureQueryXml utility generates a pureQueryXML
file from an XML file, the special register information is included
in the pureQueryXML file as metadata. There are differences between
the special register information captured by pureQuery Runtime and
Query Workload Tuner. the GeneratePureQueryXml includes the special
register infomation that is normally captured by pureQuery Runtime.
When
you configure the pureQueryXML file with the Configure utility, you
can specify the option -groupSQLBySpecialRegisters to
group the SQL statements in the file according to special register
settings. You can also specify the option -optionsFileForBind to
generate a sample StaticBinder options file that contains bind options
based on the special register settings. For information about the
Configure options, see Configure utility.
For
information special register information tracked and recorded by pureQuery
Runtime, see : Tracking DB2 special
register changes with pureQuery client optimization.
- Before adding an SQL statement to the pureQueryXML file, the utility
prepares the statement to collect metadata about it. If a value for
the CURRENT SCHEMA special register is present in the XML file, the
utility uses the value of the CURRENT SCHEMA special register when
performing the prepare.
- If the utility determines that the SQL statement in the XML file
was run statically, the statement is not included in the output pureQueryXML
file.
- The GeneratePureQueryXml utility copies some statistics from
the XML file to the output pureQueryXML file. The statistics in the
input XML file depend on type and version of database and the version
of InfoSphere Optim Query Workload Tuner. These statistics
do not affect how the SQL statements in the pureQueryXML file are
bound or how pureQuery Runtime matches SQL statements with statements
issued from an application.
When the utility does not find time
stamp information for SQL statements, the utility uses the current
date and time.
- Cursor attributes cannot be specified in the XML file. You can
specify cursor attributes with the GeneratePureQueryXml utility options
-resultSetType, -resultSetHoldability,
and -resultSetConcurrency. The options apply to the
all the SQL statements in the XML file.
For XML files from DB2 for z/OS, the result set holdability for the initial
prepare of an SQL statement is stored in the file. When the result
set holdability information is in the file, the utility uses that
value for the SQL statement in the output pureQueryXML file.
pureQuery
Runtime considers the SQL statement and the statement's cursor attributes
when controlling how an SQL statements runs. For example, you can
configure pureQuery Runtime to run SQL statements statically. If the
statement and attributes in the pureQueryXML file do not match the
application's statement and attributes, pureQuery Runtime does not
run the statement statically.
- If the SQL statements in the output pureQueryXML file require
different bind options to run statically, you can use the pureQueryXML
editor in the workbench to group the SQL statements into statement
sets. Then you can specify different bind options for the statement
sets when you run the StaticBinder utility.
You can run the StaticBinder
utility with the option -statementBindError specifying
the value REMOVE or MARK_INVALID.
The StaticBinder utility skips the SQL statements that return an SQL
error during the bind process.
- The output pureQueryXML file is not directly usable with pureQuery
client optimization to execute SQL statements statically. Before using
the StaticBinder utility, you must use the Configure utility to modify
the pureQueryXML file so that it is usable with pureQuery client optimization.
- The output pureQueryXML file does not contain application execution
information such as stack trace information and special register information
that is available with a pureQueryXML file created when capturing
SQL statements from an application that uses pureQuery client optimization.
- The output pureQueryXML file does not contain the following SQL
statement information that is used by pureQuery client optimization
to identify a statement as a match SQL statements at runtime:
- Auto-generated key column names
- Auto-generated key column indexes
- Auto-generated key indicator
If an application that uses pureQuery client optimization
is configured to run SQL statements statically or in captured-only
mode and uses a pureQueryXML file created by the GeneratePureQueryXml
utility, a statement issued by the application will not run statically
if the statement uses any of the three features, even if the text
of the SQL statements is identical.
The following example creates the pureQueryXML file c:\statements.pdqxml
that contains information for statements found in the SQL script file
c:\mystatements.sql. The value of the -
sqlLiteralSubstitution option
is
ENABLE to replace literals in SQL statements
with parameter markers.
java com.ibm.pdq.tools.GeneratePureQueryXml
-username db2admin –password mypass
–url jdbc:db2://localhost:50000/mydb
-sqlLiteralSubstitution ENABLE
–pureQueryXml "C:\statements.pdqxml" –inputSql "C:\mystatements.sql"
Determining duplicate SQL statements
in an SQL file
The GeneratePureQueryXml identifies two SQL
statements as duplicates if the text of the two SQL statements is
identical, excluding the leading and trailing white space, and if
the following attributes are identical:
- result set type
- result set concurrency
- result set holdability
The following two entries in an SQL file are considered
identical. For the SQL statements, only the leading and trailing white
spaces are different:
-- resultSetType=TYPE_FORWARD_ONLY
-- resultSetConcurrency=CONCUR_READ_ONLY
SELECT * FROM EMPLOYEE ;
-- resultSetType=TYPE_FORWARD_ONLY
-- resultSetConcurrency=CONCUR_READ_ONLY
SELECT * FROM EMPLOYEE ;
The following two entries
in an SQL file are not considered identical. The amount of white space
within the SQL statements are different:
-- resultSetType=TYPE_FORWARD_ONLY
-- resultSetConcurrency=CONCUR_READ_ONLY
SELECT * FROM EMPLOYEE;
-- resultSetType=TYPE_FORWARD_ONLY
-- resultSetConcurrency=CONCUR_READ_ONLY
SELECT * FROM EMPLOYEE;
These two entries in an SQL file are not be identical
because they have different cursor attributes specified:
-- resultSetType=TYPE_FORWARD_ONLY
-- resultSetConcurrency=CONCUR_READ_ONLY
SELECT * FROM EMPLOYEE;
-- resultSetType=TYPE_SCROLL_SENSITIVE
-- resultSetConcurrency=CONCUR_READ_ONLY
SELECT * FROM EMPLOYEE;
The following entries are in an input SQL file. The
SQL statements are identical except for the literal values.
SELECT * FROM EMPLOYEE WHERE EMPNO = '000010'
SELECT * FROM EMPLOYEE WHERE EMPNO = '000020'
SELECT * FROM EMPLOYEE WHERE EMPNO = '000030'
If you run the GeneratePureQueryXml utility with the value
of the
sqlLiteralSubstitution option set to
ENABLE,
the literal values are replaced with a parameter marker and the duplicates
created by the replacement are removed. The result is a single SQL
statement that is written to the pureQueryXML file:
SELECT * FROM EMPLOYEE WHERE EMPNO = ?