The GeneratePureQueryXml utility generates a pureQueryXML
file from a SQL script file that contains statements and cursor attribute
information.
The GeneratePureQueryXml utility reads a file containing
SQL statements and cursor attribute information and creates a pureQueryXML
file. After creating the file, use the Configure utility to prepare
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.
Syntax of command

(1)
>>-------java--java com.ibm.pdq.tools.GeneratePureQueryXml------>
>-- -username--user-ID-- -password--password-- -url--JDBC-URL--->
>--+---------------------------+-- -pureQueryXml--fileName------>
'- -driverName--JDBC-driver-'
>-- -inputSql--fileName--+----------------------------------+--->
'- -commentStart--commentIndicator-'
>--+---------------------------------+-------------------------->
'- -statementDelimiter -delimiter-'
>--+-------------------------------------+---------------------->
'- -resultSetHoldability -holdability-'
>--+-------------------------------------+---------------------->
'- -resultSetConcurrency--concurrency-'
>--+-----------------------+--+-----------------------+--------->
'- -resultSetType--type-' | (2) |
'-| Trace options |-----'
>--+--------+--------------------------------------------------><
'- -help-'
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
- -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 the SQL script file.
For information about the format of the SQL script file, see Format of the SQL script file used by the GeneratePureQueryXml utility.
- -commentStart 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 (--).
- -statementDelimiter delimiter
- Specifies the character combination used in the SQL script file
that separates statements. Default character is a semicolon (;).
- -resultSetHoldability holdability
- Specifies the result set holdability to apply to all statements.
The holdability is one of the following values:
- CLOSE_CURSORS_AT_COMMITsets the result set
holdablity to close at commit.
- HOLD_CURSORS_OVER_COMMIT sets the result set
holdablity to hold cursors over commit.
The default value is HOLD_CURSORS_OVER_COMMIT.
The
holdability value can be overridden for a specific SQL statement in
the SQL statement by specifying the holdability in a comment line
immediately preceding the SQL statement.
- -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.
The
concurrency value can be overridden for a specific SQL statement in
the SQL statement by specifying the concurrency in a comment line
immediately preceding the SQL statement.
- -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 result set type value can be overridden for a specific
SQL statement in the SQL statement by specifying the result set type
in a comment line immediately preceding the SQL statement.
- 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, ensure
that the following criteria is met for each SQL statement:
- The SQL statement text in the file exactly matches the SQL statement
issued by the application.
- 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 runtime capturedOnly property
is set to TRUE.
- 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 to be executed statically 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 run SQL statements statically 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
java com.ibm.pdq.tools.GenPdqXml
-username db2admin –password mypass
–url jdbc:db2://localhost:50000/mydb
–pureQueryXml "c:\statements.pdqxml" –inputSql "c:\mystatements.sql"