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.
(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 |-----'
To understand the conventions that are used in the diagram, see How to read syntax diagrams.
For information about the format of the SQL script file, see Format of the SQL script file used by the GeneratePureQueryXml utility.
If an SQL statement is in the input file multiple times, the SQL statement occurs only one time in the output pureQueryXML file. For information about how duplicate SQL statements are determined, see Determining duplicate SQL statements in an SQL file.
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.
The following list describes the supported values:
The value of SQL literal substitution attribute in the output pureQueryXML file is set to DISABLE.
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 to ENABLE.
The value of SQL literal substitution attribute in the output pureQueryXML file is set to NOT_SET.
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.
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.
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
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
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
>>-+------------------------+--+---------------------------+--->< '- -traceFile--file-name-' | .-OFF-----. | '- -traceLevel--+-ALL-----+-' +-SEVERE--+ +-WARNING-+ +-INFO----+ +-CONFIG--+ +-FINE----+ +-FINER---+ '-FINEST--'
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.
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.
You must configure a DB2 for z/OS database to capture some SQL performance statistics in a InfoSphere Optim Query Workload Tuner XML file. For information about enabling the collection of statistics, see Capturing DB2 for z/OS performance statistics in an InfoSphere Optim Query Workload Tuner XML file.
When the utility does not find time stamp information for SQL statements, the utility uses the current date and time.
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 statement 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.
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.
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.
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"
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;
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 = ?