GeneratePureQueryXml utility

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

Read syntax diagramSkip visual syntax diagram
    (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:
  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

-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.
Read syntax diagramSkip visual syntax diagram
>>-+------------------------+--+---------------------------+---><
   '- -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"

Feedback