GeneratePureQueryXml utility

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.

Syntax of the command

Read syntax diagramSkip visual syntax diagram
    (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:
  1. You can specify the options in any order.
  2. The default value is two dashes (--)
  3. 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.
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 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 = ?

Feedback